Beginner

690. JOIN with INTERSECT Result

Understand how an INTERSECT result can be used as a dataset for further JOIN operations.

📘 Lesson 690 of 728 ⏱ 15 min 🎯 Advanced

INTERSECT returns rows common to two compatible result sets, and that result can be used as a derived dataset for a JOIN.

It allows common records to be identified before additional descriptive information is retrieved.

Find customers appearing in both current and approved customer lists, then JOIN them with customer details.

SQL 
SELECT
    c.CustomerID,
    c.CustomerName
FROM Customers c
INNER JOIN (
    SELECT CustomerID
    FROM CurrentCustomerList

    INTERSECT

    SELECT CustomerID
    FROM ApprovedCustomerList
) x
    ON c.CustomerID = x.CustomerID;

Used in reconciliation, compliance validation, data matching, and master-data analysis.

  • INTERSECT returns common rows.
  • The participating queries need compatible columns.
  • The result can be used as a derived table.
  • Useful for reconciliation and matching.
  • Database support for INTERSECT varies.
What does INTERSECT return?

It returns rows that are common to both compatible result sets.

Can INTERSECT be used inside a JOIN?

Yes. Its result can be placed in a derived table and then JOINed with another table.

Have you finished this lesson?

Introduction to SQL – All Lessons