Do You Want to Learn SQL?

Take our free eight day course, where I'll teach you in simple to understand English all you need to get started learning SQL.


Free Beginners SQL Course!

Order of Execution in SQL Explained



Knowing the order of execution in SQL helps you better understand SQL’s “hair pulling” errors! As you start to work with SQL you find that some of the errors don’t make sense or you wonder why you’re able to use a column alias in the ORDER BY clause but not in a join condition.

As you get to understand the order SQL’s query processor “reads” SQL and processes it to execute your query, you start to understand why this is the case.

Given that humans read SQL as it appears on our screen, it makes sense that we would think the computer would read SQL the same way.

But this is not so.  In fact, how the query engine approaches SQL will seem scattered.  Here is the official order of execution from Microsoft:

  1. FROM
  2. ON
  3. JOIN
  4. WHERE
  10. ORDER BY
  11. TOP

Let’s look at an example to see how order of execution in play:

Order of Execution In SQL Example
SQL Order of Execution (click image to enlarge)

Notice how we tend to read the statement top-down, and left to right, the DBMS query processor is first examining the FROM statement, then moving on from there.

It may see strange that the column aliases in the SELECT (step 8) are one of the last portions evaluated in our example!


Verbal Explanation of SQL Order of Execution

This is what is happening.

Before the query processor runs the query it first needs to know what tables are involved in the query.  Therefore, the query processor first  evaluates the FROM and JOIN clauses (Steps 1, 2, and 3).

Step 4 filters out rows within the WHERE clause. 

The query processor now has the correct information to “know” how ask for the data.  Within our example the focus now turns to preparing the result’s appearance.

In step 8 the display columns are prepared by the query processor.  Here the processor assigns any column aliases. 

Finally, in step 10, the query processor prepares the ORDER BY clause.

Why Should I know This?

Knowing the order of execution is helpful when you’re writing and troubleshooting your SQL.

One you know the order of execution the following idiosyncrasies make sense:

  • Why you can use a table alias throughout your SQL, but not a column Alias.
  • Why you can use a column alias in the order by clause.
  • Why you cannot use column aliases within the WHERE clause.

Whenever I’m writing SQL, I keep the order of execution in mind.  Especially when I go to run SQL and I get a syntax error!  When that happens, I think about the error and whether I’m trying to use a column name or other object before the query processor “knows” about its existence.

Why do you try it out for yourself?

This statement has an error in it.  Try running it, find the error, and see if you can fix it.

SELECT p.ProductNumber, p.Name ProductName, i.Bin, i.LocationID, i.Quantity FROM Production.ProductInventory i INNER JOIN Production.Product p on i.ProductID = p.ProductID WHERE ProductName like '%Mountain%' ORDER BY ProductName
/* Answer */
/* Notice you can not use an aliased column name in the WHERE clause */
SELECT p.ProductNumber, p.Name ProductName, i.Bin, i.LocationID, i.Quantity
FROM   Production.ProductInventory i
       INNER JOIN Production.Product p on i.ProductID = p.ProductID
WHERE  p.Name like '%Mountain%'
ORDER BY ProductName


Knowing how SQL processes your SQL certainly help you understand how to write better SQL and understand why some SQL passes as legal SQL, but other queries do not pass.

Here are some insights I’ve gained that I hope also help you:

1.  I always wondered why I couldn’t use aliases in my WHERE clause…  now I know, as the aliases aren’t discovered until a later step than the WHERE clause.

2.  Likewise, I wondered why I could use a column alias in the order by, which is at the bottom of the statement, but not in the middle.

2 responses to “Order of Execution in SQL Explained”
  1. Prathyusha

    Thanks for this blog, Kris. This was helpful and cleared my confusion of table alias and column alias and when to use each of them.

    1. You are most welcome!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SQL Server