The Complete Reference - Set Operations In MS SQL - UNION [ALL], INTERSECT, EXCEPT

Introduction 

 
In this article, you will see the Complete Reference - Set Operations in MS SQL: UNION [ALL], INTERSECT, EXCEPT.
 

Article Overview

  • Background
  • Prerequisites
  • Set Operations in MS SQL: UNION [ALL], INTERSECT, EXCEPT
  • Basic Rules on Set Operations
  • Difference between UNION, UNION ALL, INTERSECT, and EXCEPT Operators
  • Summary

Background

 
Using set operations, the results of multiple queries can be combined into a single result set. Set operators include UNION, EXCEPT, and INTERSECT in MS SQL.
 
There are various situations where you need to combine results of multiple queries into a single result set. 
 
For example, you have Speakers and Authors data.
  • Now, you want to display the list of speakers and/or authors. For, such a requirement, you can use UNION.
  • Let us consider another case; you want the list of people who are speakers and they are also authors. This can be done using INTERSECT.
  • Take another scenario, you want the list of people who are only Speakers i.e. they are not Authors. This can be done using EXCEPT. 
To perform such kind of various set operations on the multiple data set; SQL mainly provides the following three operators:
  1. UNION, UNION ALL
  2. INTERSECT
  3. EXCEPT
Prerequisites
  • You should have a basic knowledge of MS SQL and queries.

Set Operations in MS SQL - UNION [ALL], INTERSECT, EXCEPT

 
Now, let us understand each operator one by one with a practical example.
 
Before beginning, let us create one Member table and insert some data into it with the following script:
 
Create Table 
  1. CREATE TABLE Speakers  
  2. (  
  3.   Name VARCHAR(25),  
  4. )  
  5.   
  6. CREATE TABLE Authors  
  7. (  
  8.   Name VARCHAR(25),  
  9. )  
Insert data into the table
  1. INSERT INTO Speakers VALUES ('Sachin')  
  2. INSERT INTO Speakers VALUES ('Rahul')  
  3. INSERT INTO Speakers VALUES ('Kamplesh')  
  4. INSERT INTO Speakers VALUES ('Chirag')  
  5.   
  6. INSERT INTO Authors VALUES ('Sachin')  
  7. INSERT INTO Authors VALUES ('Rahul')  
  8. INSERT INTO Authors VALUES ('Pratik')  
  9. INSERT INTO Authors VALUES ('Rajesh')  
  10. INSERT INTO Authors VALUES ('Anil')  

Speakers

 
Authors
 
 

UNION

 
Union is used to combine the results of two queries into a single result set of all matching rows. Both the queries must result in the same number of columns and compatible data types in order to unite. All duplicate records are removed automatically unless UNION ALL is used.
 
Generally, it can be useful in the applications where tables aren't perfectly normalized, for example, a data warehouse application.
 
Syntax
 
{ <query_specification> | ( <query_expression> ) }
{ UNION | UNION ALL}
{ <query_specification> | ( <query_expression> ) }
 
Example-1
 
You want to invite all the Speakers and Authors for the annual conference. Hence, how will you prepare the invitation list? 
  1. select name from Speakers  
  2. union  
  3. select name from Authors  
  4. order by name  
Output
 
 
As you can see here, the default order is ascending order and you have to use in the last query instead of both queries.
 

UNION ALL

 
It will not remove duplicate records. It can be faster than UNION.
 
Example-2
 
You want to give a prize to all the Speakers and Authors at the annual conference. Hence, how will you prepare the prize list? 
  1. select name'Speaker' as 'Role' from Speakers  
  2. union all  
  3. select name'Author' as 'Role' from Authors  
  4. order by name  
Output
 
 

INTERSECT

 
It is used to take the result of two queries and returns the only those rows which are common in both result sets. It removes duplicate records from the final result set.
 
Syntax
 
{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }
 
Example-3
 
You want the list of people who are Speakers and they are also Authors. Hence, how will you prepare such a list? 
  1. select name from Speakers  
  2. intersect  
  3. select name from Authors  
  4. order by name  
Output
 
 

EXCEPT

 
It is used to take the distinct records of two one query and returns the only those rows which do not appear in the second result set.
 
Syntax
 
{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }
 
Example-4
 
You want the list of people who are only Speakers and they are not Authors. Hence, how will you prepare such a list? 
  1. select name from Speakers  
  2. except  
  3. select name from Authors  
  4. order by name  
Output
 
 
Example-5
 
You want the list of people who are only Authors and they are not Speakers. Hence, how will you prepare such a list? 
  1. select name from Authors  
  2. except  
  3. select name from Speakers  
  4. order by name  
Output
 
 

Basic Rules on Set Operations

  • Result sets of all the queries must be the same number of columns.
  • In all result sets the data type of each of the columns must be well matched and compatible with the data type of its corresponding columns in another result set.
  • For sorting the result, the ORDER BY clause can be applied to the last query.

Difference between UNION, UNION ALL, INTERSECT, and EXCEPT Operators

 

Summary

 
Now I believe you understand the key important things about UNION, UNION ALL, INTERSECT, EXCEPT in MS SQL. 
  • UNION combines results from both tables.
  • UNION ALL combines two or more result sets into a single set, including all duplicate rows.
  • INTERSECT takes the rows from both the result sets which are common in both.
  • EXCEPT takes the rows from the first result data but does not in the second result set.