Difference Between WHERE and HAVING in SQL
WHERE filters rows BEFORE grouping, HAVING filters groups AFTER grouping. See the execution order, a query using both together, and the aggregate-function rule.
The school-assembly analogy
See both in one query
-- students table
+---------+-------+-------+
| name | class | marks |
| Aman | 10 | 92 |
| Priya | 10 | 88 |
| Rahul | 9 | 45 |
| Neha | 9 | 95 |
| Kabir | 8 | 60 |
+---------+-------+-------+
SELECT class, COUNT(*) AS total, AVG(marks) AS avg_marks
FROM students
WHERE marks >= 50 -- STEP 1: gate check - Rahul (45) rejected
GROUP BY class -- STEP 2: teams formed - class 10, 9, 8
HAVING AVG(marks) > 70; -- STEP 3: team check - class 8 (avg 60) rejectedTrace it: WHERE removed Rahul first, so class 9's average became 95 (only Neha). Then groups formed. Then HAVING removed class 8. Understanding this order is the entire topic.
The execution order (memorize this line)
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
(rows) (make (groups)
groups)This order also explains a famous error: WHERE cannot use aggregate functions. WHERE AVG(marks) > 70 fails because at the WHERE stage, groups don't exist yet — there is nothing to average!
SELECT class, AVG(marks) FROM students
WHERE AVG(marks) > 70 -- ERROR: Invalid use of group function
GROUP BY class;
-- Correct: aggregate conditions go in HAVING
SELECT class, AVG(marks) FROM students
GROUP BY class
HAVING AVG(marks) > 70; -- worksComparison table
| Point | WHERE | HAVING |
|---|---|---|
| Filters | Individual rows | Groups |
| Runs | Before GROUP BY | After GROUP BY |
| Aggregate functions (AVG, COUNT...) | ❌ Not allowed | ✅ Allowed — its main purpose |
| Works without GROUP BY | ✅ Yes, normal filtering | Technically yes, practically pointless |
| Performance | Better (cuts rows early) | Runs on already-grouped data |
Rule of thumb
- Condition about a single row's column (class, city, date) → WHERE.
- Condition about a calculated group value (COUNT(*), AVG, SUM) → HAVING.
- Put every condition you can in WHERE — filtering early means fewer rows to group, so the query runs faster.
- Interview line: "WHERE filters rows before grouping and cannot use aggregates; HAVING filters groups after grouping and exists precisely for aggregate conditions."
School-competition वाली analogy
दोनों को एक query में देखिए
-- students table
+---------+-------+-------+
| name | class | marks |
| Aman | 10 | 92 |
| Priya | 10 | 88 |
| Rahul | 9 | 45 |
| Neha | 9 | 95 |
| Kabir | 8 | 60 |
+---------+-------+-------+
SELECT class, COUNT(*) AS total, AVG(marks) AS avg_marks
FROM students
WHERE marks >= 50 -- STEP 1: gate check - Rahul (45) बाहर
GROUP BY class -- STEP 2: teams बनीं - class 10, 9, 8
HAVING AVG(marks) > 70; -- STEP 3: team check - class 8 (avg 60) बाहरTrace कीजिए: WHERE ने पहले Rahul हटाया, इसलिए class 9 का average 95 बना (सिर्फ Neha). फिर groups बने. फिर HAVING ने class 8 हटाई. यह order समझना ही पूरा topic है.
Execution order (यह line रट लीजिए)
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
(rows) (groups (groups)
banao)यही order एक famous error भी समझाता है: WHERE में aggregate functions नहीं चलते. WHERE AVG(marks) > 70 fail होता है क्योंकि WHERE stage पर groups बने ही नहीं — average निकालने को कुछ है ही नहीं!
SELECT class, AVG(marks) FROM students
WHERE AVG(marks) > 70 -- ERROR: Invalid use of group function
GROUP BY class;
-- सही: aggregate conditions HAVING में जाती हैं
SELECT class, AVG(marks) FROM students
GROUP BY class
HAVING AVG(marks) > 70; -- चलता हैComparison table
| Point | WHERE | HAVING |
|---|---|---|
| Filter करता है | Individual rows | Groups |
| चलता है | GROUP BY से पहले | GROUP BY के बाद |
| Aggregate functions (AVG, COUNT...) | ❌ Allowed नहीं | ✅ Allowed — यही main purpose |
| बिना GROUP BY | ✅ हां, normal filtering | Technically हां, practically बेकार |
| Performance | बेहतर (rows जल्दी कटती हैं) | पहले से grouped data पर चलता है |
Rule of thumb
- Condition single row के column पर (class, city, date) → WHERE.
- Condition calculated group value पर (COUNT(*), AVG, SUM) → HAVING.
- जो condition WHERE में जा सकती है, वहीं रखें — जल्दी filter = कम rows group होंगी = तेज़ query.
- Interview line: "WHERE grouping से पहले rows filter करता है और aggregates use नहीं कर सकता; HAVING grouping के बाद groups filter करता है और aggregate conditions के लिए ही बना है."
Frequently Asked Questions
What is the difference between WHERE and HAVING?
WHERE filters individual rows before GROUP BY and cannot contain aggregate functions; HAVING filters the groups after GROUP BY and exists specifically for conditions on aggregates like AVG or COUNT.
Why can WHERE not use aggregate functions?
Because WHERE runs before grouping — at that stage groups do not exist yet, so there is nothing to COUNT or AVG over; such conditions belong in HAVING.
Can WHERE and HAVING be used in the same query?
Yes, and it is common: WHERE cuts rows first for speed, GROUP BY forms groups, then HAVING keeps only the groups meeting the aggregate condition.