Differences Between UNION EXCEPT and INTERSECT Operators in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Differences Between UNION EXCEPT and INTERSECT Operators in SQL Server

In this article, I will explain the Differences between UNION EXCEPT and INTERSECT Operators in SQL Server with some examples. Please read UNION and UNION ALL, EXCEPT and INTERSECT Operators of this article series before proceeding to this article. The SET operators are mainly used to combine the result of more than 1 select statement and return a single result set to the user. 

The set 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: Combine two or more result sets into a single set, without duplicates.
2. UNION ALL: Combine two or more result sets into a single set, including all duplicates.
3. INTERSECT: Takes the data from both result sets which are in common.
4. EXCEPT: Takes the data from the first result set, but not in the second result set (i.e. no matching to each other)

Rules on Set Operations:

1. The result sets of all queries must have the same number of columns.
2. In every result set the data type of each column must be compatible (well matched) to the data type of its corresponding column in other result sets.
3. In order to sort the result, an ORDER BY clause should be part of the last select statement. The column names or aliases must be found out by the first select statement.

Understand the differences between these operators with examples.

Use below SQL Script to create and populate the two tables that we are going to use in our examples.

CREATE TABLE TableA
(
  ID INT,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO TableA VALUES(1, 'Pranaya', 'Male','IT')
INSERT INTO TableA VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO TableA VALUES(3, 'Preety', 'Female','HR')
INSERT INTO TableA VALUES(3, 'Preety', 'Female','HR')
GO

Fetch the records:

SELECT * FROM TableA

CREATE TABLE TableB
(
  ID INT,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO TableB VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO TableB VALUES(3, 'Preety', 'Female','HR')
INSERT INTO TableB VALUES(4, 'Anurag', 'Male','IT')
GO

Fetch the records:

SELECT * FROM TableB

UNION Operator:

The Union operator will return all the unique rows from both the queries. Notice that the duplicates are removed from the result set. 

SELECT ID, Name, Gender, Department FROM TableA
UNION
SELECT ID, Name, Gender, Department FROM TableB

Result: 
 

UNION ALL Operator:

The UNION ALL operator returns all the rows from both the queries, including the duplicates. 

SELECT ID, Name, Gender, Department FROM TableA
UNION ALL
SELECT ID, Name, Gender, Department FROM TableB

Result:

 

INTERSECT Operator:

The INTERSECT operator retrieves the common unique rows from both the left and the right query. Notice the duplicates are removed. 

SELECT ID, Name, Gender, Department FROM TableA
INTERSECT
SELECT ID, Name, Gender, Department FROM TableB

Result:  
 

EXCEPT Operator:

The EXCEPT operator will return unique rows from the left query that aren’t present in the right query’s results. 

SELECT ID, Name, Gender, Department FROM TableA
EXCEPT
SELECT ID, Name, Gender, Department FROM TableB

Result: 
 
If you want the rows that are present in Table B but not in Table A, reverse the queries. 

SELECT ID, Name, Gender, Department FROM TableB
EXCEPT
SELECT ID, Name, Gender, Department FROM TableA

Result: 
 

For all these 4 operators to work the following 2 conditions must be met

1. The number and the order of the columns must be the same in both the queries
2. The data types must be the same or at least compatible

For example, if the number of columns is different, you will get the following error

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. 

In the next article, I am going to discuss the Joins in SQL Server with examples. Here, in this article, I try to explain the Differences between UNION EXCEPT and INTERSECT Operators in SQL Server with some examples. I hope this article, will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.