HAVING vs WHERE

Row-Level vs Group-Level Filtering

SQL queries have two distinct filtering stages, and understanding the difference between them is essential for writing correct aggregation queries.

WHERE filters individual rows before grouping. It operates on raw table rows and cannot reference aggregate functions. Its job is to reduce the input to the grouping step.

HAVING filters groups after aggregation. It operates on the grouped result set and can reference aggregate functions. Its job is to remove groups that do not meet summary-level conditions.

Why Two Stages Exist

Consider the question: "Which regions have more than 100 orders?" This question has two parts:

1. Compute the order count per region (aggregation)
2. Keep only regions where that count exceeds 100 (group filtering)

You cannot filter on the order count before computing it. The count does not exist until after GROUP BY runs. This is why HAVING exists — it is the only clause that can reference aggregate results.

The Execution Order Reminder

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

WHERE runs before GROUP BY. HAVING runs after GROUP BY. This order is not a suggestion — it is the SQL standard, and every database engine follows it.

A Concrete Example

-- Find regions with more than 100 orders
SELECT region, COUNT(*) AS order_count
FROM orders
GROUP BY region
HAVING COUNT(*) > 100;

This query first groups all orders by region, then removes groups where the count is 100 or fewer. The result contains only high-volume regions.

WHERE: Filter Rows Before Aggregation

The WHERE clause determines which rows participate in the aggregation. It runs before GROUP BY, so it can only reference raw table columns — not aggregate functions.

Valid WHERE Usage

-- Count orders per region, but only for orders in 2024
SELECT region, COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'
GROUP BY region;

The WHERE clause filters out all orders outside 2024 before grouping. The aggregation then operates only on 2024 orders.

Invalid WHERE Usage

-- This query fails
SELECT region, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) > 100
GROUP BY region;

This produces an error: "aggregate functions are not allowed in WHERE." The WHERE clause runs before grouping, so COUNT(*) does not exist yet. The database cannot compute an aggregate over groups that have not been formed.

Why This Error Occurs

The error is not a limitation — it is a logical necessity. WHERE operates on individual rows. An aggregate function like COUNT(*) operates on a set of rows. You cannot apply a set-level operation to individual rows. The database rejects the query because the request is logically incoherent.

WHERE with Joins

WHERE also filters join results before grouping:

SELECT c.region, COUNT(*) AS order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.region;

Only completed orders are counted. The WHERE clause filters the joined rows before they are grouped by region.

Performance Implication

Filtering in WHERE is more efficient than filtering in HAVING when the filter does not depend on an aggregate. WHERE reduces the number of rows that must be grouped, which reduces the work the database does. If you can filter in WHERE, do it there.

HAVING: Filter Groups After Aggregation

The HAVING clause filters the grouped result set. It runs after GROUP BY, so it can reference aggregate functions and the grouping columns.

Valid HAVING Usage

-- Regions with average order value above $200
SELECT region, AVG(amount) AS avg_order_value
FROM orders
GROUP BY region
HAVING AVG(amount) > 200;

-- Regions with more than 5 unique customers
SELECT region, COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY region
HAVING COUNT(DISTINCT customer_id) > 5;

-- Regions with total revenue between $10,000 and $50,000
SELECT region, SUM(amount) AS total_revenue
FROM orders
GROUP BY region
HAVING SUM(amount) BETWEEN 10000 AND 50000;

Each of these queries computes an aggregate per group, then filters groups based on that aggregate.

HAVING Without GROUP BY

HAVING can be used without GROUP BY, in which case the entire result set is treated as a single group:

SELECT COUNT(*) AS total_orders
FROM orders
HAVING COUNT(*) > 0;

This is valid but unusual. It returns one row if the table has any rows, and zero rows if the table is empty. In practice, this pattern is rare because a simple WHERE or a direct SELECT is usually clearer.

HAVING Can Reference Aliases (in Some Databases)

MySQL and PostgreSQL allow HAVING to reference column aliases defined in SELECT:

SELECT region, COUNT(*) AS order_count
FROM orders
GROUP BY region
HAVING order_count > 100;

This works because HAVING logically runs after SELECT. However, for maximum portability, repeat the aggregate expression: HAVING COUNT(*) > 100.

Combining WHERE and HAVING

The most powerful pattern uses both clauses in the same query — WHERE to filter rows before grouping, HAVING to filter groups after:

-- Regions with more than 100 completed orders in 2024
SELECT region, COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
  AND order_date >= '2024-01-01'
  AND order_date < '2025-01-01'
GROUP BY region
HAVING COUNT(*) > 100;

WHERE reduces the input to completed 2024 orders. GROUP BY partitions them by region. HAVING removes regions with 100 or fewer such orders. Each clause does exactly one job, and the query is both correct and efficient.

Mistake 1: Aggregate in WHERE

The most common error is placing an aggregate condition in WHERE:

-- WRONG: causes an error
SELECT region, COUNT(*) AS cnt
FROM orders
WHERE COUNT(*) > 10
GROUP BY region;

-- CORRECT: use HAVING
SELECT region, COUNT(*) AS cnt
FROM orders
GROUP BY region
HAVING COUNT(*) > 10;

Mistake 2: Filtering Non-Aggregated Columns in HAVING

HAVING can filter on non-aggregated columns, but this is usually a mistake — it should be in WHERE:

-- Works but inefficient
SELECT region, COUNT(*) AS cnt
FROM orders
GROUP BY region
HAVING region != 'Internal';

-- Better: filter in WHERE
SELECT region, COUNT(*) AS cnt
FROM orders
WHERE region != 'Internal'
GROUP BY region;

Both queries return the same result, but the WHERE version is more efficient because it filters rows before grouping, reducing the aggregation workload.

Mistake 3: Confusing the Two Stages

A subtle bug occurs when a developer intends to filter groups but writes the condition in WHERE:

-- Intended: regions with avg order value > 200
-- Actual: filters individual orders with amount > 200, then groups
SELECT region, AVG(amount) AS avg_value
FROM orders
WHERE amount > 200
GROUP BY region;

This query does not compute the average order value per region and then filter. It first removes all orders with amount ≤ 200, then computes the average of the remaining orders. The result is the average of large orders only, not the average of all orders filtered by a threshold.

Debugging Checklist

When an aggregation query returns unexpected results, check:

1. Is the filter condition in the right clause? Row conditions go in WHERE. Aggregate conditions go in HAVING.
2. Is WHERE filtering too much? A WHERE condition on a joined table can silently remove rows you intended to include.
3. Is HAVING filtering too little? A HAVING condition that is too permissive lets unwanted groups through.
4. Does the query use both clauses correctly? WHERE for input filtering, HAVING for output filtering.

The Decision Framework

Ask one question: Does the filter condition reference an aggregate function?

• Yes → HAVING
• No → WHERE

This single rule resolves 95% of WHERE vs HAVING decisions.