Difference Between WHERE and HAVING?
Understand the difference between WHERE and HAVING in SQL, when each one runs in the query execution order, and how to combine them correctly.
Expected Interview Answer
WHERE filters individual rows before any grouping or aggregation happens, while HAVING filters groups after GROUP BY has produced aggregate values, so HAVING can reference aggregate functions like SUM() or COUNT() but WHERE cannot.
The SQL logical processing order evaluates FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, so WHERE only ever sees raw table rows and has no knowledge of aggregate results. HAVING runs after grouping, so it operates on the summarized rows produced by GROUP BY and can filter on conditions like HAVING COUNT(*) > 5 or HAVING SUM(amount) > 1000. You can use WHERE and HAVING together: WHERE narrows the raw rows first (cheaper, since it reduces work before aggregation), and HAVING then filters the resulting groups. Using WHERE for non-aggregate conditions is more efficient than pushing everything into HAVING, because it reduces the row count before the expensive grouping step.
- WHERE filters early, reducing rows before aggregation for better performance
- HAVING enables filtering on aggregate results like totals or counts
- Combining both lets you filter rows and then filter groups precisely
- Understanding the order prevents 'unknown column' errors on aggregates in WHERE
AI Mentor Explanation
WHERE is like a selector filtering out players who did not turn up for trials, before any scores are even tallied. HAVING is like filtering the tally sheet afterward to show only players who scored above fifty runs across the trial, which is only possible once totals exist.
Step-by-Step Explanation
Step 1
SQL processes FROM first
The engine identifies the source tables and any joins before filtering anything.
Step 2
WHERE filters raw rows
Non-aggregate row-level conditions are applied here, before any grouping occurs.
Step 3
GROUP BY forms groups
Remaining rows are bucketed into groups based on the grouping columns.
Step 4
Aggregate functions compute
SUM, COUNT, AVG, and similar functions calculate one value per group.
Step 5
HAVING filters groups
Conditions on aggregate values are applied here, keeping or dropping entire groups.
Step 6
SELECT projects the result
The final column list and aliases are applied last, producing the output rows.
What Interviewer Expects
- States that WHERE filters rows before grouping, HAVING filters groups after
- Knows HAVING can use aggregate functions but WHERE cannot
- Understands the logical SQL execution order (FROM, WHERE, GROUP BY, HAVING, SELECT)
- Can combine WHERE and HAVING correctly in one query
- Explains why filtering with WHERE first is more efficient than only using HAVING
Common Mistakes
- Trying to use an aggregate function like COUNT(*) inside WHERE
- Using HAVING for simple row filters that WHERE could handle more efficiently
- Assuming WHERE runs after GROUP BY
- Forgetting that HAVING requires a GROUP BY to be meaningful in most dialects
Best Answer (HR Friendly)
“WHERE filters out individual rows before any totals are calculated, like removing bad entries from a spreadsheet before summing. HAVING filters out entire groups after totals exist, like removing regions whose total sales are too low, which you can only judge once the totals are ready.”
Code Example
-- orders: id, region, amount, status
-- (1,'North',500,'paid'), (2,'North',300,'paid'),
-- (3,'South',100,'paid'), (4,'North',200,'cancelled')
SELECT region, SUM(amount) AS total_paid
FROM orders
WHERE status = 'paid' -- filters rows first (drops the cancelled row)
GROUP BY region
HAVING SUM(amount) > 400; -- filters groups after aggregation
-- Result:
-- region | total_paid
-- North | 800 (South's 100 is excluded by HAVING)Follow-up Questions
- Can you use HAVING without a GROUP BY?
- What is the logical order of execution in a SQL query?
- Why can't you reference a column alias from SELECT inside WHERE?
- How would you filter both raw rows and aggregated totals in one query?
- Is there a performance difference between filtering with WHERE versus HAVING?
MCQ Practice
1. Which clause filters rows before grouping and aggregation occur?
WHERE is evaluated before GROUP BY, so it filters raw rows prior to any aggregation.
2. Which clause can reference aggregate functions such as SUM() or COUNT()?
HAVING runs after GROUP BY has produced aggregate values, so it can filter based on those aggregates; WHERE cannot.
3. In the logical SQL execution order, which comes first?
The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY — WHERE precedes HAVING and SELECT.
Flash Cards
What does WHERE filter? — Individual rows, before any GROUP BY or aggregation happens.
What does HAVING filter? — Groups, after GROUP BY has produced aggregate values like SUM or COUNT.
Can WHERE use aggregate functions? — No — aggregates don't exist yet when WHERE runs, so they cannot appear there.
Why filter with WHERE before HAVING when possible? — WHERE reduces the row count before the costlier grouping and aggregation step, improving performance.