Q5: What is the difference between the WHERE and HAVING clauses?
The fundamental difference lies in aggregation pipeline execution timing:
- WHERE: Filters individual records before group aggregations occur. It cannot evaluate aggregate functions like
SUM(),AVG(), orCOUNT(). - HAVING: Filters aggregated groups after the
GROUP BYstep finishes computing group statistics.
Combining WHERE and HAVING in a Single Query
SELECT department_id, COUNT(*) AS employee_count
FROM employees
WHERE status = 'ACTIVE' -- Filters raw rows first
GROUP BY department_id
HAVING COUNT(*) > 5; -- Filters aggregated groups💡 WHERE filters out inactive employees before computing department counts; HAVING filters out departments with 5 or fewer active employees.
Key Takeaways
- WHERE operates on individual records; HAVING operates on aggregated groups.
- Filtering records as early as possible with WHERE improves performance by reducing group calculation payload size.