Loading...
WHERE filters individual rows before they are grouped. HAVING filters the groups after. Toggle each filter below and watch rows drop, then whole groups drop.
Conor Robertson
July 12, 2026 · 9 min read
The questions that come up most once people start filtering grouped queries for real.
QueryCase teaches SQL through mystery cases on real datasets. Music, sports, film, and more. Your first cases are completely free.
Start your first caseFree to start. No credit card required.
WHERE and HAVING are the two ways SQL filters a query, and almost everyone mixes them up at first. They look interchangeable, both take a condition and throw away what does not match, but they act at two completely different moments, and once you see when each one runs, you will never confuse them again.
That single line down the middle above, GROUP BY, is the whole story. Everything to its left is still loose individual rows; everything to its right has been folded into groups. Flip the toggle to WHERE and the filter reaches back to the left of the line, cutting rows one by one before any group exists. Flip it to HAVING and the filter moves to the right of the line, leaving every row alone and instead cutting whole finished groups. Same data, two filters, two moments.
The one-line answer: WHERE filters rows, HAVING filters groups.
WHERE runs early, while the query is still looking at individual rows one at a time. It can test any raw column, WHERE status = 'open', WHERE amount > 100, but it has no idea about totals, because nothing has been grouped yet. HAVING runs late, after GROUP BY has collapsed the rows into groups and computed their aggregates. That is why HAVING can say HAVING COUNT(*) > 5 or HAVING SUM(amount) > 1000: by the time it runs, those numbers exist.
The mental model
Picture a nightclub. WHERE is the bouncer at the door, checking people one at a time and turning some away before they ever get in. GROUP BY is everyone inside sorting themselves into friend groups. HAVING is the inspector who walks in later and clears out entire groups that are too small or too loud. The bouncer judges individuals; the inspector judges finished groups. Same venue, two filters, two moments.
Here are the two side by side. Notice the only structural difference: WHERE sits before GROUP BY, HAVING sits after it.
Same data, two filters at two moments. WHERE thins the rows on the way in; HAVING judges the totals on the way out.
Everything above comes down to one thing: SQL runs a query's clauses in a fixed logical order, and WHERE and HAVING sit on opposite sides of GROUP BY.
Read it left to right. Rows are read, WHERE thins them, GROUP BY folds them into groups, thins the groups, then and shape the output. Because happens before grouping, it only ever sees one raw row at a time. Because happens after, it sees the finished aggregates. If this pipeline is new to you, it is worth a proper look on its own: explains why your queries behave the way they do, and it is the key that unlocks vs for good.
HAVINGSELECTORDER BYWHEREHAVINGWHEREHAVINGThis is the mistake that sends people looking for the difference in the first place. You write something like this, and the database rejects it:
SELECT city, COUNT(*) FROM cases WHERE COUNT(*) > 5 -- error: aggregate not allowed here GROUP BY city;
It fails because WHERE runs before the rows are grouped, so there is no count yet to compare against. Asking WHERE about COUNT(*) is like asking the bouncer how loud a group is before anyone has walked in and formed one. Picture the toggle at the top stuck on the left of the GROUP BY line: there are no groups over there to count. The fix is always the same: move the aggregate condition down to HAVING, which runs after the counting is done.
SELECT city, COUNT(*) FROM cases GROUP BY city HAVING COUNT(*) > 5;
Every database rejects the first version and accepts the second, for exactly this reason: the count does not exist until GROUP BY has run, and WHERE runs before that.
Here is where it clicks into a real skill. Most reporting queries use both, and each does a job the other cannot. WHERE trims the rows you never cared about, then HAVING judges the groups that are left.
Say you want the busy cities for open cases only: cities that still have more than ten cases once you ignore the closed ones. That is a WHERE (drop closed cases, row by row) followed by a HAVING (keep the big groups).
SELECT city, COUNT(*) AS open_cases FROM cases WHERE status = 'open' -- filter rows first GROUP BY city HAVING COUNT(*) > 10 -- then filter the groups ORDER BY open_cases DESC;
The order matters for more than correctness. Because WHERE runs first, the closed cases are gone before any counting happens, so the database aggregates fewer rows. Filtering early in WHERE is one of the simplest ways to keep a grouped query fast. Reserve HAVING for the part that genuinely needs the totals.
Once the timing clicks, choosing is mechanical. Ask one question: does my condition use an aggregate? If yes, HAVING. If no, WHERE. Read this one backwards, find what you are filtering on the left, and the clause is on the right.
| You want to keep | Use | Why |
|---|---|---|
| Only rows from 2024 | WHERE | A raw column test, no grouping involved |
| Only orders over £100 | WHERE | Per-row value, checked before grouping |
| Cities with more than 5 cases | HAVING | COUNT(*) only exists after GROUP BY |
| Customers who spent over £1,000 total | HAVING | SUM per customer is an aggregate |
| Products whose average rating < 3 | HAVING | AVG is computed per group |
| Open cases, then busy detectives | BOTH | WHERE status = open, then HAVING COUNT(*) > 10 |
| Rows where price > cost | WHERE | Two raw columns compared, still per row |
Good news for interviews and portable code: this is all core standard SQL. WHERE filters rows before grouping and HAVING filters groups after, identically, in PostgreSQL, MySQL, SQLite, SQL Server, BigQuery, Snowflake and Redshift. The aggregate-in-WHERE error shows up everywhere too. Learn the rule once and it travels to every dialect without a single change.
The only modern convenience worth knowing: some databases let you reference a SELECT alias in HAVING (for example HAVING open_cases > 10 using the alias from the query above). Postgres and SQL Server generally do not; MySQL and BigQuery often do. When in doubt, repeat the aggregate, HAVING COUNT(*) > 10, which is valid everywhere.
What happens when you run this query?
HAVING city = 'London' is legal when city is in the GROUP BY, but it makes the database carry that row all the way through grouping before discarding it. The identical WHERE city = 'London' throws it out at the door. If a condition does not touch an aggregate, it belongs in WHERE, every time.
Because WHERE runs before SELECT, an alias you define in the SELECT list does not exist yet when WHERE is evaluated. WHERE total_xp > 300 fails if total_xp is an alias for SUM(xp). This is the same wrap-then-use timing issue that window functions hit, and the fix is the same: put the real expression in the filter, or wrap the query in a CTE and filter in the outer query.
You can filter on an aggregate you never display. GROUP BY city HAVING SUM(amount) > 1000 is fine even if SUM(amount) is nowhere in the SELECT list. HAVING filters on the group's computed values, not on what you happen to be showing.
The fastest way to lock this in is to write it, not read it. Take any grouped query and try to filter it two ways: once on a raw column, once on a count or a total. The first will only accept WHERE, the second will only accept HAVING, and feeling that difference a few times is what turns the rule into instinct.
Once WHERE vs HAVING feels automatic, it slots in beside the rest of the toolkit: GROUP BY and aggregates build the groups, JOIN combines the tables first, and CASE WHEN lets you filter and bucket inside the aggregates themselves. Learn where each one runs, and they stop competing and start working together.