What is Difference Between HAVING and WHERE – Interview Question of the Week #068

Question: What is the difference between WHERE and HAVING? WHERE filters input rows. HAVING filters groups or the result of an aggregate, after the relevant grouping in the logical query processing.

A prospective consulting client once interviewed me before a performance-tuning project. I welcome that, because it means they care about who will work on their database. This question prompted a useful discussion when my practical answer did not quite match the textbook phrasing.

Keep the original publisher example

The original query selected California publishers, then kept only publishers whose average title price exceeded 10. Here is the same relationship with self-contained private data, so the old pubs sample database is not required:

DECLARE @Publishers TABLE(pub_id int PRIMARY KEY,state char(2));
DECLARE @Titles TABLE(pub_id int,price decimal(10,2));
INSERT @Publishers VALUES(1,'CA'),(2,'CA'),(3,'NY');
INSERT @Titles VALUES(1,8),(1,16),(2,6),(2,10),(3,30);
SELECT p.pub_id,AVG(t.price) AS AveragePrice
FROM @Titles AS t JOIN @Publishers AS p ON t.pub_id=p.pub_id
WHERE p.state='CA'
GROUP BY p.pub_id HAVING AVG(t.price)>10;

-- No GROUP BY: one implicit aggregated group, not row filtering.
SELECT COUNT(*) AS CaliforniaTitles
FROM @Titles AS t JOIN @Publishers AS p ON t.pub_id=p.pub_id
WHERE p.state='CA' HAVING COUNT(*)>=4;

The first result is publisher 1 with average price 12.000000. WHERE excludes the New York publisher’s input rows. GROUP BY calculates an average for each California publisher. HAVING excludes publisher 2, whose average is 8.

HAVING without GROUP BY still has an aggregate group

The second query treats the qualifying California titles as one implicit group and returns count 4. It does not make HAVING behave like a row-by-row WHERE clause. An aggregate condition such as AVG(price)>10 belongs in HAVING for this query, not WHERE.

Some predicates on grouping keys can legally be written in either place, and the optimizer may produce equivalent plans. That does not guarantee equal performance for every possible rewrite. Put a row-level condition in WHERE when that is its meaning, and compare actual plans when performance is the question.

Logical processing describes the query’s semantics, not a promise that the engine physically executes operators in that exact order. The optimizer can move a safe predicate without changing the answer.

GROUP BY explains the grouped calculation.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.