In this lesson, you will learn how to identify and isolate "orphaned records"—data entries that exist in a child table but lack a valid, corresponding parent in a related table.
Understanding Orphans
In a healthy database, every record in a child table (like Orders) should point to a valid record in a parent table (like Customers) using a foreign key. An "orphan" occurs when that reference is broken—perhaps a customer was deleted, but their old orders remain, or a data import failed to link correctly. These records can cause your reports to miss data or produce inaccurate calculations.
Detecting Orphans via Left Joins
The most efficient way to find these records is to use a LEFT JOIN combined with a WHERE clause that checks for NULL values.
Imagine we want to find orders that don't have a matching customer. We join the Orders table to the Customers table, keeping all orders, and then filter for rows where the Customers side turned up empty:
SELECT o.order_id, o.customer_id
FROM Orders o
LEFT JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
Why this works
When you perform a LEFT JOIN, SQL keeps every record from the Orders table. If it finds a match in Customers, it fills in the columns. If no match exists, it fills the columns from the Customers table with NULL. By adding WHERE c.customer_id IS NULL, we tell SQL: "Show me only the orders where the connection to the customer failed."
This technique isolates the "orphans" so you can either clean them up, investigate why they exist, or exclude them from your financial reports to ensure your totals stay accurate.
Key Takeaway: You can identify orphaned records by performing a LEFT JOIN from the child table to the parent table and filtering for results where the parent’s primary key is NULL.