INTERSECT Clause in SQL Server

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

INNER JOIN

INTERSECT
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.
Read More
http://technet.microsoft.com/en-us/library/ms188055.aspx