The INTERSECT operator in SQL Server is a powerful tool that allows you to retrieve common records from two or more queries. It is similar to the INNER JOIN clause in many cases, but with some distinct differences.

When using the INTERSECT operator, the number and order of columns must be the same in all queries, and the data types must be compatible. This ensures that the results are accurate and meaningful.

Let’s explore how the INTERSECT operator works by using examples with the AdventureWorks database.

Example 1: Simple INTERSECT

In this example, we want to find the common EmployeeIDs between two queries:

SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE EmployeeID IN (1, 2, 3)
INTERSECT
SELECT * FROM HumanResources.EmployeeDepartmentHistory WHERE EmployeeID IN (3, 2, 5)

The result set shows the EmployeeIDs that are common in both queries, which are 2 and 3.

Example 2: INTERSECT between Two Tables

In this example, we use the INTERSECT operator to find common records between two tables:

SELECT VendorID, ModifiedDate FROM Purchasing.VendorContact
INTERSECT
SELECT VendorID, ModifiedDate FROM Purchasing.VendorAddress

The result set displays the records that are common in both tables. In this case, there are 104 common records.

Example 3: INNER JOIN

Now, let’s compare the INTERSECT operator with the INNER JOIN clause:

SELECT va.VendorID, va.ModifiedDate FROM Purchasing.VendorContact vc
INNER JOIN Purchasing.VendorAddress va ON vc.VendorID = va.VendorID AND vc.ModifiedDate = va.ModifiedDate

The result set shows all the records that are common to both tables. However, if we carefully observe, we can notice that many of the records are duplicates. Unlike the INTERSECT operator, the INNER JOIN retrieves all records from both tables, including duplicates.

Example 4: INNER JOIN with DISTINCT

To eliminate duplicate records when using INNER JOIN, we can use the DISTINCT clause:

SELECT DISTINCT va.VendorID, va.ModifiedDate FROM Purchasing.VendorContact vc
INNER JOIN Purchasing.VendorAddress va ON vc.VendorID = va.VendorID AND vc.ModifiedDate = va.ModifiedDate

The result set in this example does not contain any duplicate records, as the DISTINCT clause is used in the SELECT statement. The final result is exactly the same as in Example 2, where we used the INTERSECT operator.

In summary, the INTERSECT operator in SQL Server allows you to retrieve common records from multiple queries, similar to the INNER JOIN clause. However, the INTERSECT operator provides distinct results without duplicates. If you need to eliminate duplicates when using INNER JOIN, you can use the DISTINCT clause.