INTERSECT clause in SQL Server help us to find common rows in different SQL sets. In the case of INNER JOIN operation we join two sets horizontally or combine different columns of two or more tables that have a matching Primary Key and Foreign Key. Whereas INTERSECT combines two sets vertically or gives us the matching rows in those sets. A Venn Diagram demonstration of both operations is demonstrated below
For INTERSECT to work each sets must be union compatible or corresponding column of both sets must have the same number of columns, same data type, and same order of columns in order for two sets to be union-compatible and for INTERSECT to work properly.
To take a practical example from Adventure Works database. We want to find names of Sales person who are only customers of Adventure Wors. To find this we used CTE to make it simple where SETA = Customers and SETB = Sales Person
;WITH SETA AS( SELECT FirstName,MiddleName,LastName FROM Sales.Customer JOIN Person.Person ON Person.BusinessEntityID = Customer.CustomerID ), SETB AS( SELECT FirstName,MiddleName,LastName FROM Sales.SalesPerson JOIN Person.Person ON Person.BusinessEntityID = SalesPerson.BusinessEntityID ) SELECT FirstName,MiddleName,LastName FROM SETA INTERSECT SELECT FirstName,MiddleName,LastName FROM SETB;
This gives us 17 sales persons who are also purchased from Adventure works in the past.