What is the difference between WHERE and HAVING in SQL?
WebMid-level
In shortWHERE filters individual rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates like COUNT(*) or SUM().
Model answer
They filter at different moments:
FROM/JOINbuilds the rows.WHEREremoves rows. Aggregates don’t exist yet, soWHERE COUNT(*) > 1is an error.GROUP BYfolds the remaining rows into groups and calculatesCOUNT,SUM,AVG…HAVINGremoves groups, using those aggregates.
SELECT user_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
WHERE status <> 'canceled' -- ignore canceled orders (rows)
GROUP BY user_id
HAVING COUNT(*) >= 2; -- users with 2+ real orders (groups)Tester uses: HAVING COUNT(*) > 1 finds duplicates (emails, coupon uses, payment ids), and HAVING with SUM finds orders whose total doesn’t match their items.
Learn more: SQL for Testers
What they're looking for
The order of evaluation (WHERE, then GROUP BY, then HAVING), a working example that uses both, and a practical tester use such as finding duplicates.
Common mistakes
- Saying they are interchangeable.
- Trying to use COUNT or SUM in WHERE.
- Putting plain row filters in HAVING, which works in some databases but is slower and harder to read.