Lessons · SQL · HAVING vs WHERE
Filter rows with WHERE, groups with HAVING
WHERE runs before grouping on individual rows; HAVING runs after on the aggregated groups.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
'Customers with more than 5 orders' cannot be a WHERE; the count does not exist yet.
How to think about it
Does the condition mention an aggregate? If the condition mentions an aggregate (COUNT, SUM), it is HAVING. If it mentions a plain column, it is WHERE, and putting it there is cheaper.
Worked example
SELECT customer_id, COUNT(*) AS nThe group and its count.
FROM orders WHERE status = 'paid'Rows first: only paid.
GROUP BY customer_id HAVING COUNT(*) > 1;Then groups: only repeat customers.
Your turn
Ratings with more than 10 films.
SELECT rating FROM films GROUP BY rating COUNT(*) > 10;
Run a query against real tables
The trap
Moving a plain-column filter into HAVING. It works but groups everything first, slowly.
Practise HAVING vs WHERE on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.