top of page
Search
Writer's pictureVictor Escott

Logical query processing phases

Updated: Dec 27, 2018

This is one of the most challenging concepts that I had to learn when I started working with SQL.


I am not ashamed to confess that it took me some time to assimilate the way SQL Server processes its code and this is because SQL server does not process statement from top to bottom (like most programming languages)


I am an Accidental DBA, so I was expecting SQL Server to process the code as other programming languages does.

The “keyed-in order"

This is the order that you are supposed to type the query clauses. Please see below.


Logical Query Processing Phases

Here is where the fun begins and this is because the logical query processing order is different. It starts with the FROM clause.


Here is the same query based on “logical query processing order”:

  1. Each phase operates on one or more tables as inputs and returns a virtual table as output.

  2. The output table of one phase is the input to the next phase.

  3. The very last step returns the final query result.

If you are a visual learner like myself, please take look at Itzik Ben-Gan’s flow chart of Logical Query Processing.



Examples:

A common mistake is try to use an ALIAS on the WHERE clause, this is going to throw an error because the SELECT clause is evaluated after a WHERE clause, so the column alias is not known to the WHERE clause.

On the other hand, we can use ALIAS on the ORDER BY clause since it is evaluated after the SELECT clause.

Using a little bit of everything based on the following rules:

  • GROUP BY is processed before the SELECT (we cannot use alias here)

  • HAVING is processed before the SELECT(we cannot use alias here, but we can use COUNT)

  • ORDER BY is processed after the SELECT (we can use alias here)


Thanks for reading. Enjoy!

40 views0 comments

Recent Posts

See All

Comments


bottom of page