HAVING vs WHERE
Overview
WHERE and HAVING both filter rows, but they operate at different stages of the logical query flow: - WHERE filters INDIVIDUAL rows BEFORE grouping. It cannot reference aggregate functions. - HAVING filters GROUPS AFTER GROUP BY. It CAN reference aggregate functions. Against the shared dataset: SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE status = 'paid' GROUP BY customer_id HAVING COUNT(*) >= 2 ORDER BY customer_id; Step by step: WHERE keeps only paid orders (6 rows). GROUP BY collapses them by customer_id. HAVING keeps only groups with 2 or more paid orders. Result: customer_id 1 (2 paid orders). Think of it this way: WHERE is a bouncer at the door (filters rows before they enter the GROUP BY room). HAVING is a judge inside the room (will evaluate each group after they've formed).
Filtering before vs after grouping produces fundamentally different results. Putting an aggregate condition in WHERE causes a syntax error; putting a row-level filter in HAVING is legal but wastes work — it groups rows only to discard them. Knowing which clause to use is a common interview question and a daily practical need.
Where used: finding high-value customer segments, data quality checks (groups with too few rows), dashboard filters on aggregated metrics
Why learn this
- Filtering groups by aggregate results (e.g. 'customers with more than 2 orders') requires HAVING — WHERE cannot do it
- This is one of the most frequently asked SQL interview questions
Query walkthrough
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;Focus: Watch WHERE reduce 8 rows to 6, GROUP BY collapse them into 5 groups, then HAVING keep only the group with 2+ orders.
Common mistakes
- Putting an aggregate in WHERE: WHERE COUNT(*) > 1 is a syntax error because WHERE runs before GROUP BY, so aggregates don't exist yet. Move the condition to HAVING COUNT(*) > 1.
- Using HAVING for row-level filters: HAVING status = 'paid' is a syntax error in Postgres because 'status' is not in GROUP BY. If the column IS in the GROUP BY clause, it's legal but inefficient compared to WHERE — it lets all rows through GROUP BY, forms groups, then discards them.
- Confusing the logical order: The logical flow is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE and HAVING sit on opposite sides of GROUP BY. Remembering this order clears up most confusion about which clause to use.
Glossary
- logical
- Relating to the step-by-step sequence of operations or rules that a computer follows to process information.
- evaluate
- To calculate or determine the outcome of a condition, like checking if a customer has more than two orders.
- clause
- A specific part of a SQL statement, like WHERE or HAVING, that performs a particular function in the query.
Recall questions
- Can WHERE reference an aggregate function like COUNT(*)? Why or why not?
- In the logical query flow, where does HAVING sit relative to GROUP BY?
- Is it valid to use HAVING with a non-aggregate condition? Is it a good idea?
Understanding checks
What rows does this return?
1 | 2
WHERE keeps only paid orders: ids 1,2,4,6,7,8 (6 rows). GROUP BY customer_id: customer 1 has orders 1,2 (count=2), customer 2 has order 8 (count=1), customer 3 has order 4 (count=1), customer 5 has order 6 (count=1), customer 6 has order 7 (count=1). HAVING COUNT(*) >= 2 keeps only customer 1.
Which categories appear in the result?
Electronics | 3 Furniture | 2
Products group into Electronics (3), Furniture (2), Stationery (1). HAVING COUNT(*) > 1 drops Stationery, keeping only Electronics and Furniture.
Practice tasks
Move the filter to the right clause
This query contains a syntax error because it puts a row-level filter on an ungrouped column (`status`) in the HAVING clause. Move the `status` condition to WHERE (where it belongs) and keep the aggregate filter in HAVING.
Challenge
Categories with high average price
Write a query that returns each product category and its average price, but only for categories where the average price is above 50. Order by average price descending.
Continue learning
Previous: GROUP BY
Previous: Aggregate Functions
Next: Scalar & IN Subqueries