Consider below SQL query in AdventureWorks2014 database.Case 1:
Above query result in error, saying "Invalid column name 'Code'". Here our query doesn't recognize alias name 'Code' in where condition.
Now, let's consider another query.
And this time it gives below output. This time it recognizes alias name 'Code' in order by clause.
In order to know this dual behavior of SQL Server, we need to know about execution order of SQL Queries.
Logical processing order of select statement is as below. For more details refer this link.
|6||WITH CUBE or WITH ROLLUP|
Here, it is now clear that logical processing of where clause occurs prior to select clause. So alias defined in select clause is not accessible in where clause. That’s the reason for case-1 behavior.
And in case-2 it is not giving error, reason behind is also the same. Logical processing of order by clause occurs after logical processing of select clause. So alias columns are accessible in order by clause.