What's the difference between WHERE and HAVING?

3 minbeginnersql-basicswherehavinggroup-by

Quick Answer

WHERE filters individual rows before grouping/aggregation happens. It cannot reference aggregate functions. HAVING filters groups after GROUP BY has run, for conditions on aggregates like COUNT(*) or SUM(x). If a query has no GROUP BY, HAVING treats the whole result set as one group.

Detailed Answer

Both clauses filter rows, but at different stages. Recall the logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT.

WHERE: filters rows before grouping

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'   -- filters individual employee rows first
GROUP BY department;

HAVING: filters groups after aggregation

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 75000;       -- filters the resulting department groups

Combining both

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'           -- row-level filter first
GROUP BY department
HAVING COUNT(*) >= 10;            -- group-level filter second

The rule of thumb

Use WHERE for conditions on raw column values that exist before grouping.
Use HAVING for conditions on the result of an aggregate function.
Putting an aggregate condition in WHERE (WHERE COUNT(*) > 5) fails, because COUNT(*) doesn't exist until GROUP BY has run.
Filter as early as possible with WHERE. It's almost always more efficient than HAVING, because it shrinks the row set before the often-expensive grouping work happens. Never use HAVING for a condition that WHERE could express.