SQL Server Set Operators are one of the more common tools we have available to us when querying SQL Server databases. It is important you know how to write them and how they work.
Set operators are part of the Top 6 tools you should know for writing AWESOME Select statements
If you want to learn more about other extremely common and useful query tools, check out that tutorial.
In this tutorial, we will cover the basics of SQL Server Set Operators. We’ll discuss the different kinds of set operators, how to write them, and the rules you need to remember when using them.
We’ll cover the following topics in this tutorial:
- What are SQL Server Set Operators?
- UNION and UNION ALL Set Operators
- INTERSECT Set Operator
- EXCEPT Set Operator
- Rules you need to remember when using Set Operators
- Tips, tricks, and links.
Don’t forget to Download your FREE 1-page Simple SQL Cheat Sheet on Set Operators!
Thank you for reading! Let’s get started.
1. What are SQL Server Set Operators?
SQL Server Set Operators are used to combine the results of two or more SELECT queries into a single result set.
Depending on the set operator used, you may see all rows from both queries, or you may only see some rows from each query.
By that, I mean some set operators are designed to return all rows from each SELECT query, and some are meant to specifically show you a subset of rows from each SELECT query.
If you’re not sure what that means, I understand. It is honestly easiest to explain the different set operators by looking at some examples.
2. UNION and UNION ALL Set Operators
The UNION set operator will combine the results of two or more queries into a single result set.
If you have two SELECT statements, for example, and you want the results of both displayed in a single result set (instead of two), you might be able to use the UNION set operator.
Here is a common diagram you will see that will help you visualize the rows returned from the UNION set operator:
So basically, this operator will show you all rows from both result sets.
Here is the syntax for the UNION set operator:
<first SELECT statement> UNION <second SELECT statement>
As an example, let’s say you are a small business owner and you have a store located in Denver and another in the neighboring town of Boulder. You store customer information in two separate tables called DenverBranch and BoulderBranch.
If you wanted to see ALL the customers you have in both locations (combined into a single result set), you could use UNION:
Nice!
BUT WHAT ABOUT UNION ALL?!
Well, I’m glad you asked. To understand, UNION ALL, it’s important to understand something about UNION first.
UNION will remove duplicates from the final result set.
UNION ALL will not remove duplicates from final the result set.
To demonstrate, take a look at the rows in each individual query:
I underlined Sara Winchester. Sara is in both tables! She lives in Boulder, but works in Denver, so she shops at either location depending on which is more convenient at the time.
But notice in our UNION query, she only appeared once:
Like I said, she only appears once because the UNION operator will remove duplicates. UNION assumes you don’t care to see the same information twice.
But, if you specifically wanted to see the same information twice, you would need to use UNION ALL:
Pretty cool, right?
For the full in-depth discussion on the UNION and UNION ALL set operators, check out this tutorial:
The UNION Set Operator: Everything you need to know
3. INTERSECT Set Operator
The INTERSECT Set Operator uses SELECT queries to find overlapping data between two or more result sets.
This is one of those set operators “meant to only show you a subset of rows from your queries”.
Here is a diagram to help you visualize the rows that get returned when using the INTERSECT set operator:
So in a nutshell, the INTERSECT set operator will only return rows that exist in both result sets.
Remember Sara Winchester? She was one of those people that intersected the two result sets. But again, what if we wanted to see only people like her, who are referenced in both the DenverBranch and BoulderBranch tables?
You would use INTERSECT!
You write it like this:
<first SELECT statement> INTERSECT <second SELECT statement>
Let’s write out the query to see customers who specifically shop at both locations:
It looks like Sara Winchester wasn’t the only one!
For the full in-depth discussion on the INTERSECT set operator, check out the full tutorial:
The INTERSECT Set Operator: Everything you need to know.
4. EXCEPT Set Operator
The EXCEPT Set Operator will return rows from one result set that specifically are NOT in another result set.
This is another set operator “meant to only show you a subset of rows from your queries”.
Here is another diagram to help you visualize the rows that get returned when using the EXCEPT set operator:
So in a nutshell, you’re saying you want to see rows from your first result set that specifically are NOT in your second result set.
What if we want to see customers who shop exclusively at the Denver store?
If we have a customer who shops at both locations, we don’t want to see them. We want to see the people who shop in the Denver store only.
To put it another way, “Show me the list of Denver customers, except for the ones who also shop in Boulder”.
See what I did there? 🙂
You would write your query like this:
<first SELECT statement> EXCEPT <second SELECT statement>
Here is the example of customers who shop at our Denver location exclusively:
Compare that to a quick query of everyone we have in the DenverBranch table by itself:
From our discussion about INTERSECT, we know Doug, Emily, June, and Sara all shop at the Boulder location too, which is why they are not seen when we use EXCEPT.
Pretty neat, right?
For the full in-depth discussion on the EXCEPT set operator, check out the full tutorial:
The EXCEPT Set Operator: Everything you need to know.
5. Rules you need to remember when using Set Operators
As you might have guessed, the syntax for writing and using a set operator is really easy. Basically, you just butt up two queries against each other, and put your set operator keyword between the two.
While the syntax is easy, you need to remember a handful of rules about the queries you are using with your set operators.
Here are the rules, explained:
Rule # 1. The two SELECT statements must have the same number of columns
Take our example of the EXCEPT set operator:
Each query being used with our set operator has 3 columns, so we’re not breaking this rule. But if we added a fourth column to the second query, for example, we’d get an error message:
Here’s that full error message:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Column counts must be the same!
Rule # 2. The data types of each column in each SELECT statement must be compatible.
The columns, from left to right, in each query of your set operator must be compatible.
Let’s take this query as an example:
From left to right, let’s look at each column in each query.
The first column in the first query is FirstName. The first column in the second query is CustID.
In the Object Explorer, let’s take a look at the data types:
FirstName is a varchar (aka a character string) data type. CustID is an int (aka an integer) data type.
So the question becomes, “Is a character string data type compatible with an integer data type?“.
Well, try solving this math problem:
12 + ‘Meatloaf’ = ?
Umm, I don’t think that will work. I’d say a character string is not compatible with an integer!
So if we try to run the query, we get an error message:
SQL Server is trying to convert someone’s first name, “Doug“, into an integer, which won’t work!
Rule # 3. In the final result set, the names of your columns are pulled from the names of the columns in your first query.
Let’s look at the following query, where we put aliases in the second query of our set operator:
Notice none them were used in the final result set. We wasted our time making those aliases!
If we wanted those aliases used in our final result set, we should have moved them to the first query, like so:
Much better!
Rule # 4. The only ORDER BY clause allowed in the set operator query is a presentation ORDER BY clause after the last query.
Let’s break this rule, and put an ORDER BY clause after the first query.
As you can see, we get an error message.
Let’s fix it, and put the ORDER BY clause after the last query.
Much better!
6. Tips, tricks, and links.
Here is a list of some tips and tricks you should know when working with set operators:
Tip # 1: If you know, without a doubt, your two result sets do not contain duplicate data, you should prefer to use UNION ALL.
The UNION set operator will actively look for and attempt to remove duplicates. If you are confident your result sets simply do not contain duplicates, SQL Server would be wasting time and energy in trying to look for them.
If you are confident there are no duplicates, you should prefer to use UNION ALL and help SQL Server avoid doing unnecessary work.
Tip # 2: You can use more than one set operator in a single query.
Let’s say we open a third store in Colorado Springs, and we want to see if we have any customers who shop at all three locations.
(Before I show the query, can you guess which set operator I will need to use?)
Here’s the query:
Wow, let’s treat Sara Winchester like royalty when she comes in!
Tip # 3: Set operators have precedence.
If you are using different set operators in a single query, you should know that certain set operators take precedence over others.
INTERSECT has the highest precedence. UNION and EXCEPT have the same precedence. UNION and EXCEPT are evaluated in the order they appear.
You can actually force a specific precedence through the use of parentheses (so I guess parentheses actually have the highest precedence).
For the full tutorial on set operator precedence, check out the full blog post:
Set Operator Precedence: Explained
Tip # 4: Remember, column names in the final result set are pulled from column names in the first query
If you put an alias in anything but your first query, you wasted your time. It won’t be used!
Tip # 5: Remember, the only ORDER BY clause allowed is a presentation ORDER BY clause after the last query.
There should only be one ORDER BY clause, and it needs to be at the end!
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 all the different set operator. The book actually has an entire chapter dedicated to the discussion of all the different set operators. 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, don’t forget to…
Download your FREE 1-page Simple SQL Cheat Sheet on Set Operators!
This tutorial is simply meant to be an introduction to set operators. For the full, in-depth discussions on each of the different set operators, you should check out my in-depth tutorials:
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
After reading these tutorials, you will know everything there is to know about set operators!
And as mentioned earlier, set operators are part of the Top 6 tools you should know for writing AWESOME Select statements. If you want to learn more about other extremely common and useful query tools, check out that tutorial.
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!
