Execution order of SQL Statement

Written on Apr 24, 2016

The Problem:

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.


Case 2:

And this time it gives below output. This time it recognizes alias name 'Code' in order by clause.


The Solution:

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.

1 FROM
2 ON
3 JOIN
4 WHERE
5 GROUP BY
6 WITH CUBE or WITH ROLLUP
7 HAVING
8 SELECT
9 DISTINCT
10 ORDER BY
11 TOP

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.