SQL में WHERE और HAVING में अंतर
WHERE grouping से PEHLE rows filter करता है, HAVING grouping के BAAD groups. Execution order, दोनों साथ वाली query, और 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
WHERE और HAVING में क्या अंतर है?
WHERE GROUP BY से पहले individual rows filter करता है और उसमें aggregate functions नहीं आ सकते; HAVING GROUP BY के बाद groups filter करता है और AVG या COUNT जैसी aggregate conditions के लिए ही बना है.
WHERE aggregate functions use क्यों नहीं कर सकता?
क्योंकि WHERE grouping से पहले चलता है — उस stage पर groups बने ही नहीं होते, तो COUNT या AVG निकालने को कुछ नहीं होता; ऐसी conditions HAVING में जाती हैं.
क्या WHERE और HAVING एक ही query में आ सकते हैं?
हां, और यह common है: WHERE पहले rows काटता है (speed के लिए), GROUP BY groups बनाता है, फिर HAVING सिर्फ aggregate condition पूरी करने वाले groups रखता है.