WHERE filters individual rows before grouping, and HAVING filters groups after aggregation. They are not interchangeable: a row predicate in HAVING runs after the work is done and can change which rows feed the aggregate, while an aggregate in WHERE is not allowed at all.
Before you start
You should be comfortable with GROUP BY and aggregate functions. This article uses PostgreSQL; the rule is standard SQL.
Step-by-step walkthrough
Step 1: Filter rows first with WHERE
Put any condition on a single row’s columns in WHERE. Filtering before grouping means the aggregate processes fewer rows, which is both faster and semantically clear. WHERE status = 'paid' selects only paid orders before they are counted.
Step 2: Filter groups with HAVING
Put conditions that use an aggregate, such as count(*) > 5, in HAVING, because the aggregate does not exist until after grouping. HAVING sees the grouped result, so it can reference count, sum and the grouping keys.
Step 3: Do not expect aliases in WHERE
Output aliases are visible in ORDER BY and sometimes HAVING, but not in WHERE, which runs before the projection. Repeat the expression or use a subquery or CTE if you need the alias earlier. Keeping row predicates in WHERE also lets the planner use indexes.
Worked scenario
The row filter runs before grouping and the group filter after.
SELECT customer_id, count(*) AS orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING count(*) > 5
ORDER BY orders DESC;Walk through the example
WHERE status = 'paid' removes unpaid orders before grouping, so only paid orders are counted. HAVING count(*) > 5 then keeps customers with more than five such orders. The alias orders is usable in ORDER BY because that runs after projection, which is not true for WHERE.
Common mistake
Putting a row predicate in HAVING, such as HAVING status = 'paid', which either errors or forces a scan of all rows before discarding most of them. Another is relying on an alias in WHERE and getting an unknown-column error.
Verify the behavior
Run a query with the same predicate in WHERE and in HAVING on a nullable or otherwise tricky case and compare results. Examine the EXPLAIN plan and confirm the WHERE version scans fewer rows. Assert an aggregate in WHERE produces a syntax error.
Interview exercise
Does moving a filter from HAVING to WHERE ever change the result, not just the speed?
Answer and reasoning
Yes, when the predicate depends on individual rows. Filtering rows before grouping changes which rows contribute to the aggregate, so counts and sums differ. If the predicate is on a grouped column and applied after grouping, the aggregate already includes every row. So the move can change both performance and meaning.
Continue learning
Compare aggregation grain in SQL aggregation grain and COUNT semantics. Read the PostgreSQL aggregate functions documentation and try the SQL interview questions.