WHERE vs HAVING — one rule decides every case

WHERE filters rows before they are grouped; HAVING filters the groups after they exist. Everything else about this pair is that sentence plus execution order. If your condition mentions an aggregate — COUNT(*), SUM(...), AVG(...) — it cannot be WHERE, because when WHERE runs, those numbers don't exist yet.

The execution order that explains everything

FROM employees        -- 1. take the rows
WHERE ...             -- 2. drop rows (no aggregates exist yet)
GROUP BY department   -- 3. collapse survivors into groups
HAVING ...            -- 4. drop groups (aggregates NOW exist)
SELECT ...            -- 5. shape the output
ORDER BY ...          -- 6. sort it

WHERE runs at step 2 — before any group or COUNT exists. HAVING runs at step 4 — after. That is the entire distinction; the four cases below are just the rule meeting reality.

Four cases on the real employees table

Case 1 — filter groups by an aggregate: HAVING

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;   -- Engineering 18, Sales 9, HR 8, Marketing 8, Finance 7

Writing WHERE COUNT(*) > 5 instead throws an error in every engine (SQLite: "misuse of aggregate function COUNT()") — WHERE runs before the counting.

Case 2 — filter rows by a plain column: WHERE

SELECT department, COUNT(*) AS hires
FROM employees
WHERE hire_date >= '2020-01-01'   -- rows drop BEFORE counting
GROUP BY department;

This could be written as HAVING-less-efficient tricks, but it shouldn't: cutting rows early means the engine groups less data, and the intent — "only count recent hires" — is a row condition.

Case 3 — both at once

SELECT department, COUNT(*) AS recent_hires
FROM employees
WHERE hire_date >= '2020-01-01'   -- which ROWS count
GROUP BY department
HAVING COUNT(*) >= 4;             -- which GROUPS survive

On the real data this returns all 5 departments (Engineering 7, HR 7, Marketing 6, Sales 6, Finance 4). Move the date into HAVING and you get the same answer slower; move the count into WHERE and you get an error. Each condition has exactly one correct home.

Case 4 — HAVING on a non-aggregate: legal, but a smell

HAVING department != 'HR' runs — grouped columns are visible to HAVING — but it filters after doing the grouping work for HR and then discarding it. Write it as WHERE. Rule of thumb: if the condition has no aggregate in it, it belongs in WHERE.

▶ Practice: GROUP BY Basics

FAQ

Can HAVING exist without GROUP BY?

Yes — the whole result acts as one group: SELECT COUNT(*) FROM orders HAVING COUNT(*) > 100 returns either one row or none. Rare, occasionally handy for "only report if threshold met."

Can I use an alias in HAVING?

Engine-dependent: SQLite, MySQL and Postgres accept HAVING headcount > 5; SQL Server and Oracle make you repeat HAVING COUNT(*) > 5. Repeating the aggregate is the portable spelling.

Is HAVING slower than WHERE?

For conditions that could go either way (non-aggregates), yes in principle: WHERE cuts rows before grouping does its work. For aggregate conditions the comparison is moot — WHERE cannot express them at all.