It's important to understand that there is a specific order you write SQL query clauses in, as well as a specific order that they are actually executed in by MySQL engine.
You write SQL query clauses in this order:
- SELECT
- FROM/JOIN
- WHERE
- GROUP BY
- HAVING
- ORDER BY
- LIMIT
The order that the clauses are run in by the MySQL engine is as follows:
- FROM/JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
- LIMIT
This is why you can only use column aliases from the SELECT clause in the ORDER BY clause. Understanding the order that MySQL actually runs your clauses in can help you to better understand how your query works or doesn't work.
There is a setting in MySQL that overrides this default behavior for the GROUP BY and HAVING clauses, which are enabled by default, so MySQL will allow you to use column aliases in them. The other RDBMSes...
