What is the difference between WHERE and HAVING?
WHERE filters individual rows before any grouping happens, and it cannot use aggregate functions. HAVING filters groups after a GROUP BY, and it can use aggregates like COUNT and SUM. In short, WHERE filters rows and HAVING filters groups.
The order of operations
SQL first applies WHERE to keep only certain rows, then groups them with GROUP BY, then applies HAVING to keep only certain groups. Because HAVING runs after grouping, it can talk about aggregates that only exist once rows are grouped.
SELECT department, COUNT(*) AS staff
FROM employees
WHERE active = 1 -- filter rows first
GROUP BY department
HAVING COUNT(*) > 5; -- filter groups afterHere WHERE removes inactive employees before grouping, and HAVING keeps only departments with more than five people after grouping.
The one liner is WHERE filters rows before grouping, HAVING filters groups after. Adding that you cannot use COUNT in a WHERE clause, only in HAVING, answers the classic follow up in advance.
Common follow up questions
Related interview questions
Want the full SQL guide?
Read every SQL concept with notes, diagrams, and code in one place. Track your progress as you go.
Open the SQL guide All SQL questions