Database Analyst · NOT EXISTS · orphan rows · NOT IN and NULL
3. Orphaned invoices
Concept: NOT EXISTS · orphan rows · NOT IN and NULLshow
An orphan is a child row pointing at a parent that doesn't exist, which happens whenever no foreign key enforces the link. Find them with NOT EXISTS or LEFT JOIN ... WHERE parent.key IS NULL. Avoid NOT IN against a column with NULLs: x NOT IN (list with a NULL) is never true, so it silently returns nothing.
Example (different data):
SELECT r.id, r.room_code
FROM bookings r
WHERE r.room_code IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM rooms m WHERE m.room_code = r.room_code
);Your task
Finance is moving off a legacy billing system that never enforced links, and invoices pointing at accounts that don't exist can't be migrated.
Return id, account_code and amount of every legacy_invoices row whose account_code matches no account_code in legacy_accounts (exact match). Invoices with no account_code at all are a different problem; leave them out. Sort by id.
Starting Postgres in your browser…
Checking your access…