Try reloading this page, or reviewing your browser settings
This video explains how to use the Intersect and Except Set Operators to combine result sets.
- Set Operators
- Union All
About this video
- John Deardurff
- First online
- 05 April 2019
- Online ISBN
- Copyright information
- © John Deardurff 2019
In this video segment, we’re going to discuss the intersect and except set operators. Typically, when we’re referring to sets, we’re talking about the records that are returned from a select statement. There are times though we want to combine those select statements into a single result set. That’s why we have our set operators.
Now, previously, we did discuss the union and union all set operators. For this video segment, we’re going to focus on the intersect and except. The intersect operator will combine the two select statements, but will only return results sets that will appear in both sets, where the except operator will return rows from the first set unless it is also in the second set. Now, of the four operators, it is important in which order that I write my select statements for the except statement, because it will look at the first select statement and will return all those records except if it’s in the second.
So let’s look at our Management Studio. Previously, we had written a union statement to return the records from both the employees table and the customers table. But remember, with the union statement, it did not return any duplicates. Now, if I replace the union statement with the intersect– intersect operator– it will return all the records from the employees table that was also in the customers. So this is telling us that we have at least one employee and one customer that are in Indianapolis, Los Angeles, and New York. The opposite of that is if I use the except. So it’s going to find everybody in the employees unless there is a matching record in the customers.
So in this case, Houston. So I have an employee in Houston, but I do not have a customer in Houston. As I mentioned, for the except operator, it is important which select statement I run first. If I switch these around, and instead of having the employees table first, I cut and paste and put the except keyword there– I’ll go ahead and remove the other except keyword. Now I’ll get a different result, because now I’ll see that I have a customer in Chicago where I’m returning all the records from the customers table, except if there’s a matching record in the employees. So this video segment, we’ve discussed the intersect and except set operators.