HAVING vs WHERE in SQL
Understand HAVING vs WHERE in SQL: WHERE filters rows before grouping, HAVING filters groups after aggregation. See examples, order of execution and tips.
Expected Interview Answer
WHERE filters individual rows before any grouping happens, while HAVING filters entire groups after aggregation, so HAVING can reference aggregate functions like SUM or COUNT and WHERE cannot.
In the logical order of a query, WHERE runs first on raw rows, then GROUP BY forms buckets, then HAVING removes buckets that fail an aggregate condition. Because aggregates are not yet computed when WHERE executes, you cannot write WHERE SUM(x) > 100 — that belongs in HAVING. For non-aggregate conditions, prefer WHERE since filtering earlier reduces the rows that must be grouped, which is usually faster.
- WHERE cuts data early for better performance
- HAVING enables filtering on aggregated values
- Together they filter at both row and group level
- Clarifies query intent and avoids errors
- Essential for correct summary reports
AI Mentor Explanation
WHERE is the selector dropping players who did not bat this innings before any totals are calculated. HAVING is the award committee that, after each batter's runs are summed, keeps only those who scored a century. One trims raw entries, the other judges finished totals.
Step-by-Step Explanation
Step 1
WHERE filters rows
It runs first, removing individual rows that fail a non-aggregate condition.
Step 2
GROUP BY forms buckets
Surviving rows are partitioned into groups by the grouping columns.
Step 3
Aggregates compute
COUNT, SUM, AVG and friends are calculated once per group.
Step 4
HAVING filters groups
Groups whose aggregate values fail the HAVING condition are discarded.
Step 5
Choose the right clause
Use WHERE for row conditions and HAVING only when the condition depends on an aggregate.
What Interviewer Expects
- WHERE runs before grouping, HAVING after
- HAVING can use aggregates, WHERE cannot
- Performance benefit of filtering early with WHERE
- Correct clause choice for a given condition
- A concrete example combining both clauses
Common Mistakes
- Using HAVING for plain row conditions that belong in WHERE
- Trying to reference an aggregate inside WHERE
- Believing HAVING requires a GROUP BY (it can filter a whole-table aggregate)
- Assuming the two clauses are interchangeable
- Expecting WHERE to see aggregated values
Best Answer (HR Friendly)
“WHERE picks which individual records to keep before any totals are worked out, and HAVING then keeps or drops whole groups based on those totals. In short, WHERE filters rows and HAVING filters summarized groups.”
Code Example
SELECT customer_id,
SUM(amount) AS total_spent
FROM orders
WHERE status = 'completed' -- filter rows first
GROUP BY customer_id
HAVING SUM(amount) > 1000 -- then filter groups
ORDER BY total_spent DESC;Follow-up Questions
- Why can't you use an aggregate function inside WHERE?
- Can HAVING be used without GROUP BY?
- Which clause is generally better for performance and why?
- What is the logical order of execution in a SELECT statement?
- How would you rewrite a HAVING condition that only uses non-aggregate columns?
MCQ Practice
1. Which clause can reference an aggregate function like SUM()?
Aggregates are computed after grouping, so only HAVING, which runs post-aggregation, can reference them.
2. In logical execution order, which runs first?
WHERE executes before GROUP BY and HAVING, filtering raw rows before any grouping occurs.
3. For a simple non-aggregate condition, which clause is preferred?
WHERE filters rows before grouping, reducing work and usually improving performance over HAVING.
Flash Cards
What does WHERE filter? — Individual rows, before grouping, using non-aggregate conditions.
What does HAVING filter? — Whole groups, after aggregation, and it can reference aggregate functions.
Can WHERE use SUM()? — No. Aggregates are not computed yet when WHERE runs; use HAVING instead.
Which is faster for row conditions? — WHERE, because filtering early shrinks the data that must be grouped.