Beginner

513. Finding Orphan Records

Understand orphan records and learn how to identify child records without a valid parent.

📘 Lesson 513 of 728 ⏱ 12 min 🎯 Intermediate

An orphan record is a child record that references a parent record that does not exist or is no longer available.

Orphan records can indicate broken relationships and data-quality problems that may affect reporting and applications.

Find orders whose CustomerID does not exist in the Customers table.

SQL 
SELECT
    o.OrderID,
    o.CustomerID
FROM Orders o
LEFT JOIN Customers c
    ON o.CustomerID = c.CustomerID
WHERE c.CustomerID IS NULL;

Common in database auditing, migration validation, reconciliation, and data-quality analysis.

  • Orphan records have missing parent relationships.
  • LEFT JOIN can identify orphan records.
  • Foreign key constraints can help prevent them.
  • Data migrations can create orphan records.
  • Orphan records should be investigated before correction.
What is an orphan record?

An orphan record is a child record that references a parent record that does not exist.

How can orphan records be identified?

Use a LEFT JOIN from the child table to the parent table and filter for missing parent keys.

Have you finished this lesson?

Introduction to SQL – All Lessons