Find orphaned rows
Rows whose parent id points nowhere — orders with no customer, line items with no order — found with a LEFT JOIN and a check for the missing side.
Last updated
SELECT o.* FROM orders AS o LEFT JOIN customers AS c ON o.customer_id = c.id WHERE c.id IS NULL;
Why it’s written this way
An INNER JOIN only keeps rows that match on both sides, which is exactly wrong for finding what's missing. A LEFT JOIN keeps every row from orders regardless of whether customers has a match, filling the customers columns with NULL when there's no match — so WHERE c.id IS NULL isolates precisely the rows the join couldn't complete.
Filtering on c.id rather than any other customers column matters: c.id is a primary key, so it's never NULL in a row that actually matched. The only way it shows up NULL here is a failed join, which is what makes it a reliable test for "orphaned."
Edge cases to know
- →If orders.customer_id can itself be NULL, those rows are trivially orphaned too and will appear here — decide whether that's the question being asked or a separate one worth splitting out.
- →Filtering on o.customer_id IS NULL instead of c.id IS NULL gives a different, narrower answer: it only catches orders that never had a customer_id, not ones pointing at a customer that was later deleted.
- →On large tables, an index on customers.id (usually free as the primary key) and one on orders.customer_id keep this a fast anti-join instead of a full scan of both tables.
Part by part
- LEFT JOIN customers AS c
- Keeps every order whether or not a matching customer exists. An INNER JOIN would discard exactly the rows this query is looking for.
- ON o.customer_id = c.id
- The join condition. Orphans are rows where this finds nothing, leaving every column of c as NULL.
- WHERE c.id IS NULL
- The anti-join filter, and the one case where testing the right table in WHERE is correct — it is what keeps the unmatched rows rather than removing them.
Common mistakes
WHERE c.id != o.customer_idNULL fails every comparison including inequality, so unmatched rows are not returned. The result is empty and looks like there are no orphans, which is the opposite of the truth.
WHERE c.id IS NULLSELECT o.* FROM orders AS o LEFT JOIN customers AS c ON o.customer_id = c.id WHERE c.name IS NULLTesting a nullable column cannot distinguish a missing customer from a customer with no name. Test the join key or the primary key, which is NULL only when the join found nothing.
DELETE FROM orders WHERE customer_id NOT IN (SELECT id FROM customers)If any id in the subquery is NULL, NOT IN returns unknown for every row and the delete affects nothing. It fails safe here and the same construct fails dangerously when used to keep rows.
Questions
Why do orphaned rows exist at all?
Almost always a missing foreign key constraint, or one added after the data. A database that enforces the relationship cannot produce orphans, so finding them is usually the prelude to adding the constraint.
Should I delete them?
Investigate first — an orphan can mean a legitimate deletion cascaded incorrectly, or that a customer record was removed when it should have been archived. The rows are evidence of a bug before they are junk.
Is LEFT JOIN or NOT EXISTS faster?
It depends on the planner and the data, and modern planners often produce the same execution for both. NOT EXISTS tends to read more clearly when you want no columns from the other table at all.
How do I stop this recurring?
Add the foreign key constraint with an explicit ON DELETE behaviour — RESTRICT to block the deletion, SET NULL to keep the row, CASCADE to remove children. Choosing deliberately is the point; the default is rarely what anyone wants.