Which invoices disappear when joined to customers?

Define the decision and input records

An inner join removes invoices with unknown customer identities. Diagnose that omission with a left join and test the customer primary key for NULL. Checking customer name instead would confuse a blank name with a missing customer. Keep the orphan invoice in the review queue rather than inventing a customer to make totals match.

All records below are synthetic. Create these tables in a fresh SQLite database before running the read-only query. Quantities are units; monetary values are integer HKD cents, so divide by 100 when presenting HK dollars.

CREATE TABLE customers(id TEXT PRIMARY KEY, name TEXT);
CREATE TABLE invoices(id TEXT PRIMARY KEY, customer_id TEXT, amount_cents INTEGER, due_on TEXT, status TEXT);
CREATE TABLE payments(id TEXT PRIMARY KEY, invoice_id TEXT, amount_cents INTEGER, status TEXT);
CREATE TABLE refunds(id TEXT PRIMARY KEY, payment_id TEXT, amount_cents INTEGER, status TEXT);
INSERT INTO customers VALUES ('C1','Acorn'),('C2','Birch');
INSERT INTO invoices VALUES ('I1','C1',100000,'2026-10-01','issued'),('I2','C2',50000,'2026-10-20','issued'),('I3','C9',20000,'2026-10-02','issued'),('I4','C1',30000,'2026-09-01','void');
INSERT INTO payments VALUES ('P1','I1',40000,'settled'),('P2','I1',20000,'settled'),('P3','I2',50000,'settled'),('P4','I3',20000,'failed');
INSERT INTO refunds VALUES ('R1','P1',10000,'completed'),('R2','P1',5000,'pending'),('R3','P3',50000,'completed');

Run the query and compare the result

SELECT i.id,i.customer_id,i.amount_cents FROM invoices i
LEFT JOIN customers c ON c.id=i.customer_id
WHERE i.status='issued' AND c.id IS NULL ORDER BY i.id;

I3 references C9, but the fixture has only C1 and C2. Its HK$200.00 amount would vanish from an inner-joined issued-invoice report. Verify the original invoice and customer mapping; the amount alone gives no basis for choosing a replacement customer.

Apply the result within its limits

The diagnostic deliberately permits an orphan record in the synthetic dataset. It does not recommend disabling foreign keys in a real system. Customer deletion, archival and migration mapping need their own retention rules.

The supplied setup and result were executed with SQLite. Adapt the schema and status rules to a test copy of your system, then agree the expected result with the process owner. These examples have not been tested on other database platforms.