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:
 
Union

Example
  1. SELECT Standard FROM School1  
  2. UNION  
  3. 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:

UNION ALL 

Example

  1. SELECT Standard FROM School1  
  2. UNION ALL  
  3. 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:
 
INTERSECT Operator

Example
  1. SELECT Standard FROM School1  
  2. INTERSECT  
  3. 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:

EXCEPT Operator

Example
  1. SELECT Standard FROM School1  
  2. EXCEPT  
  3. SELECT Standard FROM School2 

Output

Standard

Fifth

Seventh

Third

Up Next
    Ebook Download
    View all
    Learn
    View all