Set Operator Precedence: Explained

In this tutorial, we will discuss a very useful topic you should know when querying SQL Server databases: Set Operator precedence.

The three set operators available to us in SQL Server are UNION, INTERSECT, and EXCEPT.  I have in-depth tutorials on all 3 different set operators, found there:

The UNION Set Operator: Everything you need to know.

The INTERSECT Set Operator: Everything you need to know.

The EXCEPT Set Operator: Everything you need to know.

You should read those tutorials first, then read this tutorial on set operator precedence, then you’re training will be complete. You will be a master of the set operators.

In this tutorial, we will cover the following topics:

1. What is the precedence of set operators?
2. An example of set operator precedence
3. Tips, Tricks and links

Don’t forget to Download your FREE 1-page Simple SQL Cheat Sheet on Set Operators!

Let’s spar.

1. What is the precedence of set operators?

Here it is:

INTERSECT has the highest precedence.

UNION/UNION ALL and EXCEPT have the same precedence.

Since UNION and EXCEPT have the same precedence, if they both appear in a query, they are simply evaluated form left to right.

For example, take a look at this query that uses both UNION and EXCEPT:

Just to give you some quick background information: We pretend you are a business owner with 3 stores in Denver, Boulder, and Colorado Springs. The DenverBranch, BoulderBranch, and ColoradoSpringsBranch tables all store information about customers who shop at your stores in their respective cities. Some customers are referenced in more than one table, if they shop at more than one location.

But anyway, back to our topic.

Since we’re only using UNION and EXCEPT in the query above, we really don’t need to worry about precedence. We just have to take note of the order of the operators, from top to bottom (SQL refers to it as ‘from left to right‘).

So, the process would work like this: The EXCEPT operator appears first, so SQL will do the EXCEPT operation between the BoulderBranch table and the ColoradoSpringsBranch table first.

Then, the result set of that process will be used as the left input to the next UNION operator. Of course, the right input to the UNION operator will be the SELECT statement against the DenverBranch table.

But what if we flipped things around, and used UNION first and EXCEPT second? Would the final result set be different?

Answer: Most likely.

Here’s the two, side by

Yep, definitely different.

2. An example of set operator precedence

We already sort-of looked at an example, but let’s look at more examples.

Let’s take the following query:

Ok, so remember the order of precedence. Which operation will happen first?

It’s the INTERSECT between the BoulderBranch table and the ColoradoSpringsBranch table.

Nice, let’s see what the result set of just that operation will be:

Cool, only two rows.

So then, this result set is used at the right input to the EXCEPT set operator. Of course, our left input will be the SELECT statement against the DenverBranch table.

So now, lets see if we can predict what the final query will look like

Let’s see who is in just the DenverBranch table:

Ok, cool. So let’s think about what the final query will present to us.

We’re going to see everyone who shops in Denver, except for those who shop at both the Colorado Springs and Boulder stores.

Said another way: “Show me my Denver customers, but if they shop at both Colorado Springs AND Boulder, I don’t want to see them!”

So, let’s look at our two result sets side by side:

So, in our final result set, we’re going to see everyone who appears in the first result set, except for those who also appear in the second result set.

That means we’ll see everyone in the first result set except for Patti Goldsworth and Sara Winchester. Of course, Patti Goldsworth isn’t even in the first result set, so really it’s just Sara Winchester who will get removed.

Let’s see if we’re right:

Well done.

Precedence should be a familiar thing 

I want you to remember something: Because of the precedence of set operators, the INTERSECT operator was ran before the EXCEPT operator, despite the INTERSECT operator coming after the EXCEPT operator.

This is very similar to the ‘order or operations’ principle in math equations. If you remember basic high school math, you’ll know how to solve this problem:

1 + 4 x 2 = ???

It’s 9. You do the multiplication between 4 and 2 first (resulting in 8), then the addition of 1, resulting in our final answer of 9. It doesn’t matter that the multiplication comes after the addition. By the rules of math, you do the multiplication first!

It’s the same idea with set operator precedence.

Another example

Now, I want to play a game.

Take the following individual queries:

If I run this whole query, what will the final result set look like? You might need to take out a piece of paper to figure this out.

Answer:

Well done. 

Let’s talk about how it came to this final result set. So the first thing that will happen is the INTERSECT between DenverBranch and BoulderBranch. This happens first because INTERSECT has the highest precedence, and NOT because INTERSECT happens to be the first operator.

The result set of that first operation is this:

Ok, so in the final result set, we will see all these people except for those who are in the ColoradoSpringsBranch table.

The next logical question is: “Are any of those people in the ColoradoSpringsBranch table?”

Let’s check. Is Doug, Emily, June, or Sara in the Colorado Springs table?:

That pesky ol’ Sara Winchester.

So, in the final result set, Sara Winchester gets removed, leaving us with Doug, Emily, and June. Here it is again:

3. Tips, Tricks and links

Here is a list of some helpful tips and tricks you should know about set operator precedence:

Tip # 1: You can use parentheses to force precedence

The only real trick to know is how you can use parentheses to force a certain precedence.

Take this example:

What if we specifically wanted the UNION operator to run first, instead of the INTERSECT operator?

You would put parentheses around the UNION operator between the BoulderBranch table and ColoradoSpringsBranch table, which would force it to run first.

So if we’re being honest, parentheses actually have the highest precedence.

Let’s see what we get:

Nice. (As a side note, you aren’t required to indent the text in parentheses like I have. I just think it reads a bit easier)

Compare that to the result set if we didn’t use parentheses:

This is much different!

Again folks, this is similar to your high school math problem:

(1 + 4) x 2 = ??? 

This time, 1 + 4 is performed first since it is in parentheses, which gives us 5.

Then, we multiply 5 by 2, resulting in our final answer of 10.

Set operator precedence is very similar!

Links

There is a great book called T-SQL Fundamentals written by Itzik Ben-Gan that goes over several core concepts you should know about SQL Server, including set operator precedence. The book actually has an entire chapter dedicated to the discussion of all the different set operators and their precedence. You won’t regret owning this book, trust me. Definitely get it today!

Next Steps:

Leave a comment if you found this tutorial helpful!

If you found this tutorial helpful, make sure you download the following FREE GUIDE:

FREE 1-page Simple SQL Cheat Sheet on Set Operators!

Also be sure to check out my in-depth tutorials on the 3 different set operators:

The UNION Set Operator: Everything you need to know.

The INTERSECT Set Operator: Everything you need to know.

The EXCEPT Set Operator: Everything you need to know.

Thank you very much for reading!

Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!

If you have any questions, please leave a comment. Or better yet, send me an email!