Differences between UNION EXCEPT and INTERSECT Operators

Differences between UNION EXCEPT and INTERSECT Operators in SQL

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. In this article, I will explain the Differences between UNION EXCEPT and INTERSECT Operators in SQL Server with some examples.

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.

Please read UNION and UNION ALL, EXCEPT and INTERSECT Operators of this article series before proceeding to this article.

Let us 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

Differences between UNION EXCEPT and INTERSECT Operators

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

Differences between UNION EXCEPT and INTERSECT Operators

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: 
Differences between UNION EXCEPT and INTERSECT Operators  

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:

Differences between UNION EXCEPT and INTERSECT Operators  

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:  
Differences between UNION EXCEPT and INTERSECT Operators  

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: 
Differences between UNION EXCEPT and INTERSECT Operators  
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: 
Differences between UNION EXCEPT and INTERSECT Operators  

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 will discuss the Joins in SQL server.

SUMMARY

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.

Leave a Reply

Your email address will not be published. Required fields are marked *