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.