← Blog

Your SQL Query Has Been Lying to You

The real SQL order of execution: why SELECT runs sixth, not first.

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, 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 errors coming up.

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.

Where does JOIN fit? Right at the start, with FROM

Most real cases span more than one table. Your suspects live in suspect_info, but their ticket records might live in tickets. To question both, you JOIN.

Here's the thing beginners miss: JOIN happens inside step 1, alongside FROM. Before anyone is filtered, grouped, or counted, SQL stitches the tables into one combined room of rows. Only then does WHERE start dismissing people.

FROM suspect_info
JOIN tickets ON tickets.suspect_id = suspect_info.id

That order explains a classic trap. A condition in the ON clause runs while the room is being assembled. A condition in WHERE runs after it's assembled. On an INNER JOIN the two behave almost identically, so nobody notices. On a LEFT JOIN they behave very differently, and putting a filter in the wrong one quietly deletes rows you meant to keep. That trap deserves its own case file. For today, just hold the headline: JOIN rides with FROM, at the very start.

Watch it fail, then fix it: WHERE vs HAVING

Theory is nice. Watching a query explode and knowing exactly why is better. Same case, three attempts.

Attempt one. You want roles with more than one person, so you filter on the count:

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

Attempt two. Fine, drop the alias and ask for the count directly:

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

Attempt three. Ask the question at the right moment, after grouping:

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

Now it works.

The rule in one line: filter individuals with WHERE, filter groups with HAVING. If your condition needs a COUNT, SUM, AVG, or any aggregate, it belongs in HAVING, every single time.

One last puzzle. If WHERE can't see the passenger_count alias, why can ORDER BY?

ORDER BY passenger_count DESC

That works fine, and now you know why. ORDER BY runs at step 6, after SELECT created the alias at step 5. WHERE runs at step 2, long before it exists. Same alias, different moment in the story. Once you're reading queries as a timeline, the inconsistency stops being spooky.

If the WHERE-versus-HAVING split is the part that keeps biting you, there's a whole case on it: WHERE vs HAVING: The Alibi Problem.

SQL execution order 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.

FAQ

Does SQL run SELECT first?

No. SELECT runs sixth, near the end. SQL starts with FROM to gather the rows, then works through WHERE, GROUP BY, and HAVING before it ever reaches SELECT. You write SELECT first out of habit. The engine reads it almost last.

Why can't I use an alias in WHERE?

Because the alias doesn't exist yet when WHERE runs. Aliases are created in the SELECT step (step 5), and WHERE runs back at step 2. Use the full expression in WHERE, or move the condition to HAVING if it involves an aggregate. ORDER BY can use the alias because it runs after SELECT.

Is the execution order the same in MySQL and PostgreSQL?

Yes. The logical processing order comes from the SQL standard, so FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT holds in MySQL, PostgreSQL, SQL Server, SQLite, and Oracle alike. The syntax for taking the top rows varies (SQL Server uses TOP or OFFSET/FETCH instead of LIMIT), but the order the clauses are processed in does not change.

Where does JOIN run in the order?

With FROM, at the very start. JOIN builds the combined set of rows before any filtering, grouping, or counting happens. A condition in the ON clause runs as the tables are joined; a condition in WHERE runs afterward, which is why the two can behave differently on a LEFT JOIN.

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 →