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 returns employees record. 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.
 
If you see in the above graphical representation, 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 common which is nothing but an intersection.
 
Now let's see how it works in the database.
 
So we are going to create 2 tables, EmpTable and ManagerEmp and then we will insert records in them. 
  1. CREATE TABLE EmpTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50))  
  2. CREATE TABLE ManagerTable(EmpName   VARCHAR(50),City    VARCHAR(50),Title   VARCHAR(50))  
  3.   
  4. INSERT INTO EmpTable  
  5. SELECT EmpName='John',City='Stamford',Title='Operator'  
  6.   
  7. INSERT INTO EmpTable  
  8. SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer'  
  9.   
  10. INSERT INTO EmpTable  
  11. SELECT EmpName='Smith',City='Wilton',Title='Driver'  
  12.   
  13.   
  14. INSERT INTO ManagerTable  
  15. SELECT EmpName='Mike',City='Wilton',Title='Driver'  
  16.   
  17. INSERT INTO ManagerTable  
  18. SELECT EmpName='Smith',City='Wilton',Title='Driver'  
  19.   
  20. INSERT INTO ManagerTable  
  21. SELECT EmpName='Jonathan',City='Armonk',Title='Accountant'  
  22.   
  23. INSERT INTO ManagerTable  
  24. SELECT EmpName='Warner',City='Stamford',Title='Customer Service'  
  25.   
  26. INSERT INTO ManagerTable  
  27. SELECT EmpName='Luis',City='Danbury',Title='Electrical Engineer'  
Now run below the query to find out the common employees in both tables. 
  1. SELECT * FROM EmpTable  
  2.   
  3. SELECT * FROM ManagerTable  
  4.   
  5. -- INTERSECTION  
  6. SELECT * FROM EmpTable  
  7. INTERSECT  
  8. SELECT * FROM ManagerTable  
Here is the output.
 
If you see below, in both the "EmpTable" and the "ManagerTable" tables, Luis and Smith both are employees. To join these 2 queries with INTERSECT, it gave these 2 names.
 
 
One thing is to remember here is both the tables/result sets should have the same columns and the same datatype for those columns, otherwise, it may give you "Conversion failed when converting...." if the data type does not match.
 
Hope you enjoyed reading this article and benefitted from it.