SQL Server – Using INTERSECT Operator

* Compatibility SQL Server 2005 onwards
INTERSECT operator was introduced in SQL Server 2005. INTERSECT can be used to return distinct records from the left and right side of a query.

INTERSECT is often compared with INNER JOINS, it can be said that INTERSECT is almost same as INNER JOIN with the following key differences:

• INTERSECT by default returns a DISTINCT result set whereas INNER JOIN return duplicates unless it is instructed not to do so by using DISTINCT clause to the SELECT statement

• INTERSECT requires same column number and order on both side of the query with comparable data type, INNER JOIN does not require same column number and order condition

For more details on INTERSECT visit: MSDN

Let us consider a real-world INTERSECT example using NorthWind database. You can download and install NorthWind database from the following location.

(a) How many Products have been at least ordered once

SELECT
ProductID
FROM
Products
INTERSECT
SELECT
ProductID
FROM
[Order Details]

ProductID
--------------
1
2
3
4
5
….
….

Query returns 77 rows, which are unique ProductID. The above query demonstrated that we can extract unique ProductID for all the Products purchased using INTERSECT operator instead of writing an INNER JOIN to accomplish the same output

(b) INTERSECT Vs INNER JOIN

SELECT
ProductID
FROM
Products
INTERSECT
SELECT
ProductID
FROM
[Order Details]

ProductID
--------------
1
2
3
4
5
….
….
Returns 77 rows

SELECT
ORD.OrderID
FROM
[Order Details] ORD
INNER JOIN Orders O ON O.OrderID = ORD.OrderID

ProductID
------------
1
2
3
4
5
……
……

Query returns 2155 rows, which are non-unique ProductID. The above query demonstrated the default behavior of INNER JOIN

SELECT
DISTINCT ORD.OrderID
FROM
[Order Details] ORD
INNER JOIN Orders O ON O.OrderID = ORD.OrderID

ProductID
------------
1
2
3
4
5
……
……

Query returns 77 rows, which are unique ProductID as we used DISTINCT keyword to filter out the duplicates.