INTERSECT and EXCEPT operators
Try reloading this page, or reviewing your browser settings
This video explains how to use the Union and Union All 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 focus on set operators, specifically, the UNION and UNION ALL operators. When we talk about sets, we’re typically talking about the sets of records that are returned from a Select statement. For example, if I select the City and state field from the Employees table, I would have a set of eight records. Also, if I select City and state from the Customer’s table, I would have a set of records of 10 cities and states.
But what if I wanted to combine those results sets, or combine these two sets of results into a single result set? That is where I can use my SET operators. My SET operators are UNION, UNION ALL, INTERSECT, and EXCEPT. For this video segment, we’re just going to focus on the first two, UNION and UNION ALL.
The UNION statement will allow me to combine the results sets, but will remove any duplicates. So if there’s more than one person who lived in Indianapolis, Indiana, it would only show that once in the results set. The UNION ALL statement would show all the records from both the employees and the customers, including the duplicates.
So let’s go ahead and look at this more closely in our Management Studio. Here, you can see I’ve already written code to select the city and state from the Employees table and the city and state from the Customer’s table. So we have eight employees and 10 customers. When I add the UNION ALL set operator, it will combine the two select statements into a single result set.
So you can see, we had eight employees and 10 customers for a total of 18 rows. And we do have multiple people who live in Indianapolis. So the UNION ALL statement returned all the records, including duplicates. If I remove the ALL operator and then execute, you will see that I only have five rows returned because it removed any of the duplicate records.
Now a key point I do want to point out, is that the requirements for our select statements is that we have to have the same number of columns for both Select statements. So in this case, we have two columns in the employees and two columns in the customers. But they have to be the same data type. So at the City field is a character field, then the corresponding field in the second set also has to be a Character field. So in this video segment, we discussed using the UNION and UNION ALL operators.