Set Operators in SQL Server

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:

1. UNION
2. UNION ALL
3. INTERSECT
4. 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