I am active online community member and try to help more and more people via my blog and some other forums like Experts-Exchange.com (EE), Asp.net, MSDN etc. in my leisure time. I have recently seen one question in EE where one guy was asking that column alias is not working in his WHERE clause. Obviously, column alias of SELECT clause won’t work in WHERE clause as well as in JOIN clause due to execution order. SQL Server has its own way to execute your SELECT query and if you don’t know how it works, you may fall into this kind of issue. After seeing that question in EE, I decided to write this post.
You SELECT query might consists of SELECT, WHERE, JOIN, GROUP BY etc. clauses in it but SQL Server follows the following order to execute your query.
FROM ON OUTER WHERE GROUP BY ROLLUP | CUBE HAVING SELECT DISTINCT ORDER BY TOP
If you can see “WHERE” clause in in above list then you can find it at 4th position whereas SQL Server know about the column alias you have used in 4th from last position. This is the reason that you can’t get column alias in WHERE clause but you do get it in ORDER BY clause. Let us see it practically by executing simple SELECT query in AdventureWorks2012 database.
USE AdventureWorks2012 GO SELECT SalesOrderID AS SOID ,SalesOrderDetailID AS SODID ,CarrierTrackingNumber ,OrderQty FROM sales.SalesOrderDetail WHERE SOID=43659 --you will be greeted with error something like this: --Msg 207, Level 16, State 1, Line 2 --Invalid column name 'SOID'.
Following query would work as we have proper column name in WHERE clause whereas alias in ORDER BY clause.
USE AdventureWorks2012 GO SELECT SalesOrderID AS SOID ,SalesOrderDetailID AS SODID ,CarrierTrackingNumber ,OrderQty FROM sales.SalesOrderDetail WHERE SalesOrderID=43659 ORDER BY SOID
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.