Beginner

691. JOIN with EXCEPT Result

Use an EXCEPT result to identify records that exist in one dataset but not another before JOINing.

📘 Lesson 691 of 728 ⏱ 15 min 🎯 Advanced

EXCEPT returns rows from the first query that are not present in the second compatible query.

It is useful for reconciliation, exception reporting, and identifying missing relationships.

Find customers in the current customer list who are not in the approved list and retrieve their details.

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

    EXCEPT

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

Used in compliance checks, reconciliation, migration validation, and data-quality analysis.

  • EXCEPT returns rows unique to the first result.
  • Both queries need compatible columns.
  • The result can be JOINed as a derived table.
  • Useful for exception analysis.
  • Database support and syntax can vary.
What does EXCEPT return?

It returns rows from the first query that do not exist in the second query.

Where is EXCEPT useful?

It is useful for reconciliation, missing-record detection, and data-quality validation.

Have you finished this lesson?

Introduction to SQL – All Lessons