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. WHERE
2. GROUP BY
3. WITH CUBE or WITH ROLLUP
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY

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

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.

xxxxxxxxxx

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

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.