WHERE vs HAVING in SQL

WHERE filters individual rows before the engine groups them. HAVING filters the groups that come out of the aggregation. The logical order of evaluation is FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT — so WHERE prunes input and HAVING judges output.

That order is not cosmetic. A predicate in WHERE removes rows before they are grouped, so it changes the aggregates: a WHERE on amount > 100 changes what COUNT and SUM see. A predicate in HAVING sees the finished aggregate and can only discard whole groups, so it changes which groups survive but never what is inside one.

The other practical difference is what each clause can reference. WHERE works on raw column values of a single row, so it can use an index on that column. HAVING works on aggregate results and aliases, which are computed after the scan, so an index on the underlying column generally cannot help unless the optimizer can prove the equivalence.

Two rules of thumb settle most arguments. If the condition mentions no aggregate and no SELECT alias, it belongs in WHERE. If it mentions COUNT, SUM, AVG, MIN, MAX or an alias, it has to go in HAVING — or, better, be rewritten so the filter can happen in WHERE at all.

There is a common exception worth knowing: WHERE cannot contain a window function, because window functions are evaluated after GROUP BY. A query that filters on a window result — the classic "top row per group" pattern — must use a CTE or a subquery with the window computed first, then filter outside it.

The point of this pair is that WHERE and HAVING are not interchangeable spellings of the same idea. Moving a filter from one to the other changes the aggregates, not just the row count.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 1,  90.00),
  (4, 2,  75.25),
  (5, 2, 610.00);

-- Filter before grouping: only qualifying orders are counted.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
WHERE amount > 100
GROUP BY customer_id
ORDER BY customer_id;

-- Filter after grouping: every order counts, then groups are discarded.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 600
ORDER BY customer_id;

The first query returns two rows: customer 1 with 2 orders totalling 460.50, and customer 2 with 1 order totalling 610.00. The 90.00 and 75.25 orders never entered the group, so customer 1's count is 2 rather than 3.

The second query returns one row: customer 2, with 2 orders totalling 685.25. Customer 1's group was computed over all three of its orders — 550.50 in total — and was then discarded because that total is not above 600.

Read them as two different questions. The first asks "among orders over 100, which customers appear and what did they spend on those orders?" The second asks "which customers spent more than 600 in total, and how many orders was that?" If the report you were given said "customers who spent over 600" and the number of orders was wrong, this is exactly the mistake that was made.

It should not happen with a plain filter on a raw column, so treat it as a signal that the two versions are not equivalent — and then check the plan, because the usual explanations are all about how the optimizer is estimating work.

First possibility: the HAVING version was filterable by an index while the WHERE version is not. A HAVING predicate such as HAVING COUNT(*) > 100 can sometimes be pushed down into an index scan that only touches groups with enough rows, and the optimizer may have chosen a different strategy once you moved the predicate. Compare the two plans rather than assuming they are the same query with a different clause.

Second possibility: the rewrite changed the aggregates. A predicate on a raw column in HAVING — which most engines allow — is evaluated on the grouped result, while the same predicate in WHERE changes which rows enter the group. If the totals changed, the report is now answering a different question, and a different question can legitimately cost more or less.

Third possibility: you hit a cardinality estimation cliff. Moving a predicate changes the estimated selectivity, and the optimizer may now believe a nested loop is cheapest when a hash join was actually right. This is the classic reason a "faster" rewrite becomes slower after the data volume changes.

Fourth possibility: the timing was noise. A single cold run is not a measurement; cached plans, a cold buffer pool and an unlucky checkpoint can dominate a small difference. Compare execution plans, row counts and buffers read, and run each query several times.

The right fix is to make the two versions semantically identical first — push the predicate into a CTE, aggregate, then filter on the aggregate — and only then compare plans. That removes the possibility that you optimised two different queries against each other.