SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

HAVING vs. WHERE and Query Execution Order

WHERE filters individual rows before grouping happens; HAVING filters groups, after aggregation — which is exactly why HAVING can reference an aggregate like COUNT(*) and WHERE cannot (at the point WHERE runs, no aggregation has happened yet, so there's nothing to aggregate):

SELECT dept, COUNT(*) AS n
FROM Employees
GROUP BY dept
HAVING COUNT(*) > 1; -- only departments with more than one employee

It helps to know SQL's actual logical processing order, since it doesn't match the order you write the clauses in:

FROM  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY  →  LIMIT

A few consequences fall directly out of this order:

• You can't reference a column alias defined in SELECT inside a WHERE clause on the same query (WHERE avg_salary > 1000 when avg_salary is a SELECT-list alias fails on most engines) — WHERE runs before SELECT does. HAVING and ORDER BY, which run after SELECT, often can reference the alias, depending on the engine.
• Filtering something as early as possible (in WHERE rather than HAVING) is both more correct and generally faster, since it shrinks the row set before the expensive grouping/aggregation step rather than after.
• WHERE dept = 'IT' before grouping and HAVING dept = 'IT' after grouping can produce the same answer here, but they're doing meaningfully different amounts of work, and only one of them is idiomatic — reserve HAVING for conditions that genuinely depend on an aggregate.