INTERSECT Operator in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

INTERSECT Operator in SQL Server with Examples

In this article, I am going to discuss the INTERSECT Operator in SQL Server with examples. The INTERSECT operator belongs to the SET Operator category. Please read our previous articles where we discussed the basics of SET Operators in SQL Server as well as we also discussed the UNION and UNION ALL SET Operators in detail.

What is the INTERSECT operator in SQL Server?

The INTERSECT operator in SQL Server is used to retrieve the common records both the left and the right query of the Intersect operator. If this is not clear at the moment then don’t worry we will try to understand this with some examples.

Understanding the INTERSECT operator with examples.

Let us understand the INTERSECT Operator in SQL Server with some examples. We are going to use the following “TableA” and “TableB” tables to understand this operator.

Please use the below SQL Script to create Database EmployeeDB, tables EmployeeIndia, and EmployeeUK and populate these two tables with the required test data.

CREATE DATABASE EmployeeDB
GO

USE EmployeeDB
GO

CREATE TABLE EmployeeIndia
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO EmployeeIndia VALUES(1, 'Pranaya', 'Male','IT')
INSERT INTO EmployeeIndia VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO EmployeeIndia VALUES(3, 'Preety', 'Female','HR')
INSERT INTO EmployeeIndia VALUES(4, 'Subrat', 'Male','HR')
INSERT INTO EmployeeIndia VALUES(5, 'Anurag', 'Male','IT')
GO

CREATE TABLE EmployeeUK
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO EmployeeUK VALUES(1, 'James', 'Male','IT')
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO EmployeeUK VALUES(3, 'Sara', 'Female','HR')
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Male','HR')
INSERT INTO EmployeeUK VALUES(5, 'Pam', 'Female','HR')
GO

Example:

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?

The INTERSECT Operator filters duplicate rows and return only the 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 in the output.

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’)

Example: Using the INTERSECT query

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

Result:

Example: Using 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 am going to discuss the differences between UNION, EXCEPT, and INTERSECT Operators in SQL Server with some examples. Here, in this article, I try to explain the INTERSECT Operator in SQL Server step by step with examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.