Drill 3 of 10 in Database Analyst
0

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…