MySQL HAVING Clause
MySQL HAVING Clause
The HAVING clause in MySQL is used to filter grouped results produced by GROUP BY.
Think of HAVING as WHERE for aggregate functions.
What is HAVING Clause?
Filters groups, not individual rows
Used after
GROUP BYWorks with aggregate functions like
COUNT(),SUM(),AVG(),MIN(),MAX()WHERE→ filters rowsHAVING→ filters groups
Basic Syntax
Simple Example
Departments with More Than 5 Employees
COUNT(*) > 5cannot be used inWHERE
HAVING with SUM()
Departments with Total Salary > 5,00,000
HAVING with AVG()
Products with Average Price ≥ 1000
WHERE vs HAVING (Very Important)
WHEREfilters rows before groupingHAVINGfilters groups after grouping
HAVING Without GROUP BY
- Valid in MySQL (acts on a single group)
Multiple Conditions in HAVING
HAVING with Alias (Allowed in MySQL)
- MySQL allows alias in HAVING (not all DBs do)
HAVING vs WHERE – Comparison Table
| Feature | WHERE | HAVING |
|---|---|---|
| Filters | Rows | Groups |
| Aggregate functions | No | Yes |
| Used with GROUP BY | No | Yes |
| Execution | Before GROUP BY | After GROUP BY |
Common Mistakes
- Using aggregate functions in WHERE
- Forgetting GROUP BY
- Confusing row filter vs group filter
- Poor performance (filter late with HAVING instead of WHERE)
Interview Questions & MCQs (Very Important)
Q1. What is HAVING clause used for?
A) Filtering rows
B) Filtering groups
C) Sorting data
D) Joining tables
Answer: B
Q2. Which clause is used with aggregate functions?
A) WHERE
B) HAVING
C) ORDER BY
D) LIMIT
Answer: B
Q3. Which is correct?
A) WHERE COUNT(*) > 5
B) HAVING COUNT(*) > 5
Answer: B
Q4. Which executes first?
A) HAVING
B) WHERE
Answer: B
Q5. Can HAVING be used without GROUP BY in MySQL?
A) Yes
B) No
Answer: A
Q6. Which clause should filter rows before grouping?
A) HAVING
B) WHERE
Answer: B
Q7. HAVING is applied:
A) Before SELECT
B) After GROUP BY
C) Before WHERE
D) After ORDER BY
Answer: B
Real-Life Use Cases
- Departments with high salary cost
- Customers with many orders
- Products with high average rating
- Reports & analytics dashboards
Summary
HAVING filters group results
Used with GROUP BY
Supports aggregate conditions
WHERE filters rows; HAVING filters groups
Essential for reports, analytics, interviews
