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.
- WHERE filters individual rows before grouping.
- HAVING filters groups after GROUP BY and aggregation.
- You cannot use aggregate functions in WHERE, but you can in HAVING.
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.
Frequently asked questions
Can you use WHERE and HAVING in the same query?
Yes. WHERE filters rows first, then rows are grouped, and HAVING filters those groups. Using both is common and efficient.
Why can WHERE not use aggregate functions?
WHERE runs before groups are formed, so aggregates like SUM or COUNT do not exist yet. HAVING runs after grouping, so it can use them.
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