Where an alias may be used — Aptitude Question
Where an alias may be used: Why can a column alias defined in the SELECT list be referenced in ORDER BY but not in… Answer and worked solution on CodeKairo.
- Topic: OS, DBMS & Networks
- Section: Programming MCQs
- Difficulty: Medium
- Time target: 90 seconds
Question
Why can a column alias defined in the SELECT list be referenced in ORDER BY but not in WHERE?
Options
- WHERE is evaluated before SELECT, while ORDER BY is evaluated after it
- WHERE accepts only indexed columns
- Aliases are case sensitive inside WHERE
- ORDER BY runs on the client and WHERE runs on the server
Show the answer and the worked solution
Correct answer: A. WHERE is evaluated before SELECT, while ORDER BY is evaluated after it
Worked solution
- A query is evaluated in the logical order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
- When WHERE is evaluated the SELECT list has not been computed, so its aliases do not yet exist.
- ORDER BY is evaluated after SELECT, by which point the alias names a column of the result and can be referenced.
- In short, WHERE is evaluated before SELECT, while ORDER BY is evaluated after it.
Answer: WHERE is evaluated before SELECT, while ORDER BY is evaluated after it.
The idea
The logical clause order explains a whole family of SQL puzzles: why aggregates cannot appear in WHERE, why HAVING can use them, and why ORDER BY may use an alias or even an output column number.
Shortcut. Eliminate the rules SQL does not have: WHERE can filter unindexed columns, letter case is not what blocks the alias, and both clauses run on the server, which leaves evaluation order.
Common trap. "ORDER BY runs on the client and WHERE runs on the server" sounds plausible because sorting feels like presentation, but the database engine runs both.