SQL WHERE vs HAVING

The textbook answer, “WHERE filters rows and HAVING filters groups”, is correct, and it’s also where most candidates stop. The interesting part is what follows from it. The same condition can mean two different things depending on which clause it’s in, and some databases will happily run a query that PostgreSQL rejects, giving you an answer that looks plausible and is wrong.

Everything below uses one small table of orders. Four customers, ten rows:

 customer | status    | amount
----------+-----------+--------
 ana      | paid      |    120
 ana      | paid      |     80
 ana      | refunded  |    300
 ben      | paid      |     40
 ben      | paid      |     35
 ben      | paid      |     30
 cho      | paid      |    500
 cho      | cancelled |     20
 cho      | cancelled |     25
 dev      | refunded  |     60

All query output shown here came from running the statements against SQLite 3.53. Where PostgreSQL behaves differently, and there are two such places, the article says so.

The order a SELECT is evaluated in

You write a query starting with SELECT, but the database evaluates it in a different logical order:

1. FROM and joins build the set of input rows.
2. WHERE throws away individual rows.
3. GROUP BY collapses the surviving rows into groups.
4. Aggregates (count, sum, avg) are computed per group.
5. HAVING throws away whole groups.
6. SELECT computes the output expressions and aliases.
7. ORDER BY, then LIMIT.

Almost every WHERE versus HAVING question can be answered from that list. WHERE runs before any aggregate exists, so it can’t refer to one. HAVING runs after grouping, so it can only refer to things that have one value per group: the grouping columns and aggregates. And since aliases are created at step 6, a clause that runs earlier can’t, in standard SQL, see them.

(The real executor is free to reorder work as long as the result is the same. PostgreSQL, for example, moves a HAVING condition that contains no aggregate down into the row filter. The logical order is about meaning, not about what the engine physically does first.)

Same condition, different question

Suppose the request is “customers with at least three paid orders”. The status filter belongs on rows, so it goes in WHERE, and the count threshold goes in HAVING:

SELECT customer, count(*) AS n
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING count(*) >= 3;

 customer | n
----------+---
 ben      | 3

Only ben qualifies. Ana has two paid orders, cho has one. Now drop the WHERE and keep the HAVING:

SELECT customer, count(*) AS n
FROM orders
GROUP BY customer
HAVING count(*) >= 3;

 customer | n
----------+---
 ana      | 3
 ben      | 3
 cho      | 3

Three customers, because now every order counts, including refunds and cancellations. Nothing errored. The query answered “customers with at least three orders of any kind”, which is a different question. This is the core of the topic: a filter in WHERE decides what gets aggregated, while a filter in HAVING decides which aggregated results you keep. Moving a condition between them is never a neutral refactor.

Sometimes you genuinely need both views at once, for example “customers with at least three orders in total, of which at least one was paid”. Then the row condition can’t go in WHERE, because it would remove the rows the total needs. Move it into the aggregate instead:

SELECT customer, count(*) AS n
FROM orders
GROUP BY customer
HAVING count(*) >= 3
   AND count(*) FILTER (WHERE status = 'paid') >= 1;

That keeps ana, ben and cho (dev has only one order). FILTER is PostgreSQL’s (and the standard’s) way of restricting a single aggregate; sum(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) does the same on engines without it.

The errors each clause raises

Put an aggregate in WHERE and every database refuses. SQLite says misuse of aggregate: count(); PostgreSQL says aggregate functions are not allowed in WHERE. Both are telling you the same thing as step 2 of the list: no groups exist yet.

The second error is the more interesting one, because not every engine raises it. Here’s a query that puts a row-level condition in HAVING:

SELECT customer, count(*) AS n
FROM orders
GROUP BY customer
HAVING count(*) >= 3 AND status = 'paid';

PostgreSQL rejects it: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function. That’s correct. Each group has several rows with different statuses, so “the group’s status” has no single value. SQLite, which allows bare columns in grouped queries, runs it and returned all three customers: ana, ben and cho. It evaluated status against whichever row of each group it happened to pick. Cho has a paid order and two cancelled ones, and still passed. MySQL with ONLY_FULL_GROUP_BY disabled behaves similarly. If you learned SQL on one of those engines, this is the habit to unlearn.

The third difference is about aliases:

SELECT customer, sum(amount) AS total
FROM orders
GROUP BY customer
HAVING total > 200;

SQLite and MySQL accept this and return ana (500) and cho (545). PostgreSQL fails with column "total" does not exist, because the alias is assigned after HAVING runs. Repeat the expression, HAVING sum(amount) > 200, which works everywhere. Aliases are allowed in ORDER BY in PostgreSQL, since that clause runs last.

Cost: why row filters belong in WHERE

Even when a condition is on a grouping column, so it would give the same answer in either clause, WHERE is the right home. WHERE customer = 'ana' can use an index on customer and discard the other customers’ rows before any grouping. Written as HAVING customer = 'ana', the logical meaning is “build every customer’s group, then keep ana’s”. As noted earlier, PostgreSQL’s planner will usually move that condition down for you, but not every engine does, and a reader shouldn’t have to know which optimiser rules apply to understand your query. Row conditions in WHERE, group conditions in HAVING, and the query documents itself.

There’s one more case worth knowing: HAVING without GROUP BY. Then the whole table is one group, and HAVING either keeps that single row or removes it. SELECT count(*) FROM orders HAVING count(*) > 100 returns no rows at all on our ten-row table, not a row containing 10. That’s occasionally handy for “return the total only if it crosses a threshold” and frequently surprising.

Interview exercise: high-value paid customers

Write a query for “customers whose paid orders add up to more than 100”, and explain what goes wrong with this attempt:

SELECT customer, sum(amount) AS total
FROM orders
GROUP BY customer
HAVING sum(amount) > 100;

Answer. The attempt sums every order, so refunds and cancellations inflate the totals. It reports ana at 500, though 300 of that was refunded, and cho at 545, though only 500 was paid. It also includes ben at 105, which happens to be right only because all of his orders are paid. The status condition is about individual rows, so it belongs in WHERE, before the sum:

SELECT customer, sum(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING sum(amount) > 100;

That returns ana 200, ben 105 and cho 500. Explaining why (the WHERE clause changes the input to sum, the HAVING clause only filters the outputs) is the part interviewers listen for. If you’re also asked to show each customer’s gross total alongside, keep all rows and use sum(amount) FILTER (WHERE status = 'paid') for the paid figure, with the threshold in HAVING on that filtered sum.

More on grouping

• SQL interview questions and SQL MCQs
• The “must appear in the GROUP BY clause” error
• COUNT(*), COUNT(column) and COUNT(DISTINCT)
• PostgreSQL docs: GROUP BY and HAVING