Where vs Having in SQL

The query that refuses to run

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
WHERE AVG(salary) > 90000;

That fails before it even checks the condition:

syntax error at or near "WHERE"

WHERE has to come before GROUP BY, and even moved into the right position it still won't work, because WHERE AVG(salary) > 90000 isn't legal SQL: WHERE runs before grouping exists, so there's no average yet to compare. Swap the keyword for HAVING and it runs.

That much every tutorial on this topic covers: WHERE filters rows before grouping, HAVING filters groups after. What most of them stop short of showing is that this isn't just a syntax rule. Put a condition in the wrong one of the two and you don't get an error, you get a different, silently wrong number, because the two clauses don't just run at different times, they filter different sets of rows before the aggregate is computed.

Same-looking filter, different answer

Say you want each department's average salary, counting only employees hired since 2020. Written with WHERE, the filter removes rows before AVG ever sees them:

Now write what looks like the same idea, but with the date check moved to HAVING and a condition on the group's earliest hire instead:

The first query returns all five departments, each with its post-2020 hires averaged. The second returns nothing at all: every department has at least one employee hired before 2020, so every department's MIN(hire_date) fails the condition and the whole group is dropped, salary average and all. The two queries don't return the same rows filtered a different way, they answer different questions: "what's the average salary of employees hired after 2020, regardless of who else is on their team" versus "which departments were formed entirely after 2020." HAVING here throws out a whole department over a single old hire, it never gets the chance to narrow which salaries the average is computed from. This is the actual risk: WHERE and HAVING conditions that sound interchangeable in English compute the aggregate over different rows, so the one you pick changes the number, not just where the filter happens to sit in the query.

Using both in one query

Because they run at different stages, WHERE and HAVING aren't a choice between two options, they can both appear, doing their separate jobs. Keep only completed orders, then keep only the product categories where that filtered set still totals more than 3 orders:

WHERE o.status = 'completed' runs first and throws out cancelled and pending rows before any grouping happens. GROUP BY then collapses what's left into one row per category, and HAVING COUNT(*) > 3 drops categories whose surviving, completed order count is too small. The count HAVING checks is already the post-WHERE count, they compose instead of competing.

HAVING without GROUP BY

HAVING doesn't require GROUP BY. Without one, the entire result set is treated as a single group, which makes HAVING behave like a filter on one aggregate row:

Company-wide average salary is under $100,000, so HAVING throws out the one row this query would otherwise produce and you get an empty result, not an error. This is a useful check when you want a query to return nothing unless some company-wide condition holds, but it's also a common source of "why did my query return zero rows with no error message" when someone adds a HAVING clause without noticing there's no GROUP BY above it.

The portability trap: aliases in HAVING

Every example above spells out the aggregate inside HAVING: HAVING AVG(salary) > 90000, not HAVING avg_salary > 90000. That's not just style. DuckDB, which runs the playgrounds on this page, is unusually permissive and lets you reference a SELECT-list alias directly inside HAVING:

That runs here and returns the same result as the version written out in full. It will not run on PostgreSQL or SQL Server. Standard SQL resolves HAVING before the SELECT list's aliases exist, so referencing one is an undefined-column error on most engines; DuckDB and MySQL extend the standard to allow it as a convenience.

Execution order, for reference

The reason all of the above holds is the order SQL actually evaluates a query in, which doesn't match the order you type it:

1. FROM / JOIN, assemble the rows
2. WHERE, filter individual rows
3. GROUP BY, collapse the survivors into groups
4. HAVING, filter the groups
5. SELECT, compute the output columns
6. ORDER BY / LIMIT

WHERE only ever sees ungrouped, unaggregated rows, and HAVING only ever sees the already-grouped result. Neither can do the other's job, and the "different answer" trap above is just a consequence of step 2 and step 4 operating on different inputs.

Where to go next

The WHERE lesson and HAVING lesson cover each clause's syntax on its own if either felt shaky above, and the GROUP BY with HAVING example is a shorter, syntax-focused version of the basic case. For the error you get when a SELECT column isn't grouped or aggregated at all, see why SQL says a column must appear in the GROUP BY clause; it's the same underlying rule from the other direction. The practice problems have several grouping exercises worth trying once you want to build the "which clause does this belong in" judgment call without the reference queries in front of you.