SQL : Sql Query Execution Order

Most programming languages executes set of statements from Top to Bottom but SQL Server executes set of statements in a logical order which is uniquely decides by the SQL Server in a predefined order known as Logical Query processing phase.

Introduction

When we are developing an application our concern mostly related to the code optimization which enhances the performance of application. When we are talking about an application it has broadly divided into two part one is the our code written in any programming language and other is the code written in database which executes at database level and return the results to our application.
In real world most application facing the performance issue which can be improved by code optimization. Here we will talk about the SQL query optimization.Most programming languages executes set of statements from Top to Bottom but SQL Server executes set of statements in a logical order which is uniquely decides by the SQL Server in a predefined order known as Logical Query processing phase.

Logical Query Processing Phase

When you write set of statements in a SQL query these set of statements divided into logical query processing phase. Each phase generates a series of virtual table feeding into the next phase. Virtual tables are not viewable. These Phases and their execution order are as below :

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

As you can see above "FROM" clause executes first and then "ON" clause.

Let us take an example of join with where condition as you see "ON" executes first and then "WHERE" clause so if you are putting any condition in where clause it will be executed after joining the result so you have to put all the possible filter condition in "ON" clause for better optimized query since it executes before "WHERE" clause.
Let us take a query -

SELECT Salary*12 as CTC WHERE CTC > 500000

If you write above query and execute it in your query designer you will get the error that CTC is not a column defined. This is because in this query there is two clause "SELECT" and "WHERE" in which WHERE executes first and then SELECT so when you are referring CTC in WHERE it is not available cause SELECT clause has not even been executed at the time the WHERE clause is being run.

Let us take another example of OUTER JOIN with ON clause. "OUETR" clause executes subsequent to "ON" clause , so all rows eliminated by the "ON" clause will still be included in "OUTER" clause.

So next time when you are going to write query in SQL server take care of the execution order of SLQ clauses.

Happy reading!!