The order clauses actually run in
Written in one order, evaluated in another. Almost every “why can’t I use my alias there” question is answered by this, and so are several performance ones.
10 min · runs against the shop dataset
A SELECT is written SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT. It is evaluated in a different order entirely, and knowing which is the difference between memorising a dozen rules and deriving them.
This is the single most useful piece of trivia in the language, and it is trivia only in the sense that it fits on one card. Almost every confusing error message about a name that does not exist, and a surprising number of performance questions, resolve immediately once you know which step you are standing in. It is also the reason SQL reads the way it does: the written order was chosen to be readable by a person, and the evaluated order is what a database can actually do.
FROMand its joins — decide which rows exist at all.WHERE— throw rows away. No alias from the select list exists yet, because the select list has not run.GROUP BY— collapse what survived into groups.HAVING— throw groups away. Aggregates exist here; individual rows no longer do.SELECT— evaluate the expressions. Now the aliases exist, and window functions are computed.DISTINCT— de-duplicate what the select list produced.ORDER BY— which is why it can use an alias whenWHEREcannot.LIMITandOFFSET— take a slice of what is left.
WHERE uses the expression; ORDER BY uses the alias. Both are correct, for the same reason.
| doublednumeric |
|---|
| 3788.38 |
| 3773.18 |
| 3702.48 |
| 3624.38 |
| 3590.90 |
| 3585.68 |
| 3537.52 |
| 3522.48 |
| 3474.02 |
| 3468.94 |
| 3462.74 |
| 3331.16 |
| 3272.08 |
| 3199.06 |
| 3165.38 |
| 3104.18 |
| 3058.56 |
| 3012.78 |
| 2990.76 |
| 2951.06 |
| 2918.74 |
| 2898.10 |
| 2879.46 |
| 2873.76 |
| 2873.36 |
| 2834.08 |
| 2772.96 |
| 2744.04 |
| 2732.10 |
| 2712.72 |
| 2679.18 |
| 2678.96 |
| 2492.50 |
| 2388.70 |
77 rows · 90 examined · 1 page read
Three consequences fall straight out of that list, and each one is a question people ask constantly and answer by trial and error. None of them needs to be memorised separately — each is a direct reading of where its clause sits.
- *Why can I not use an alias in `WHERE`?* Because at that moment it does not exist. Repeat the expression, or wrap the query.
- *Why is a condition in `HAVING` slower than the same one in `WHERE`? Because `HAVING` runs after grouping, so the rows it discards were grouped first. Anything that filters rows* belongs in
WHERE. - *Why can I not use a window function in `WHERE`?* Because windows are computed at step five and
WHEREis step two. Wrap it in a subquery and filter outside.