Back to: SQL Server Tutorial For Beginners and Professionals

The INTERSECT Operator in SQL Server

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 discuss the INTERSECT operator in SQL Server.

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 and EXCEPT operator articles of this article series before proceeding to this article.

The INTERSECT Operator:

The Intersect operator in SQL server is used to retrieves the common records from both the left and the right query of the Intersect operator.

Let us understand INTERSECT operator with an example.

Use below Script to create the tables and populate the tables with some test data

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')
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')
GO

Fetch the records:

SELECT * FROM TableB

The following query retrieves the common records from both the left and the right query of the Intersect operator. 

SELECT ID, Name, Gender, Department FROM TableA

INTERSECT

SELECT ID, Name, Gender, Department FROM TableB

Result:

We can also achieve the same thing using the INNER join. The following INNER join query would produce the exact same result. 

Select TableA.ID, TableA.Name, TableA.Gender, TableA.Department

From TableA Inner Join TableB

On TableA.ID = TableB.ID

Result:

What is the difference between INTERSECT and INNER JOIN in SQL Server?

INTERSECT filters duplicates rows and returns only DISTINCT rows that are common between the LEFT and Right Query, whereas INNER JOIN does not filter the duplicates. 

To understand this difference, insert the following row into TableA

INSERT INTO TableA VALUES(2, ‘Priyanka’, ‘Female’,’IT’)

Now execute the following INTERSECT query. Notice that we get only the DISTINCT rows 

SELECT ID, Name, Gender, Department FROM TableA

INTERSECT

SELECT ID, Name, Gender, Department FROM TableB

Result:

Now execute the following INNER JOIN query. Notice that the duplicate rows are not filtered.

Select TableA.ID, TableA.Name, TableA.Gender, TableA.Department

From TableA Inner Join TableB

On TableA.ID = TableB.ID

Result:

We can make the INNER JOIN behave like INTERSECT operator by using the DISTINCT operator 

Select DISTINCT TableA.ID, TableA.Name, TableA.Gender, TableA.Department

From TableA Inner Join TableB

On TableA.ID = TableB.ID

Result:

INNER JOIN treats two NULLS as two different values. So if you are joining two tables based on a nullable column and if both tables have NULLs in that joining column then, INNER JOIN will not include those rows in the result-set, whereas INTERSECT treats two NULLs as the same value and it returns all matching rows.

To understand this difference, execute the following 2 insert statements

INSERT INTO TableA VALUES(NULL, ‘Pam’, ‘Female’, ‘HR’)

INSERT INTO TableB VALUES(NULL,’Pam’, ‘Female’, ‘HR’)

INTERSECT query:

SELECT ID, Name, Gender, Department FROM TableA

INTERSECT

SELECT ID, Name, Gender, Department FROM TableB

Result:

INNER JOIN query:

Select TableA.ID, TableA.Name, TableA.Gender, TableA.Department

From TableA Inner Join TableB

On TableA.ID = TableB.ID

Result:

In the next article, I will discuss the differences between UNION, EXCEPT and INTERSECT Operators in SQL Server with some examples.

SUMMARY

In this article, I try to explain the INTERSECT Operator in SQL Server step by step 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.