Beginner

655. ANTI JOIN with NOT EXISTS

Use NOT EXISTS to reliably identify records without related matches.

📘 Lesson 655 of 728 ⏱ 14 min 🎯 Advanced

NOT EXISTS returns an outer row when the correlated subquery finds no matching record.

It provides a clear and reliable method for finding missing relationships.

Find products that have never appeared in an order.

SQL 
SELECT
    p.ProductID,
    p.ProductName
FROM Products p
WHERE NOT EXISTS (
    SELECT 1
    FROM OrderItems oi
    WHERE oi.ProductID = p.ProductID
);

Used for unused products, inactive customers, missing mappings, and data-quality validation.

  • NOT EXISTS returns non-matching outer rows.
  • The subquery is correlated with the outer row.
  • Multiple matching records do not affect the result.
  • It handles NULLs differently from NOT IN.
  • It is a strong pattern for missing relationships.
What does NOT EXISTS do?

It returns the outer row when the related subquery returns no matching rows.

Why can NOT EXISTS be safer than NOT IN?

NOT EXISTS does not suffer from the same NULL-related three-valued logic issue that can make NOT IN unexpectedly return no rows.

Have you finished this lesson?

Introduction to SQL – All Lessons