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

Part 10 of 18 · ~3 min

HAVING vs. WHERE and Query Execution Order

Someone on the leadership team asks for one more thing on the headcount slide: only show departments with more than one person, so single-person "departments" don't clutter the view. The instinct is to reach for WHERE, and it doesn't work:

SELECT dept, COUNT(*) AS n
FROM Employees
WHERE COUNT(*) > 1
GROUP BY dept;
-- error: misuse of aggregate function COUNT()

The problem is timing. WHERE does its job on individual rows, one at a time, before the engine has grouped anything — there's no such thing as a "group" yet at that point in the process, so COUNT(*) has nothing to count and the engine rightly refuses. What you actually want is a clause that runs after the grouping and aggregation have already happened, evaluating a finished COUNT(*) per group rather than trying to compute one mid-stream. That's HAVING's entire reason for existing:

SELECT dept, COUNT(*) AS n
FROM Employees
GROUP BY dept
HAVING COUNT(*) > 1;
-- IT | 2
-- Sales | 2

There's a mismatch worth pinning down explicitly right here: the order these clauses get typed in and the order the engine actually evaluates them in are two completely different lists, and a good chunk of the "why doesn't this work" confusion scattered through this reference comes down to conflating the two:

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

A handful of everyday puzzles resolve immediately once you have that ordering memorized:

• A name you invented in SELECT isn't visible yet when WHERE runs — try WHERE avg_salary > 60000 where avg_salary only exists as a SELECT alias, and most engines throw it out, since by the time WHERE executes, SELECT hasn't happened yet and that name simply isn't defined. HAVING and ORDER BY sit on the other side of SELECT in the execution order, so, engine depending, they'll frequently resolve the same alias without complaint.
• Filter as early as you honestly can. A condition that belongs in WHERE and gets put in HAVING instead does strictly more work — it lets every row through to the expensive grouping step before throwing rows away, instead of shrinking the set first.
• WHERE dept = 'IT' and HAVING dept = 'IT' produce the same five rows here, which makes it tempting to treat them as interchangeable — they aren't, they just happen to agree on this particular question. Save HAVING for the handful of conditions that can only be checked once an aggregate exists, like "how many," and hand everything else to WHERE.

The two clauses also compose, and combining them is often exactly what a real report question needs. Suppose leadership actually wants "departments where more than one person clears $50,000" — a row-level filter and a group-level one, in the same query:

SELECT dept, COUNT(*) AS n
FROM Employees
WHERE salary >= 50000
GROUP BY dept
HAVING COUNT(*) > 1;
-- Sales | 2   ← the only department left standing

Trace the execution order and it's obvious why this is the only survivor. WHERE salary >= 50000 runs first and drops both Dave (NULL fails the comparison) and nobody else, leaving Alice, Bob, Carol, and Eve. GROUP BY then collapses what's left into Sales (2), IT (1, since Dave was already gone before grouping ever happened), and HR (1). HAVING COUNT(*) > 1 finally throws away every group but Sales. Two different filters, two different clauses, each one doing the job the other structurally can't.