Basic · Lesson 17 of 17

WHERE vs HAVING

Filter rows before grouping, filter groups after.

The one-line difference

WHERE filters individual rows before any grouping happens. HAVING filters the groups after aggregation, so it can use COUNT, SUM, and AVG. That is why WHERE cannot reference an aggregate.

Use both together

Cut rows down with WHERE first, since it is cheaper, then use HAVING for conditions on the totals. This is one of the most common interview follow-ups.

Examples

WHERE then HAVING

Only recent hires are averaged, then small averages are dropped.

SELECT department_id, AVG(salary) AS avg_salary FROM employees WHERE hire_date >= DATE '2022-01-01' GROUP BY department_id HAVING AVG(salary) > 100000;