What is the difference between WHERE and HAVING? | Interview Question

Q: What is the difference between WHERE and HAVING?

**Target Audience:** Beginners who have just met GROUP BY and are unsure which filter goes where. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; MySQL notes come from its manual and are marked. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. All output and error messages were produced by actually running the queries; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A teacher has a pile of exam papers and wants to know which classes did well. Two very different filters come up: 1. "Ignore the papers that were handed in late." That is a decision about **each paper**, made before anything is added up. 2. "Only show classes whose average is above 70." That is a decision about **each class**, and it can only be made after the papers a

The problem

Question presented to candidate: "Both WHERE and HAVING filter rows, so why does SQL need two of them? Using an orders table, show me a query that needs both, and tell me what goes wrong if you put a condition in the wrong one."

What a strong answer should cover:

WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and the aggregates are computed
WHERE cannot use aggregate functions; HAVING is where conditions like SUM(amount) > 200 or COUNT(*) >= 2 go
Moving a row condition from WHERE to HAVING changes the answer when it is not on a grouped column, and putting a filter in WHERE can remove whole groups (conditional aggregation keeps them)
HAVING without GROUP BY treats the whole table as one group; a HAVING condition on a grouped column is legal but belongs in WHERE (PostgreSQL pushes it down)
Engine notes: SQLite allows HAVING without GROUP BY only since 3.39.0; MySQL and SQLite accept SELECT aliases in HAVING, PostgreSQL does not

Code / implementation expected: Yes — one GROUP BY query with a WHERE row filter and a HAVING group filter.

Schema used by the examples:

where
having

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who have just met GROUP BY and are unsure which filter goes where. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15; MySQL notes come from its manual and are marked.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. All output and error messages were produced by actually running the queries; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A teacher has a pile of exam papers and wants to know which classes did well. Two very different filters come up:

"Ignore the papers that were handed in late." That is a decision about each paper, made before anything is added up.
"Only show classes whose avera

The solution is hidden so you can try first

11 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLWHERE drops refunded rows, HAVING drops small customers; without the WHERE the answer changes

SQLWHERE can remove a whole group; conditional aggregation keeps it

SQLHAVING without GROUP BY, and HAVING on an aggregate that is not selected

PostgreSQL: a HAVING condition on a grouped column is pushed down into the scan

EXPLAIN (COSTS OFF)
SELECT customer, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING customer <> 'Ben' AND SUM(amount) > 200;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.