Seeing Everything: FULL OUTER JOIN

The Reconciliation Problem

The finance team was comparing our internal `payments` table with data from the Stripe payment gateway. Some payments existed in our system but not in Stripe, and some existed in Stripe but not in ours. We needed to find ALL discrepancies.

The Quest: The Union of Both

A `FULL OUTER JOIN` keeps all rows from **both** tables. If there's no match on either side, the missing columns are filled with `NULL`.

Think of it as the entire Venn Diagram—left circle, right circle, and the overlap.

The Implementation: The Syntax

SELECT

i.order_id AS internal_order,

s.charge_id AS stripe_charge,

i.amount AS internal_amount,

s.amount AS stripe_amount

FROM internal_payments i

FULL OUTER JOIN stripe_data s ON i.stripe_id = s.charge_id;

Finding Orphans on BOTH Sides

-- Find mismatches

SELECT *

FROM internal_payments i

FULL OUTER JOIN stripe_data s ON i.stripe_id = s.charge_id

WHERE i.order_id IS NULL OR s.charge_id IS NULL;

The "Oops" Moment

I once did a FULL OUTER JOIN on two huge tables without a clear key, and the result was a Cartesian product of billions of rows. My database slowed to a crawl.

**Pro Tip**: FULL OUTER JOIN should only be used for specific reconciliation tasks. For general reports, stick to INNER or LEFT.

The Victory

The finance team got a clear list of all discrepancies: 15 payments in our system that failed to sync to Stripe, and 3 Stripe charges that were never recorded locally. Data integrity was restored.

Your Task for Today

Create a FULL OUTER JOIN between two related tables and filter for `WHERE ... IS NULL` on both sides to find all orphan records.

*Day 36: The Old Way—JOIN in the WHERE Clause.*