Click here to Skip to main content
15,867,453 members
Articles / Database Development

Order of Execution in SQL Explained

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
7 Mar 2022CPOL3 min read 5.2K   7   1
In this blog entry we do an overview of the order if execution in SQL.

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:

Image 1

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.

SQL
/* 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
QuestionIs the example correct? Pin
Paul Carmichael8-Mar-22 9:49
Paul Carmichael8-Mar-22 9:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.