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. The INTERSECT Set Operator, of course, is one of those operators you need to know.
You should also be familiar with the UNION and EXCEPT set operators. If you missed those tutorials, check them out, too.
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, too.
In this tutorial, we will be discussing the INTERSECT set operator. Here is the list of topics we will cover:
- What is the INTERSECT Set Operator?
- What is the syntax for the INTERSECT Set Operator?
- An example of using the INTERSECT Set Operator
- The rules you need to remember about the INTERSECT Set Operator
- Tips, tricks, and links
Don’t forget to Download your FREE 1-page Simple SQL Cheat Sheet on Set Operators!
And so it begins…
1. What is the INTERSECT Set Operator?
The INTERSECT Set Operator uses SELECT queries to find overlapping data between two or more result sets.
That’s a short definition, but there really isn’t much else to say. If you need to know what data exists in two or more result sets, you might be able to use the INTERSECT set operator.
There is a diagram that might help you visualize what the INTERSECT operator does. Here it is:
We can see how there are two result sets, which have some data that overlaps between the two. In other words, there is some duplicate data that exists in both result sets.
The INTERSECT operator specifically looks for that data that exists in both locations (aka the data at the intersection of the two result sets).
If you’re not sure what I mean, hang in there. We’re going to look at some simple examples soon.
2. What is the syntax for the INTERSECT Set Operator?
It’s VERY simple:
<first SELECT statement> INTERSECT <second SELECT statement>
All you need to do is basically butt up two SELECT statements against each other, and throw the word INTERSECT between them. That’s it!
However, understand while the syntax is easy, there are several rules you need to remember about the two SELECT statements. We’ll get into that in topic # 4.
3. An example of using the INTERSECT Set Operator
Let’s say you are the owner of a small chain of retail stores, based out of Colorado. You have two store locations, one in Denver and another in the neighboring town of Boulder.
Each store collects information about customers who shop at the store. All we collect is their first and last name, and their email address.
We keep track of our Denver customers in a table called DenverBranch. Customers from the Boulder location are kept in a separate table called BoulderBranch.
Let’s look at all the data we have for both locations, side by side:
Is it possible that we have some customers referenced in both the DenverBranch table and the BoulderBranch table?
Well, sure! These people would obviously shop at both locations. Maybe they live in Boulder, but work in Denver. They shop at either location depending on which is more convenient at the time.
Knowing that, maybe we want to send a special offer to these customers specifically. They are obviously very loyal customers, so we want to reward them for that loyalty. Maybe we’ll send them a 10% off coupon via email.
If we look through both result sets, it looks like the following customers are shared between the two:
Doug Bennington, Emily Foxtrot, June Braun, and Sara Winchester
So I guess we have answered the question of “Who shops at both locations”?
Gathering this data was easy, but only because it’s a small result set
Gathering this list wasn’t too difficult with a total of 20 rows in both tables. But remember, in SQL Server, we need to THINK BIG.
What if we had 200 rows in total? What about 32,763 rows? It would take FOREVER to figure out the list of shared customers.
This is where the INTERSECT set operator comes in handy. This task of finding shared customers is exactly what INTERSECT was designed to do. Let’s see how easy it is to get the list of shared customers using INTERSECT:
Nice, it returns the same rows we figured out earlier.
Notice how long it took. It didn’t even take a single second!
Again, the task of finding shared data is what INTERSECT was designed to do.
4. The rules you need to remember about the INTERSECT Set Operator
If you already read through my discussion on UNION and UNION ALL, you will already be familiar with the rules we need to remember when using set operators.
Here’s that discussion, if you want to take a look:
The UNION Set Operator: Everything you need to know
But for those of you who haven’t read that tutorial, we’ll go over them anyway. Here are the rules to remember:
- The two SELECT statements must have the same number of columns.
- The data types of each column in each SELECT statement must be compatible.
- In the final result set, the names of your columns are pulled from the names of the columns in your first SELECT statement.
- The only ORDER BY clause allowed in the query is a presentation ORDER BY clause after the last SELECT statement.
Let’s look through the query to make sure we are following all the rules of using a set operator:
1. The two SELECT statements must have the same number of columns.
Each subquery has 3 columns: FirstName, LastName, Email. This rule is satisfied!
2. The data types of each column in each SELECT statement must be compatible
Using the object explorer, let’s look at the data types for all our columns in both tables:
It looks like FirstName from each table is varchar(15). These columns are compatible
It looks like LastName is also varchar(15), making those columns compatible.
Finally Email in the BoulderBranch table is varchar(40), while the same column in the DenverBranch table is varchar(45). Sure, the size is different, but that’s ok. They are still both character string data types, which means they are compatible.
I’d say this rule is GOOD.
3. In the final result set, the names of your columns are pulled from the names of the columns in your first SELECT statement.
This one isn’t as obvious in our example. But let’s make some aliases in the second query (defying the rule) of the INTERSECT operator:
Notice none of the aliases are being used! We wasted our time making those aliases! So again, the column names in your final result set are pulled from the column names in the first query.
4. The only ORDER BY clause allowed in the query is a presentation ORDER BY clause after the last SELECT statement.
Our query doesn’t have an ORDER BY clause presently, but let’s add one (incorrectly):
We cannot have an ORDER BY clause in anything but the last query!
Ok, my bad. Let’s fix it:
Nice. Notice we can choose to order our results by an alias name created in the first query. This ties back to the idea that the columns in our final result set are pulled from the first query. These columns are also the columns we can choose to order by.
5. Tips, tricks, and links
Here is a list of some tips and tricks you should know about the INTERSECT set operator:
Tip # 1: You can use INTERSECT more than once in a single query
What if you want to find intersecting data in more than two tables? In our examples, we have only been finding intersecting data on two tables, but what if you wanted to look in 3 tables (or more)?
You can certainly have more than one INTERSECT operator in your query.
Let’s say we open a third store in Colorado Springs. What if we want to see people who shop at all three locations?
Let’s see who’s in the Colorado Springs table:
Anyone look familiar??? Let’s see who shops at all three locations:
Wow, Sara Winchester might be our best customer. We’ll make sure to treat her like royalty when she comes in our stores.
Tip # 2: Column names are pulled from the first query
Remember, if you put an alias in anything but your first query, you wasted your time. It won’t be used!
Tip # 3: The ORDER BY clause must be after the last query
Remember, the only ORDER BY clause allowed is a presentation ORDER BY clause after the last query.
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 how to use the INTERSECT 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 GUIDE:
FREE 1-page Simple SQL Cheat Sheet on Set Operators!
Be sure to check out my tutorial on the UNION and UNION ALL set operators in case you missed it. These are the most common set operators you will likely use in your career as a database developer. Check it out here:
SQL Server UNION: Everything you need to know
Also, check out the tutorial on the beloved cousin of the INTERSECT operator: The EXCEPT Set Operator. Check it out:
SQL Server EXCEPT: Everything You Need to Know
You should also know that certain set operators take precedence over others. This is in situations where you need to write a query involving more than one set operator. Make sure you understand set operator precedence by reading this article:
Set Operator Precedence: Explained
Finally, 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!
