A full outer join is a join that keeps every row from both tables, matched or not. Rows that pair up appear once, combined. Rows with no counterpart still appear, with NULL filling the columns from the other side.
Think of the four join types as answers to “which side gets to keep its unmatched rows?” Inner says neither, left and right say one, full outer says both.
A worked example: reconciliation
Full outer joins shine when you’re comparing two systems that are supposed to agree. Your payment processor exports a list of charges; your accounting ledger has its own list. You want to see everything, especially the rows only one system knows about:
SELECT
charges.id AS charge_id,
ledger.id AS ledger_id,
charges.amount,
ledger.amount
FROM charges
FULL OUTER JOIN ledger ON charges.id = ledger.external_id
Before: 500 charges from the processor, 498 ledger entries. Three ledger entries reference a charge ID that doesn’t exist.
After: 503 rows. 497 of them have values on both sides. Three rows have a charge_id and a NULL ledger_id — payments that were never booked. Three rows have a ledger_id and a NULL charge_id — ledger entries pointing at nothing.
Neither discrepancy would have been visible from a single left join, and neither would have existed at all in an inner join’s output. Add WHERE charges.id IS NULL OR ledger.id IS NULL and you have an exception report you can run on a schedule.
Other good uses
- Aligning two time series that don’t cover the same periods — say, signups by week from one source and spend by week from another. A full outer join on the week keeps every week either source knows about, so no gap gets silently dropped.
- Schema migrations and ETL validation: comparing an old table against its rebuilt replacement, row by key, to catch anything gained or lost.
Caveats
The output has no single complete key column. charges.id is NULL for right-only rows and ledger.id is NULL for left-only rows, so anything you group by afterwards should use COALESCE(charges.id, ledger.external_id) rather than either column alone.
Full outer joins are also the easiest way to accidentally produce an enormous result set, since nothing is filtered out — a many-to-many join condition multiplies both sides.
Finally, not every database implements FULL OUTER JOIN. MySQL, for example, doesn’t; the usual workaround is a left join UNIONed with a right join.
Full outer joins in Metabase
Full outer join is one of the four join types available in the query builder’s join-type picker. Because engine support varies, the picker only offers the join types your data source actually supports.
Key article
Related terms
Further reading
Put it to work
- Bank reconciliation dashboard — Dashboard
- Finance analytics — Overview
- Build a data pipeline — Integration
