Set operators are used to combine results from two or more SELECT statements. They combine the same type of data from two or more tables. This looks similar to SQL joins although there is a difference. SQL joins are used to combine columns whereas Set operators are used to join rows from multiple SELECT queries. They return only one result set.
These operators work on complete rows of the queries, so the results of the queries must have the same column name, same column order and the types of columns must be compatible. There are the following 4 set operators in SQL Server:
- UNION
- UNION ALL
- INTERSECT
- EXCEPT
Consider the following two tables for examples.
Table: School1
Standard |
Students |
First |
50 |
Second |
60 |
Third |
40 |
Fifth |
45 |
Sixth |
58 |
Seventh |
77 |
Table: School2
Standard |
Students |
First |
30 |
Second |
46 |
Fourth |
56 |
Eight |
46 |
Sixth |
34 |
1. UNION Operator
The UNION operator combines two or more result sets into a single result set, without duplications. The union of two queries gives rows that are present in the first result set or in the second result set or in both. But each row appears only once.
Venn diagram for UNION:
Example
- SELECT Standard FROM School1
- UNION
- SELECT Standard FROM School2
Output
Standard |
Eight |
Fifth |
First |
Fourth |
Second |
Seventh |
Sixth |
Third |
2. UNION ALL Operators
Like the UNION operator the UNION ALL operator also combines two or more result sets into a single result set. The only difference between a UNION and UNION ALL is that the UNION ALL allows duplicate rows.
Venn diagram for UNION ALL:
Example
- SELECT Standard FROM School1
- UNION ALL
- SELECT Standard FROM School2
Output
Standard |
First |
Second |
Third |
Fifth |
Sixth |
Seventh |
First |
Second |
Fourth |
Eight |
Sixth |
3. INTERSECT Operator
The INTERSECT operator returns only the rows present in all the result sets. The intersection of two queries gives the rows that are present in both result sets. It returns only unique rows.
Venn diagram for INTERSECT:
Example
- SELECT Standard FROM School1
- INTERSECT
- SELECT Standard FROM School2
Output
Standard |
First |
Second |
Sixth |
4. EXCEPT Operator
The EXCEPT operator returns all distinct the rows that are present in the result set of the first query, but not in the result set of the second query. It means it returns the difference between the two result sets.
Venn diagram for INTERSECT:
Example
- SELECT Standard FROM School1
- EXCEPT
- SELECT Standard FROM School2
Output
Standard |
Fifth |
Seventh |
Third |