PatternSQL
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
Pattern
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.