Why I cannot use alias name in where clause

 

Ever wonder why when alias columns in "SELECT" clause of a TSQL code, you cannot use them in some other clauses but not on others?

Until I know the truth I was also confused and frustrated.

This is caused by how SQL server query parsing engine works. It process key words/clauses in a particular order.

1. FROM
1. Cross JOIN
2. ON
2. WHERE
3. GROUP BY
1. CUBE/ROLLUP
4. HAVING
5. SELECT
1. Expressions, aggregates and alias
2. DISTINCT
3. TOP
6. ORDER BY
1. OFFSET / FETCH

As you can see from above order, alias is pretty down the order, just before ORDERY BY clause. So you can really use alias in ORDER BY clause only.

Kind of disappointing isn't it?