INTERSECT Operator in SQL Server

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 of 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 “EmployeeIndia” and “EmployeeUK” tables to understand INTERSECT SET operator.

UNION and UNION ALL Operators in SQL Server

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:

Intersect operator in SQL server

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:

Intersect operator in SQL server

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:

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

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:

Intersect operator in SQL server

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 Using DISTINCT Operatpr in SQL Server

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 the INTERSECT query

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:

Example: Using INNER JOIN query

In the next article, I am going to discuss the differences between UNION, EXCEPT, and INTERSECT Operators in SQL Server with Examples. Here, in this article, I try to explain the INTERSECT Operator in SQL Server 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 INTERSECT Operator in SQL Server with Examples article.

4 thoughts on “INTERSECT Operator in SQL Server”

  1. blank

    In the above example the common employees are ‘Priyanka’ and ‘Subrat’ while in output it is returning ‘Priyanka’ and ‘Preety’

  2. blank

    Great tutorial, thanks.

    INTERSECT AND INNER JOIN can not give the same results because in INTERSECT the comparison is based on the equality of all columns whereas in INNER JOIN it is based on one column (i.e ID).
    Let’s see the following example:-

    INSERT INTO TableA
    SELECT 1, ‘Pranaya’, ‘Male’,’IT’ UNION
    SELECT 2, ‘Priyanka’, ‘Female’,’IT’ UNION
    SELECT 3, ‘Preety’, ‘Female’,’HR’

    INSERT INTO TableB
    SELECT 1, ‘Balu’, ‘Male’,’HR’ UNION
    SELECT 2, ‘Priyanka’, ‘Female’,’IT’ UNION
    SELECT 3, ‘Preety’, ‘Female’,’HR’

    Query 1 – Using INTERSECT
    SELECT ID, Name, Gender, Department FROM TableA
    INTERSECT
    SELECT ID, Name, Gender, Department FROM TableB

    Result:-
    ID Name Gender Department
    2 Priyanka Female IT
    3 Preety Female HR

    Query 2 – Using INNER JOIN
    Select TableA.ID, TableA.Name, TableA.Gender, TableA.Department
    From TableA Inner Join TableB
    On TableA.ID = TableB.ID

    Result:-
    ID Name Gender Department
    1 Pranaya Male IT
    2 Priyanka Female IT
    3 Preety Female HR

    As we can see the results are different INNER JOIN just checks only based on ID whereas INTERSECT compares the value of each column. The was the same problem incase of EXCEPT and NOT IN.

    Thank you!

Leave a Reply

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