Left Join Visualizer

ACCTG 6155 · Week 3 · Why an INNER JOIN can quietly lose money in cash application

The scenario The lockbox bank just handed Summit Gear six new payments to apply. To match each payment to its invoice (and confirm the amount), you join the lockbox_payments table to the invoices table on invoice_id. But two of the payments reference invoices that do not exist in our system, the lockbox mis-coded the checks. Watch what each join does with that gap.

1. The two source tables

lockbox_payments (6 rows, every payment the bank deposited this week)
payment_id	invoice_id	amount

invoices
invoice_id	customer_name	amount_due

Two gaps, not one Our invoices table has four rows, and only three of them match a payment. Two payments, PAY-9004 and PAY-9006, point at invoices (INV-2026-900000, INV-2026-900001) that nobody can find. One invoice, INV-2026-0061, has no payment at all. Those are two different problems, and which one you can see depends on how you write the join.

2. Write the join

The question you are asking:

Join type:

Loading the SQL engine and the two tables.

3. The join result

Rows in result

On this report

On file

Missing from the report

4. Reconcile the orphans

In real cash-app work, the fix for a dropped row is to track down the missing invoice. It might be in another AR system, on a colleague's desk, or one phone call away at the customer or the lockbox bank. Simulate finding them and watch the INNER JOIN stop losing payments.

Then flip the question. Locating those two invoices closes the cash gap. It does nothing for INV-2026-0061, which nobody has paid.

What to take away

An INNER JOIN keeps a row only if it matches on both sides. If the invoice table is missing rows the payments reference, those payments vanish, and "matched cash" drops with no error message.
A LEFT JOIN keeps every row from the table you put first. Rows with no match show a NULL on the other side, so the gap becomes visible instead of silent. That is why you reach for a LEFT JOIN when a missing match would cost you money.
Which table you put first decides which problem you can see. Put lockbox_payments first and you find cash that cannot be applied. Put invoices first and you find an invoice nobody paid. Same keyword, and neither direction sees the other's problem.
A key that repeats changes the row count. INV-2026-0011 was paid twice, so four invoices come back as five rows. Check what your join did to the row count, every time.
For cash application, a silently missing row is the dangerous kind of error: the report still looks correct.