← Blog

WHERE vs HAVING: The Alibi Problem

The difference between WHERE and HAVING in SQL, and how to know which one you need.

You wrote this, and it looked completely reasonable:

SELECT role, COUNT(*) AS headcount
FROM suspect_info
WHERE COUNT(*) > 1
GROUP BY role;

And SQL threw this back:

The problem is not your logic. It's that WHERE and HAVING filter at two different moments, and you reached for the wrong one. Fix that, and this error never confuses you again.

New to SQL entirely? Start with the interactive cases, then come back for this.

The one-line answer

WHERE filters rows before they're grouped. HAVING filters groups after they're formed.

If your condition uses an aggregate like COUNT, SUM, AVG, MIN, or MAX, you need HAVING. If it's a plain condition on a single row, like a name or a location, you need WHERE.

That's the entire rule. The rest of this article is just making it stick, with a body count.

Why they can't be swapped: it's a timing problem

SQL runs the clauses of your query in a fixed order, and it is not the order you type them in. The short version, from step 2 onward:

2. WHERE      filter individual rows
3. GROUP BY   sort survivors into groups
4. HAVING     filter whole groups

Read that top to bottom. WHERE runs at step 2. GROUP BY doesn't run until step 3. HAVING waits until step 4.

So at the exact moment WHERE runs, no groups exist yet. Nothing has been counted. Asking WHERE COUNT(*) > 1 is asking how many people are in a group before anyone has been sorted into groups. There's nothing to count, so SQL refuses.

HAVING runs one step after GROUP BY. By then the groups exist and the counts are real, which is the only reason HAVING COUNT(*) > 1 works and WHERE COUNT(*) > 1 cannot. Same condition, different moment in the query.

If you want the full seven-step sequence, it's laid out in SQL Order of Execution. For WHERE vs HAVING, those three steps are all you need to hold.

WHERE vs HAVING, side by side in the investigation

Here's the case. You're working suspect_info, the passenger manifest from Murder on the Orient Express. Two clauses, two very different jobs.

WHERE dismisses individual suspects.

A detective clears one person because they personally have an alibi. That's a row-level decision, made one suspect at a time.

SELECT name, role, alibi_time
FROM suspect_info
WHERE case_id = 1
  AND alibi_time IS NULL;

This walks the manifest one person at a time and keeps only the suspects whose whereabouts are unaccounted for. What comes back is a plain list of people: a name, a role, and a blank alibi for each one. No counts, no totals, just the individuals who survived the filter. It never groups anything. It's judging suspects on their own facts, which is exactly what WHERE is for.

HAVING dismisses entire groups of suspects.

Now a different kind of reasoning. You're not clearing one person, you're clearing a whole category, and only after you've sorted everyone into categories first.

SELECT role, COUNT(*) AS headcount
FROM suspect_info
GROUP BY role
HAVING COUNT(*) > 1;

GROUP BY role sorts everyone into piles by their role. HAVING COUNT(*) > 1 then throws out every pile with only one person in it, keeping the roles that more than one passenger shares. What comes back is not a list of people at all. It's a list of roles, each with a number beside it: the shared roles, and how many suspects hold each one. You could not ask this question of an individual. "Do you have more than one person in your role" is meaningless said to a single suspect. It's a question about the group.

Both in one query, which is the normal case.

Real queries usually need both, and putting them together is not a conflict. It's the standard pattern.

SELECT role, COUNT(*) AS headcount
FROM suspect_info
WHERE case_id = 1
GROUP BY role
HAVING COUNT(*) > 1;

Read it as the investigation runs. WHERE case_id = 1 clears everyone who isn't part of this case, one person at a time. GROUP BY role sorts the survivors into role piles. HAVING COUNT(*) > 1 keeps only the roles shared by more than one suspect. The result is the same shape as the last query, a list of shared roles with their counts, but now scoped to a single case rather than the whole database. Individuals filtered first, groups filtered second. When you see WHERE and HAVING in the same query, that's what's happening, and it's completely healthy.

Three mistakes, and the fix for each

1. An aggregate in WHERE.

SELECT role, COUNT(*) AS headcount
FROM suspect_info
WHERE COUNT(*) > 1
GROUP BY role;

2. A column alias in WHERE.

SELECT role, COUNT(*) AS headcount
FROM suspect_info
WHERE headcount > 1
GROUP BY role;

I lost the better part of an evening to exactly this early on. I kept moving headcount between WHERE and the SELECT line, convinced I'd made a typo, because the query looked right. It was right. It was just in the wrong clause, asking for a name before that name had been invented.

3. HAVING with no GROUP BY.

SELECT COUNT(*) AS headcount
FROM suspect_info
HAVING COUNT(*) > 5;

No error. This runs. With no GROUP BY, SQL treats the whole result as a single group, so HAVING filters that one group as a unit. It's occasionally useful for a "does this total clear a threshold" check.

But most of the time, a HAVING with no GROUP BY means someone reached for the wrong clause. If your condition is about individual rows, like role = 'Conductor' or location = 'Dining Car', you wanted WHERE. Seeing HAVING with no grouping and no aggregate is a reliable tell that a row filter wandered into the wrong spot.

WHERE vs HAVING: quick reference

Screenshot that. Nearly every WHERE vs HAVING error is one of these five rows being broken.

FAQ

Can you use WHERE and HAVING in the same query?

Yes, and it's common. WHERE filters rows before grouping, then HAVING filters the groups afterward. In your query, WHERE goes before GROUP BY and HAVING goes after it. The two aren't in competition. They do different jobs at different stages.

Is HAVING slower than WHERE?

HAVING isn't slower by nature, but where you put a condition matters. WHERE runs first and shrinks the data before grouping, so fewer rows have to be grouped and counted. When a condition can legitimately go in WHERE, put it there. Save HAVING for conditions that genuinely depend on the group.

Does HAVING work without GROUP BY?

Yes. Without GROUP BY, SQL treats the entire result as one group, so HAVING filters that single group. It's legal in MySQL, PostgreSQL, and SQLite. It's only useful when your condition is an aggregate over everything. If it's a plain row condition, you almost certainly meant WHERE.

Is this the same in MySQL, PostgreSQL, and SQLite?

The core rule is identical across all three: WHERE filters rows before grouping, HAVING filters groups after. The order never changes. One small difference is aliases. MySQL lets HAVING use a SELECT alias like headcount, while PostgreSQL is stricter and wants the full expression. When in doubt, repeat the expression and it works everywhere.

Close the case

You now have the whole rule: WHERE clears suspects one at a time, HAVING clears entire categories once they're formed. The error that sent you here was just the two getting swapped.

The suspect_info table is real, and Case 1 is waiting. Open it, write a HAVING that should have been a WHERE, watch it misbehave, and fix it while it's fresh. That's the part that sticks.

Start Case 1 →