You might wonder thinking why can't you refer column aliases in where clause like the following query.
SELECT CategoryName AS [Name] From [dbo].[Category] WHERE Name ='Bike'
Well you can't because SQL server executes the query in it's logical processing order for select statement. The result set along with it's columns is passed to next step after its completion. Then it passes to the next steps and so on.
The actual physical execution of the statement is determined by the query processor and it is in the following order.
- GROUP BY
- WITH CUBE or WITH ROLLUP
- ORDER BY
Now as you can see WHERE clause executes before SELECT clause, so the alias name of a column or a derived column of SELECT clause cannot be used in WHERE clause. However, the same can be referenced by subsequent clauses such as the ORDER BY clause.