Q10 of 26 · SQL
Write a query to find orphaned records — rows in a child table with no matching parent.
SQLMidsqlorphaned-recordsleft-joindata-integrityreferential-integrity
Short answer
Short answer: LEFT JOIN the child table to the parent on the foreign key, then WHERE parent.id IS NULL filters to rows with no match.
Detail
Orphaned records are a classic data-integrity defect — they appear when a parent row is deleted without cascading to children, or when an INSERT populates the foreign key with an ID that doesn't exist.
-- order_items rows with no matching order
SELECT oi.id, oi.order_id, oi.product_id, oi.quantity
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;
Variant — using NOT EXISTS (often faster on large tables if order_id is indexed):
SELECT oi.id, oi.order_id
FROM order_items oi
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.id = oi.order_id
);
When to run this check:
- After a bulk delete or archive operation on the parent table
- After a data migration where foreign key constraints were disabled
- As part of a post-deployment smoke test
If the database enforces FOREIGN KEY constraints with ON DELETE CASCADE or RESTRICT, orphaned records shouldn't be possible — but not all schemas enforce FK constraints (MySQL MyISAM, disabled FK for performance, NoSQL-backed tables joined in SQL views, etc.).
// EXAMPLE
-- Orphaned order_items (no parent order)
SELECT oi.id, oi.order_id
FROM order_items oi
LEFT JOIN orders o ON o.id = oi.order_id
WHERE o.id IS NULL;
-- Orphaned payments (no parent order)
SELECT p.id, p.order_id
FROM payments p
LEFT JOIN orders o ON o.id = p.order_id
WHERE o.id IS NULL;// WHAT INTERVIEWERS LOOK FOR
LEFT JOIN + WHERE parent IS NULL idiom. Mention of NOT EXISTS as an alternative. At least one real scenario where orphans occur (migration, bulk delete, missing FK constraint).