Intersection in SQL Server

As you know this is basically to combine multiple result sets into single to fetch the common records in multiple result sets. Inner join finds common rows horizontally, while an INTERSECT finds common rows vertically.

Let’s look at the example:

SELECT EmployeeID,Name,Salary,DOJ from Employee
INTERSECT
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST

We are combining the results from Employee and Employee_HIST table. When you use INTERSECT operator it will give you the common records from both the tables meaning the records that are duplicated in both the tables.