A colleague spent twenty minutes on a query once. It was returning the top ten orders correctly. Then she added a WHERE clause and the wrong orders started coming back.

She assumed the WHERE was broken. It wasn't. She thought LIMIT 10 ran first, and the sort happened on those ten rows. The database doesn't work that way. It sorts everything, then trims. She drew it on a whiteboard and never made the same mistake again.

I've taught WHERE and ORDER BY many times. Every group has someone who gets stuck on the same three things: NULL sorting, aggregate in WHERE, missing parentheses. They're not hard ideas. They're just the three places where people's gut feeling is wrong.

Here's where the gut feeling is wrong.

The database runs your query in a different order than you wrote it

You write top to bottom. The database runs it in a completely different order.

FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT.

That's the whole pipeline.…