A LEFT JOIN loses customers without paid orders

Illustrative learning scenario; not a claimed client engagement. A fictional subscription report must list every account and any paid invoice. An account with only a draft invoice and an account without invoices both disappear after a status filter is added. This fixture represents reporting logic, not client work.

Symptoms

Diagnose a right-side WHERE predicate that silently removes unmatched rows.

Diagnosis

1. Write the output contract: one row per paid invoice, plus a null-extended row for each account with no paid invoices. Multiple paid invoices may legitimately produce multiple rows.
2. Run the original query with the invoice status predicate in WHERE and compare account identifiers, not just total counts.
3. Move the predicate into ON and inspect the null-extended rows. Filtering for paid OR NULL in WHERE still loses an account whose only invoice is draft.

Runnable minimal example

Oracle Database 19c SQL; read-only CTE fixture. Not executed on Oracle; expected output is documentation-reviewed.

WITH accounts AS (
  SELECT 1 id FROM dual UNION ALL SELECT 2 FROM dual
  UNION ALL SELECT 3 FROM dual
), invoices AS (
  SELECT 1 account_id, 'paid' status FROM dual
  UNION ALL SELECT 2, 'draft' FROM dual
)
SELECT a.id, NVL(i.status, 'NO PAID INVOICE') invoice_state
FROM accounts a
LEFT JOIN invoices i
  ON i.account_id = a.id AND i.status = 'paid'
ORDER BY a.id;

Expected output — not executed

ID | INVOICE_STATE
1  | paid
2  | NO PAID INVOICE
3  | NO PAID INVOICE

Fix and boundaries

Put the eligible-invoice condition in ON when all accounts must survive. A predicate on account attributes may still belong in WHERE. If the report needs exactly one row per account, aggregate the eligible invoices to that grain before the join; this fix does not collapse multiple matches.

Why it works

The ON condition decides which invoices qualify as matches. LEFT JOIN then supplies null invoice columns for accounts 2 and 3. A right-side equality in WHERE instead evaluates to UNKNOWN for a null-extended row and removes it. The NVL expression only labels the result; it does not control eligibility.

Prevention

Test no invoice, draft-only, one paid and multiple paid cases. Reconcile distinct account coverage separately from row counts. Review filters whenever an outer join is edited.

Practice exercise

Add two paid invoices for account 2. Predict the new row count, then write an aggregated version that returns a paid-invoice count for all three accounts, including zero for account 3.

Documentation

• Oracle joins