Do you remember Sets in mathematics? Well, our SQL Sets are similar to the Sets in mathematics.
Union, Union all, Intersect and Except are the Set operators in SQL server
What does Set mean?
Set is one of the most fundamental concepts in mathematics. Set is a group of collection of objects.
Relationship among the set can be picturized using a Venn diagram. We will be using Venn diagrams to understand Set operators.
Let’s start with our SQL SET Operators but first I want you to consider the to tables below. This will help us to understand the operators in a better way.
(a). Lunch (b).Dinner
When two or more sets are added they are termed as Union. The result set of union will contain records from both the sets and the records will be distinct.
The SQL UNION is used to combine two or more record set or SQL SELECT statements. It removes the duplicate records (which maybe present in both the record set) and hence returning a distinct result set.
Union operator is used by simply writing UNION keyword between the two SELECT statements
Select item from Lunch UNION Select item from Dinner
Records from both table after UNION
Above result set shows the 11 records from both the table. What is to be noted that Bread, Coffee and Olives are in both the tables, but the result set contains only single record of Bread, Coffee and Olives and hence there are no duplicate records.
Venn diagram for UNION
The above Venn diagram shows the unification of the tables. The resulted records are from Lunch and Dinner and their common records.
Union all is a special kind of UNION operator which returns records similar to the UNION, but the records are not distinct
SQL UNION ALL operator is used to combine the result sets of two or more SELECT statements. It does not remove duplicate rows between the various SELECT statements
Select item from Lunch UNION ALL Select item from Dinner
Records from both table after UNION ALL
Above result set shows the 14 records from both the table. Here; Bread, Coffee and Olives are repeated twice as they are present in both the tables. So, UNION ALL returns all the records in both the tables.
Intersection between the two or more record sets returns the matching values from the record sets.
The SQL INTERSECT operator is used to return the Distinct results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Select item from Lunch INTERSECT Select item from Dinner
Records from both tables after INTERSECT
Result set above shows the Records which are common in both the tables. We can see Bread, Coffee and Olives are returned as the intersection of tables Lunch and Dinner.
The matching records which are returned are Distinct and there are no repeated rows.
Venn diagram for INTERSECT
Venn diagram shows the colored section between the Record set of Lunch and Dinner which signifies Intersection.
Except in SQL server denotes Complement in Mathematical set. Complement implies that two related sets can be subtracted.
The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset.
The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.
Select item from Lunch EXCEPT Select item from Dinner
Records after applying EXCEPT
The Result set above shows the records which are present in Lunch table but not in Dinner table.
Below is the Except operator relationship represented by Venn diagram. Where colored section denotes records returned by the EXCEPT between the Lunch to that of the Dinner.
Venn diagram for Lunch EXCEPT Dinner
Important Points to remember:
- All record sets or the Select statements must have the same number of columns
select item, Price, Getdate() as Date from Lunch Intersect select item from Dinner
will raise an error.
- In every result set the data type of each column must match the data type of its corresponding column in the first result set.
- In order to sort the result, an ORDER BY clause should be part of the last statement
//WRONG WAY select item from Lunch order by item --- Wrong way Intersect select item from Dinner order by item --- WRONG way //RIGHT WAY select item from Lunch Intersect select item from Dinner Order by item --- Right way
- Aliases for the columns can be used. But for Intersect and except, the first SELECT statement aliases will be used in order by.
select item as D from Lunch Intersect select * from Dinner order by D --- We can’t use order by item
- Sometime Joins and Set operators act same but they are not exactly same
That’s all with SQL Set operators. If any suggestions or feedback then do let me know in comment section.
Happy coding folks!!