WHERE vs HAVING
You'll learn when to filter rows before grouping versus filtering groups after aggregation.
The Core Difference
Both WHERE and HAVING filter data, but they work at completely different stages of query execution:
- WHERE filters individual rows before any grouping happens
- HAVING filters entire groups after
GROUP BYhas created them
Think of it like sorting laundry: WHERE is choosing which individual clothes go into the wash at all (removing damaged items beforehand), while HAVING is deciding which piles of sorted laundry are big enough to bother washing (filtering out groups with too few items).
When to Use Each
Use WHERE when you need to:
- Filter based on column values in individual rows
- Reduce data before expensive grouping operations
- Apply conditions that don't involve aggregate functions
Use HAVING when you need to:
- Filter based on aggregate function results (
COUNT,SUM,AVG, etc.) - Apply conditions to groups as a whole
- Work with grouped data
Simple Rule of Thumb
-- WHERE: filters rows, can't use COUNT/SUM/AVG
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000 -- Filter individual employees first
GROUP BY department;
-- HAVING: filters groups, CAN use COUNT/SUM/AVG
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 10; -- Filter departments with >10 people
You can use both in the same query: WHERE narrows down which rows participate in grouping, then HAVING filters the resulting groups.
Key Takeaway: Use WHERE to filter individual rows before grouping; use HAVING to filter aggregated groups after GROUP BY.