Beginner

473. JOIN to Find Missing Records

Learn how JOINs can identify records that exist in one table but not another.

📘 Lesson 473 of 728 ⏱ 12 min 🎯 Intermediate

An OUTER JOIN combined with a NULL condition can identify records that have no corresponding match in another table.

Finding missing records is an important part of data analysis, reconciliation, migration testing, and data-quality checks.

Find products that exist in the Products table but have never appeared in Orders.

SQL 
SELECT
    p.ProductID,
    p.ProductName
FROM Products p
LEFT JOIN OrderDetails od
    ON p.ProductID = od.ProductID
WHERE od.ProductID IS NULL;

Used in inventory analysis, customer validation, reconciliation, migration testing, and database quality checks.

  • LEFT JOIN can reveal unmatched records.
  • IS NULL identifies missing matches.
  • This pattern is useful for data validation.
  • The correct join key is important.
  • Missing records can indicate inactivity or data-quality issues.
How do you find records missing from another table?

Use an OUTER JOIN and filter for NULL values on the table where a matching record is missing.

What is a common use of this technique?

It is commonly used for reconciliation, data-quality checks, and finding inactive or missing business records.

Have you finished this lesson?

Introduction to SQL – All Lessons