← Blog

Your SQL Query Has Been Lying to You

You wrote SELECT first. SQL read it sixth.

That's not a bug, and you're not bad at this. SQL runs its clauses in a completely different order than you type them, and the moment that clicks, three of the most baffling beginner errors quietly confess.

So. Moustache on. We're doing this like Poirot.

Here's your case. Aboard the train, find every role held by more than one person, worst offenders first:

SELECT role, COUNT(*) AS passenger_count
FROM suspect_info
WHERE case_id = 1
GROUP BY role
HAVING COUNT(*) > 1
ORDER BY passenger_count DESC
LIMIT 3;

You read that top to bottom. SQL does not. It works the case the way you would. Round everyone up before you accuse anyone.

1. FROMRound up everyone on the train
2. WHEREDismiss the ones with alibis
3. GROUP BYSort the survivors by role
4. HAVINGDismiss whole groups
5. SELECTAnnounce what you found
6. ORDER BYArrange it, most dramatic first
7. LIMITName only your top suspects

Notice where SELECT lands. The thing you wrote first is second to last. You can't announce findings before you've done the investigation, and neither can SQL.

FROM gathers the room. Every soul on the train, unfiltered, standing in the dining car. You can't filter people you haven't assembled, which is exactly why nothing else runs until this does.

WHERE starts the dismissals, one person at a time. You. Not on this case? Out. And this happens before anyone's been grouped or counted. Remember that. It's the whole reason for the first two errors below.

GROUP BY sorts the survivors into piles. All the conductors together, all the passengers together. From here on, SQL thinks in groups, not people.

HAVING is WHERE's older sibling. It dismisses entire groups. Any role with just one person? Gone. It runs after grouping, which is the only reason it's allowed to say COUNT(*) when WHERE isn't.

SELECT finally decides what goes in the report. This is also where passenger_count is born. It did not exist one moment ago. File that away too.

ORDER BY arranges the reveal, biggest group first. And yes, it can use passenger_count, because that alias exists by now. Timing is everything.

LIMIT takes the top 3. Last, obviously. "Top 3" of an unsorted pile means nothing.

Three errors that now explain themselves

See the pattern? Nearly every confusing SQL error is a clause showing up at the wrong point in the timeline.

The cheat sheet (screenshot this)

One caveat for the pedants. This is the logical order. The database is allowed to physically shuffle things for speed, as long as the answer comes out identical. For writing SQL that actually runs, this is the order that matters.

Now go accuse someone

Reading about it gets you halfway. Putting COUNT(*) in a WHERE clause, watching it explode, and knowing instantly why? That's the half that sticks.

The suspect_info table is real. It's Case 1 of Query the Murder, straight out of Murder on the Orient Express, and it's live in your browser right now. No account, no setup. Just you and a train full of liars.

Start Case 1 →