SQL · easy

What is the difference between WHERE and HAVING?

Asked in BI Developer interviews, in the SQL round.

Short answer

WHERE filters rows before aggregation, HAVING filters groups after GROUP BY.

	WHERE	HAVING
Runs	Before GROUP BY	After GROUP BY
Filters	Individual rows	Groups
Can use SUM, COUNT, AVG	No	Yes
Can use a column alias	No	Yes, in most engines
Performance	Cheaper, cuts rows early	Runs on the aggregated set

WHERE filters rows on the way in, HAVING filters groups on the way out.

How to answer it

Definition

WHERE filters individual rows before they are grouped. HAVING filters the groups that GROUP BY produced, after aggregation has run. Both narrow a result set; they simply do it at two different moments, and that is the whole difference.

A condition on a raw column belongs in WHERE. A condition on an aggregate such as COUNT(*) or SUM(amount) belongs in HAVING, because the aggregate does not exist yet at the moment WHERE runs.

How SQL actually runs the clauses

A query is not executed in the order it is written. The logical order is:

1. FROM       pick the tables and join them
2. WHERE      drop rows that fail the condition
3. GROUP BY   fold the surviving rows into groups
4. HAVING     drop groups that fail the condition
5. SELECT     compute the output columns and their aliases
6. ORDER BY   sort what is left
7. LIMIT      cut the result

Nearly every question about these two clauses is answered by that list. WHERE is step 2 and cannot see an aggregate, because aggregates are not formed until step 3. HAVING is step 4 and can see them. An alias invented at step 5 cannot be used back at step 2.

Syntax

SELECT   column, AGGREGATE(column)
FROM     table
WHERE    row_condition                    -- before grouping
GROUP BY column
HAVING   AGGREGATE(column) condition      -- after grouping

Example: filter rows, then filter groups

SELECT   customer_id, COUNT(*) AS orders_2025
FROM     orders
WHERE    order_date >= '2025-01-01'
GROUP BY customer_id
HAVING   COUNT(*) >= 5;

WHERE throws away every order placed before 2025. GROUP BY folds what survives into one row per customer. HAVING then keeps only the customers with five or more of those remaining orders. Note that the two filters cannot be swapped: a customer with twenty orders in 2024 and one in 2025 is excluded, because the row filter ran first.

Output

customer_id | orders_2025
------------+-------------
        101 |           7
        204 |           5

HAVING without GROUP BY

Legal, and occasionally useful. With no GROUP BY, the entire result is treated as a single group:

SELECT SUM(amount) AS revenue
FROM   orders
HAVING SUM(amount) > 1000000;

That returns one row if total revenue clears a million, and no rows at all otherwise.

Common errors

Putting an aggregate in WHERE:

-- wrong
SELECT   customer_id
FROM     orders
WHERE    COUNT(*) >= 5
GROUP BY customer_id;

Postgres replies aggregate functions are not allowed in WHERE. The fix is to move the condition into HAVING.

Using a SELECT alias in WHERE:

-- wrong
SELECT amount * 0.2 AS tax FROM orders WHERE tax > 100;

SELECT runs after WHERE, so tax does not exist yet. Repeat the expression in WHERE, or wrap the query in a subquery or CTE.

Which one to use

The condition mentions only raw columns, so it goes in WHERE.
The condition mentions COUNT, SUM, AVG, MIN or MAX, so it goes in HAVING.
The condition could sit in either, so it goes in WHERE. Always.

Performance

WHERE does strictly less work. It removes rows before the grouping step, so the engine sorts, hashes and aggregates a smaller set. A condition pushed from HAVING down into WHERE can also let the planner use an index on that column, which HAVING never can, because by then the original rows are gone.

On a large table the difference is not academic. Filtering a year of orders down to one month in WHERE means grouping a twelfth of the rows.

Key points

The order of evaluation is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
WHERE cannot contain an aggregate. HAVING is the clause that can.
An alias defined in SELECT is unavailable in WHERE. Most engines do allow it in HAVING.
HAVING with no GROUP BY treats the whole result as one group.
When a condition would work in either clause, put it in WHERE for the smaller intermediate set.

FAQ

Can a query use both? Yes, and most useful aggregate queries do. WHERE narrows the rows, HAVING narrows the groups.
Is HAVING slower than WHERE? For the same condition, effectively yes, because it runs after the grouping instead of shrinking the input to it.
Can WHERE reference a GROUP BY column? Yes. It is a raw column, available long before the grouping happens.
Can HAVING reference a column that is neither aggregated nor grouped? No. After grouping, that column no longer has a single value.

The 30-second answer

WHERE filters rows before they are grouped, HAVING filters the groups after. So a condition on a raw column, like a date range, goes in WHERE. A condition on an aggregate, like having at least five orders, has to go in HAVING, because that count does not exist yet at the point WHERE runs.

The reason is the evaluation order: FROM, WHERE, GROUP BY, HAVING, then SELECT. That is also why an alias I define in SELECT is not available back in WHERE. And if a condition could sit in either clause, I would put it in WHERE, because it shrinks the set before the grouping has to do any work.

In the interview

What they are checking: that you know the evaluation order, not just the two definitions.
Common mistake: putting a plain row condition in HAVING. It runs, but it groups everything first and filters late.

Practice this for real

Check your resume against a BI Developer job description, free, no account needed.
Every data interview question, by role and topic
Scored AI mock interviews, two free, no card