Get only the common records from two tables using INTERSECT

IN SQL Server INTERSECT operator is somewhat opposite to EXCEPT operator. Unlike EXCEPT, INTERSECT operator is used to get only the common records from two tables or statements.

There are several ways to get these kind of common records in SQL Server. The most common technique used is INNER JOIN. However, there are few advantages of using INTERSECT over INNER JOIN.

Advantages of INTERSECT over INNER JOIN

• Unlike INNER JOIN, INTERSECT can match NULL values and returns them as result.
• INTERSECT will not return duplicate values.
• INTERSECT compares all the columns specified in the select part of the statement. INNER JOIN compares only the specified columns.

Illustration

Now, let us see how INTERSECT operator works. For this illustration, I’m using the tables Sales.Customers and Sales.Orders in the WideWorldImporters database. Here, I have to list down all the IDs of the customers who placed orders during a specific year. Below is the sample SQL Statement.

SELECT CustomerID
    FROM Sales.Customers
EXCEPT
SELECT CustomerID
    FROM Sales.Orders
    WHERE YEAR(OrderDate) = 2015;

Reference

• Read more about INTERSECT operator at Microsoft Docs.