Combining WHERE and HAVING
You'll learn how to use WHERE and HAVING together to filter data at two different stages of your query.
Two Filters, Two Purposes
Think of WHERE and HAVING as two security checkpoints at different locations. WHERE filters individual rows before they're grouped—like checking tickets before people enter a venue. HAVING filters groups after aggregation—like checking if groups are large enough after people have been seated at tables.
When you combine them, WHERE runs first to reduce the dataset, then GROUP BY organizes what's left, and finally HAVING filters the grouped results.
How They Work Together
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 60000;
Here's the execution flow:
- WHERE filters out employees hired before 2020
- GROUP BY groups remaining employees by department
- AVG(salary) calculates average for each department
- HAVING keeps only departments where that average exceeds 60,000
Why Use Both?
Using WHERE first improves performance—it reduces data before the expensive grouping operation. You also need WHERE for non-aggregated conditions (like filtering individual dates or statuses) and HAVING for aggregate conditions (like filtering by group totals or averages).
The Key Rule
WHERE filters rows, HAVING filters groups. WHERE cannot reference aggregate functions; HAVING can.
Key Takeaway: Combine WHERE and HAVING to filter data at two stages—WHERE removes unwanted rows before grouping, while HAVING removes unwanted groups after aggregation, giving you precise control over your results.