HAVING vs WHERE Clause
Learn the difference between HAVING and WHERE in SQL, execution order, and when to filter rows versus aggregated groups.
Expected Interview Answer
WHERE filters individual rows before any grouping or aggregation happens, while HAVING filters groups after GROUP BY has aggregated them, so HAVING is the only clause that can filter on aggregate functions like COUNT or SUM.
The SQL execution order applies WHERE first to trim raw rows out of the source tables, then GROUP BY collapses the remaining rows into groups, then aggregate functions compute per-group values, and only then does HAVING filter those computed groups. Because WHERE runs before aggregation, it cannot reference an aggregate function like SUM(salary), whereas HAVING can since the aggregate already exists by the time HAVING runs. Using WHERE for row-level conditions and HAVING only for aggregate conditions keeps queries both correct and efficient.
- WHERE reduces rows early, improving performance
- HAVING enables filtering on aggregated values
- Correct clause choice avoids invalid aggregate references
- Matches the actual SQL logical execution order
AI Mentor Explanation
WHERE is like a selector excluding players below a certain age before forming squads — it happens on individual players first. HAVING is applied after squads are formed and each squad total runs scored is calculated, filtering out any squad whose combined total falls below a threshold. You cannot ask the pre-selection filter about a squad total because squads do not exist yet at that stage; only after grouping does the aggregate exist for HAVING to check.
Step-by-Step Explanation
Step 1
Filter raw rows with WHERE
Apply row-level conditions before any grouping occurs.
Step 2
Group remaining rows
GROUP BY collapses rows into groups based on the grouping columns.
Step 3
Compute aggregates
Aggregate functions like SUM, COUNT, AVG calculate per-group values.
Step 4
Filter groups with HAVING
Apply conditions on the aggregated values to keep or drop whole groups.
What Interviewer Expects
- Correct understanding of SQL logical execution order
- Ability to state WHERE cannot use aggregate functions
- A concrete GROUP BY + HAVING example
- Awareness that WHERE filtering early improves performance
Common Mistakes
- Using HAVING for a simple row-level condition instead of WHERE
- Trying to reference an aggregate function inside WHERE
- Believing HAVING always requires GROUP BY
- Not knowing the logical execution order of SQL clauses
Best Answer (HR Friendly)
“WHERE filters individual rows before grouping happens, while HAVING filters entire groups after aggregation, which is why only HAVING can filter on things like SUM or COUNT. I use WHERE to trim rows early for performance and HAVING only when I need to filter based on an aggregated result.”
Code Example
SELECT department, COUNT(*) AS emp_count
FROM Employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5;Follow-up Questions
- What is the logical order of execution of SQL clauses?
- Can HAVING be used without GROUP BY?
- Why can WHERE not reference an aggregate function?
- How does query performance differ between filtering with WHERE vs HAVING?
MCQ Practice
1. Which clause filters rows before grouping?
WHERE filters individual rows before any grouping or aggregation takes place.
2. Which clause can filter using an aggregate function like COUNT()?
HAVING runs after aggregation, so it can filter groups based on aggregate function results.
3. What happens if you use an aggregate function inside a WHERE clause?
Aggregate functions are not yet computed when WHERE executes, so referencing one in WHERE raises an error.
Flash Cards
WHERE — Filters individual rows before grouping/aggregation.
HAVING — Filters groups after GROUP BY and aggregation.
Can WHERE use aggregates? — No, aggregates do not exist yet at WHERE stage.
Execution order — FROM -> WHERE -> GROUP BY -> aggregates -> HAVING -> SELECT -> ORDER BY