INTERSECTION in SQL Server

Introduction

Sometimes, we have a scenario when we want to get a common set of rows from 2 different result sets.

For example, we have 2 queries, and both return employees' records. If we want to find who all employees are present in both result sets, that time we can use INTERSECT to get the result.

Below is the graphical representation of how INTERSECT works.

The above graphical representation shows that the left 2 circles have the B and C letters in common. The left side picture shows how the 2 circles have B and C letters in common which are nothing but an intersection.

Now let's see how it works in the database.

So we will create 2 tables, EmpTable and ManagerEmp, and then insert records in them. 

CREATE TABLE EmpTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50))  
CREATE TABLE ManagerTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50))  
  
INSERT INTO EmpTable  
SELECT EmpName='John',City='Stamford',Title='Operator'  
  
INSERT INTO EmpTable  
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer'  
  
INSERT INTO EmpTable  
SELECT EmpName='Smith',City='Wilton',Title='Driver'    
  
INSERT INTO ManagerTable  
SELECT EmpName='Mike',City='Wilton',Title='Driver'  
  
INSERT INTO ManagerTable  
SELECT EmpName='Smith',City='Wilton',Title='Driver'  
  
INSERT INTO ManagerTable  
SELECT EmpName='Jonathan',City='Armonk',Title='Accountant'  
  
INSERT INTO ManagerTable  
SELECT EmpName='Warner',City='Stamford',Title='Customer Service'  
  
INSERT INTO ManagerTable  
SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer'  

It is now run below the query to find the common employees in both tables. 

SELECT * FROM EmpTable  
  
SELECT * FROM ManagerTable  
  
-- INTERSECTION  
SELECT * FROM EmpTable  
INTERSECT  
SELECT * FROM ManagerTable  

Here is the output.

As you see below, Luis and Smith are both employees in both the "EmpTable" and the "ManagerTable" tables. To join these 2 queries with INTERSECT, it gave these 2 names.

Summary

One thing to remember here is that both the tables/result sets should have the same columns and datatype for those columns. Otherwise, it may give you "Conversion failed when converting...." if the data type does not match.

I hope you enjoyed reading this article and benefitted from it.