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
  5. GROUP BY
  6. WITH CUBE or WITH ROLLUP
  7. HAVING
  8. SELECT
  9. DISTINCT
  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

Conclusion

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.

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • 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.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

    Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

    But it doesn't have to be this way.

    I'm Putting together a free email course to help you get started learning SQL Server.

    Name*
    Email*
    >