In this tutorial, we’re going to learn about a very common Set Operator you should know: The EXCEPT Set Operator.
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 other common set operators you should know are UNION, UNION ALL, and INTERSECT. I have full tutorials on each of those set operators found here:
The UNION Set Operator: Everything you need to know
(I also discuss UNION ALL in this tutorial, and the difference between the two)
The INTERSECT Set Operator: Everything you need to know
Be sure to check out those tutorials if you need to learn all the set operators available to us.
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 following topics about the EXCEPT set operator:
- What is the EXCEPT Set Operator?
- What is the syntax for the EXCEPT Set Operator?
- An example of using the EXCEPT Set Operator
- The rules you need to remember about the EXCEPT Set Operator
- Tips, tricks, and links
Don’t forget to Download your FREE 1-page Simple SQL Cheat Sheet on Set Operators!
Here we go.
1. What is the EXCEPT Set Operator?
The EXCEPT Set Operator will return rows from one result set that specifically are NOT in another result set.
That is a short definition, but there’s not much to it.
If you are interested in seeing data that belongs to one result set, and is specifically NOT IN another result set, you might be able to use the EXCEPT set operator.
Here is a helpful diagram to help you visualize the data that gets returned from the EXCEPT set operator:
So again, we want to see data that is exclusive to your first result set.
We’ll think about some examples in topic # 3.
2. What is the syntax for the EXCEPT Set Operator?
It’s also really simple:
<first SELECT statement> EXCEPT <second SELECT statement>
All you need to do is add the word ‘EXCEPT‘ between two SELECT queries. Of course, these SELECT queries are what generate our result sets.
While the syntax is easy, there are rules you need to remember about the two SELECT queries.
If you read my tutorials on UNION and INTERSECT, you would already be familiar with that idea. But don’t worry, if you skipped those tutorials, we’re going to give you a rundown of those rules soon.
But for now, let’s move on to some examples.
3. An example of using the EXCEPT Set Operator
Let’s say you are the owner of a small woodworking business. Business is good, and you are enriching many people’s lives through the crafts and furniture you sell.
But you suspect there are some items you have made that nobody has ever bought.
You’d like to see if there are, in fact, products in your inventory that have never been ordered. You will probably remove those from your inventory, as it’s obvious nobody is interested in buying them. This would give you more time to focus on the things that actually do sell.
Let’s see all the products in your inventory:
Cool, now let’s take a look at your Orders table:
So notice each order references the Product ID of the product that was purchased.
Let’s enhance this query to focus on the product information. I’ll do a quick JOIN with the Products table, so you can see the name of the products too:
That’s better. We’re still looking at all the orders that have been placed, but now we’re just seeing the product information for the product that was purchased.
Need a rundown on the INNER JOIN operator? I got you.
Ok, so let’s look through the list of orders. It looks like there are some items that have been purchased more than once, which is good. The Small Bench is one of those items.
There are also some items that have been purchased only once, like the Bar Stool, or the Large Bench.
But what about items that have never been purchased? Obviously there won’t be a reference to those products in the Orders table.
If you want to identify which products have never been purchased, you basically need to reconcile the data in your Products table with the data in your Orders table. Let’s look at them side by side:
Ok, if we do this the hard way, we can eyeball the list of products and see which products do not have a reference in the Orders table. It looks like that’s the following items:
Product # 7: Calendar Rack
Products # 9: Plaque
Cool, so I guess this answers our question…
This was easy to do with such a small result set
Figuring out the list of order-less products was easy with a total of 9 products and 11 orders.
What if the company you own is called “Amazon” (maybe you’ve heard of it)? You likely have millions of products, and billions of orders. Good luck spending your entire life coming up with this list of order-less products.
In SQL Server, we need to THINK BIG
Using EXCEPT, we can instantly figure out what products exist in the Products table that don’t have a reference in the Orders table:
This is MUCH EASIER
So again, we’re basically saying, “Get me the full list of products, except for the ones that have been ordered“. I guess the resulting product list won’t be a “full list” at all, will it? It will be a subset.
Again, we use EXCEPT when we want to see items that exist in one result set, and specifically don’t exist in another result set.
4. The rules you need to remember about the EXCEPT 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.
Our first SELECT statement has 2 columns (ProdID, ProdName) and our second SELECT statement also has 2 columns (O.ProdID, P.ProdName).
This rule is satisfied.
If we had tried to show the OrderID in our second query, we would have broken the rule, like so:
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!
2. The data types of each column in each SELECT statement must be compatible.
So again, the columns in the first result set we’re working with are Products.ProdID and Products.ProdName.
The columns in the second result set we’re working with are Orders.ProdID and Products.ProdName
Let’s see the details for those columns in SSMS:
So, going back to our query, we need to make sure the data types are compatible. Here’s the query again:
SQL Server will look at each column in each query from left to right, and sees if they are compatible.
Let’s walk the list of columns like SQL Server does. Is ProdID from the first query compatible with O.ProdID from the second query? Well, in the properties of each column, we can see they are both data type int. Since they are the same exact data type, they are definitely compatible (but understand two data types can be slightly different and still be compatible, like char and varchar).
Next up, is ProdName from the first query compatible with P.ProdName from the second query? Well, they are actually both referring to the same column, which is the Products.ProdName column.
P.ProdName is the result of a JOIN with the Products table, remember? So, this is a trivial thing: A column is compatible with itself, obviously!
3. In the final result set, the names of your columns are pulled from the names of the columns in your first SELECT statement.
Let’s add some aliases to a couple columns in our query:
We created an alias on the first column in each SELECT statement. But which alias was actually used?
The first one!
The only aliases used in your final result set are aliases created in your first SELECT statement. If you put an alias on a column in any other SELECT statement, you wasted your time! It won’t be seen!
4. The only ORDER BY clause allowed in the query is a presentation ORDER BY clause after the last SELECT statement.
This doesn’t work:
The ORDER BY clause is not after the last query, so we’ve broken the rule.
Let’s fix it:
Much better. We can also choose to order 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 helpful tips and tricks you should know about the EXCEPT set operator:
Tip # 1: It’s possible to have more than one EXCEPT operator in your query
Let’s say you have another table called EmployeeOrders. We use this table to track orders our employees make, specifically, when they want to buy one of our products for themselves.
This table was created to separate employee orders from regular customer orders.
If we want to know what items have never been ordered, we should also be looking in this EmployeeOrders table too, to get the full picture. It’s possible one of our employees purchased one of these items that the general public has not.
So, let’s see what’s in the EmployeeOrders table:
Ok, cool. Let’s do a JOIN with the Products table to get better details:
This query looks like something we could use alongside the other queries, and use another EXCEPT operator:
If we remember, our old query (where we used EXCEPT with only two result sets) returned two items:
– Product # 9: Plaque
– Product # 7: Calendar Rack
But remember, that was when we were only looking at two result sets. If we add this third result set, we can see there is only one item that is truly order-less (aka no references in the Orders table or the EmployeeOrders table)
That item is the Calendar Rack! What a shame 🙁
Tip # 2: Unlike UNION, UNION ALL, and INTERSECT, the order of the queries used with EXCEPT matters
Take a look at our example again:
Would I get the same final result if I flipped the queries around?:
NOPE
This query now basically says, “Show me product information from my orders table, except if the product is referenced in the Products table”
Another way to say it is “What orders have been placed for products that don’t exist?”
I should hope there are none, which is exactly what the new query returns.
So, the order of your queries matters.
Tip # 3: 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 # 4: Your 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 EXCEPT 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, make sure you…
Download your 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:
The UNION Set Operator: Everything you need to know
Also, take a look at my tutorial on the INTERSECT set operator. This is also a common operator you need to know. Check it out here:
The INTERSECT Set Operator: Everything you need to know
You need to 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 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!
