HAVING vs WHERE Clause
HAVING vs WHERE Clause
Understanding the distinction between WHERE and HAVING is rooted in the physical execution order of SQL queries.
Comparison
| Feature | WHERE Clause | HAVING Clause |
|---|---|---|
| Execution Timing | Filters rows BEFORE grouping | Filters groups AFTER grouping |
| Aggregates Allowed? | No (e.g. no COUNT, AVG) |
Yes (e.g. HAVING COUNT(*) > 1) |
| Applied To | Individual table rows | Aggregated summary groups |
| Index Usage | Can leverage table indexes | Operates on aggregated results |
Execution Pipeline
- FROM: Identifies source tables and joins
- WHERE: Filters individual rows BEFORE grouping
- GROUP BY: Aggregates remaining rows into summary buckets
- HAVING: Filters aggregated groups AFTER grouping
- SELECT: Computes projected expressions and aliases
- ORDER BY: Sorts final result set
- LIMIT: Restricts row count
Code Comparison
-- WHERE filters rows before aggregation:
SELECT department, AVG(salary) AS avg_sal
FROM employees
WHERE salary > 50000 -- Only employees earning > 50k are included in groups
GROUP BY department;
-- HAVING filters after aggregation:
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000; -- Only departments with average > 75k are returned