Your billing system says 1,204 active subscriptions. Your CRM says 1,187. Which rows disagree? A LEFT JOIN shows what billing has that CRM lacks; a RIGHT JOIN shows the reverse. FULL OUTER JOIN shows both discrepancy lists in one query — which is why it's the reconciliation tool of every data team.
What you'll learn
What
FULL OUTER JOIN returns matched pairs like INNER JOIN, plus unmatched rows from the LEFT table (right columns NULL-filled), plus unmatched rows from the RIGHT table (left columns NULL-filled). Nothing from either side is discarded.
Why
Comparing two datasets is a daily task: old system vs new system, source vs warehouse copy, forecast vs actuals. Any one-sided join silently hides one direction of mismatch. FULL OUTER JOIN is the only join that makes both directions visible simultaneously — and its NULL patterns tell you exactly which side each discrepancy lives on.
Where it's used
Data migrations, pipeline parity checks, finance reconciliations (payments vs invoices), and merging two partial reference lists into one complete one.
Where this runs in production
Internal ledgers are reconciled against bank statement lines — a full-outer comparison surfaces payments without bank entries and bank entries without payments, each routed to a different ops queue.
During pipeline migrations, teams full-outer-join the legacy table to the new one on the business key and alert on any row that appears on only one side.
Rights teams match label-delivered track lists against the internal catalog; unmatched rows on either side become ingestion bugs or takedown candidates.
Two colleagues each kept a guest list for the same event. To build the definitive list you lay them side by side: names on both lists line up (matches), names only on list A get an empty cell in the B column, and names only on list B get an empty cell in the A column. Nobody is dropped just because one colleague missed them — that side-by-side master sheet is exactly a FULL OUTER JOIN.
With no key duplication: rows(FULL) = matches + left-only + right-only. A 1,204-row table and a 1,187-row table sharing 1,150 keys yield 1,150 + 54 + 37 = 1,241 rows. If either side has duplicate keys, matches multiply exactly as with INNER JOIN — outer joins add orphans on top of, not instead of, normal matching semantics.
SELECT b.subscription_id, b.amount, c.crm_id, c.plan FROM billing_subs b FULL OUTER JOIN crm_subs c ON c.subscription_id = b.subscription_id;
Same ON syntax as every other join — only the keyword changes. Column choice matters downstream: b.subscription_id is NULL for right-only rows and c.crm_id is NULL for left-only rows, which is precisely the signal you'll filter on.
After a full outer join on keys, three WHERE filters split the result: both keys non-NULL → matched; right key IS NULL → left-only (exists only in the left table); left key IS NULL → right-only. Reconciliation reports are exactly these filters plus counts. Important: test the JOIN KEY columns for NULL, not arbitrary data columns that might be legitimately NULL in matched rows.
SELECT
COALESCE(b.subscription_id, c.subscription_id) AS sub_id,
CASE
WHEN c.subscription_id IS NULL THEN 'billing_only'
WHEN b.subscription_id IS NULL THEN 'crm_only'
ELSE 'matched'
END AS side
FROM billing_subs b
FULL OUTER JOIN crm_subs c
ON c.subscription_id = b.subscription_id;Two staple idioms in one query. COALESCE merges the two half-NULL key columns into one complete key — without it, the unified list has holes on whichever side didn't match. The CASE label turns the NULL pattern into a readable category you can GROUP BY.
Exactly as with LEFT JOIN: a plain WHERE condition on one side's column (WHERE c.plan = 'pro') evaluates to unknown for rows where that side is all-NULL and drops them — silently converting your FULL join into a one-sided or inner join. Side-specific conditions belong in the ON clause, or must explicitly allow NULL (WHERE c.plan = 'pro' OR c.subscription_id IS NULL).
Two tables enter, three row populations exit. Click each region to see its NULL signature — the pattern you filter on in reconciliation queries.
Select a type to see its full definition, operations, and data science usage.
online_customers has keys 1, 2, 3. store_customers has keys 2, 3, 4. Join them fully.
Keys 2 and 3 match and pair up. Key 1 exists only online — store_id comes back NULL. Key 4 exists only in-store — online_id comes back NULL. Four rows total: 2 matches + 1 left-only + 1 right-only. Count check: 2+1+1 = 4.
online_id | store_id
-----------+----------
1 |
2 | 2
3 | 3
| 4
(4 rows)Your task
Reconcile two product lists. warehouse_products(sku, stock) and catalog_products(sku, price) each miss some SKUs. Produce one row per SKU from EITHER list with: a unified sku column, stock, price, and a status column reading 'ok' when both sides have the SKU, 'not_in_catalog' when only the warehouse has it, and 'not_in_warehouse' when only the catalog has it. Sort by sku. Fill in the blanks.
sku | stock | price | status -----+-------+-------+------------------ A1 | 10 | 19.99 | ok B2 | 0 | | not_in_catalog C3 | 5 | 4.50 | ok D4 | | 99.00 | not_in_warehouse (4 rows)
Write your solution in the editor on the right, then hit Run.
Table A has keys {1,2,3}; table B has keys {3,4}. With unique keys, how many rows does A FULL OUTER JOIN B return?
After t1 FULL OUTER JOIN t2 ON t2.id = t1.id, a result row has t1.id = NULL and t2.id = 42. What does it represent?
Which WHERE clause keeps ONLY the discrepancies (both directions) from a full outer join of a and b on key?
Why does the MySQL emulation use UNION ALL rather than UNION between the LEFT JOIN branch and the filtered RIGHT JOIN branch?
What is the most likely explanation?
forecast(month, predicted) and actuals(month, revenue) each cover a partly different set of months. Write ONE query returning a summary: for each of the statuses 'matched', 'forecast_only', 'actuals_only', the count of months (columns: status, months). Use a FULL OUTER JOIN and GROUP BY. Order by status.
When would you choose FULL OUTER JOIN over LEFT JOIN, and why is it comparatively rare in production analytics code?
FULL OUTER JOIN is the right tool when neither dataset is authoritative and you need both directions of mismatch visible at once — reconciling billing against CRM, legacy pipeline against replacement, forecast against actuals. LEFT JOIN answers 'enrich this primary table'; FULL answers 'compare these two peers'. It's rare in routine analytics because most business questions do have an authoritative side: orders enriched with customer data doesn't care about customers with no orders in a revenue query, and if it does, LEFT from the customer side expresses it. So in practice FULL joins cluster in data-quality checks, migrations, and finance reconciliation scripts. I'd add that seeing a FULL OUTER JOIN in a KPI query is a review flag — it often means the author wasn't sure which side was the spine of the analysis.
Walk me through building a reconciliation report between two systems' subscription tables. What does each part of the query do?
I full-outer-join the two tables on the business key — say subscription_id — so no row from either side can hide. From the NULL signature of the key columns I derive a status with CASE: right key NULL means left-only, left key NULL means right-only, both present means matched; the NULL checks must precede any value comparison in the CASE, since comparing against a NULL side yields unknown. I COALESCE the two key columns into one unified key for readable output. For matched rows I add a value-level comparison — e.g. ABS(a.amount − b.amount) > 0.01 — because keys agreeing doesn't mean values do. The detailed report filters to non-'ok' rows; the executive summary is GROUP BY status with counts. Run daily during a migration, the target state is a zero-row exception report, which is what gives the team license to decommission the old system.
Do NULL join keys match each other in a FULL OUTER JOIN? What are the practical consequences?
No. The ON predicate uses ordinary comparison semantics, and NULL = NULL evaluates to unknown, so two rows with NULL keys never pair — each becomes an orphan on its own side of the result. The practical consequence in reconciliations is that rows with missing business keys inflate BOTH orphan lists and can never be matched by the join at all; they need a separate handling lane, typically a pre-filter (WHERE key IS NOT NULL) plus their own 'unkeyed' count in the report so they aren't silently ignored. If the business genuinely wants NULLs to match — rare, but it happens with optional dimensions — you have to say so explicitly: ON a.key = b.key OR (a.key IS NULL AND b.key IS NULL), or in PostgreSQL the cleaner a.key IS NOT DISTINCT FROM b.key, accepting that these forms usually defeat index usage.
Common Mistakes to Avoid
1) Classifying rows by testing a data column for NULL instead of the join key — matched rows can carry legitimately NULL data, mislabeling them as orphans. 2) Putting a one-sided condition in WHERE (WHERE c.plan = 'pro'), which drops that side's NULL-filled orphans and demotes the join; side conditions go in ON. 3) Forgetting COALESCE on the key, shipping a report whose key column has holes on every unmatched row. 4) Expecting NULL keys to match each other — they orphan on both sides instead; handle unkeyed rows in a separate lane. 5) Using UNION instead of UNION ALL in the MySQL emulation, paying a dedup pass that can also merge genuine duplicate rows and corrupt counts.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: FULL OUTER JOIN with a two-guest-lists analogy of your own.' • 'Quiz me: two small key sets, I predict matched / left-only / right-only counts.' • 'Show me a reconciliation query on payments vs invoices and explain each NULL test.' • 'Why doesn't NULL = NULL match in a join, and what is IS NOT DISTINCT FROM?' • 'Interview mode: have me design a migration parity check and critique my CASE ordering.'
Glossary
FULL OUTER JOIN (FULL JOIN) — join keeping matched rows plus unmatched rows from BOTH tables, NULL-filling the missing side. Orphan — a row with no partner in the other table. Left-only / right-only — orphan populations, identified by which side's key is NULL. NULL signature — the pattern of NULL-filled key columns that classifies each result row. Reconciliation — systematically comparing two datasets and accounting for every difference. Parity check — a reconciliation asserting two pipelines produce identical output. COALESCE — first non-NULL argument; merges half-filled key columns into one. Anti-join filter — WHERE side.key IS NULL, isolating orphans. UNION ALL — concatenates result sets without deduplication. IS NOT DISTINCT FROM — PostgreSQL's NULL-safe equality, under which NULL equals NULL. Fan-out — match multiplication caused by duplicate join keys, present in outer joins too.
Recommended Resources
• Docs: PostgreSQL manual — 'Joined Tables' for the formal definition of all outer join types. • Read: the 'JOIN' visual reference at Mode's SQL tutorial to cement the three-population picture. • Practice: take any two overlapping lists you have (even two CSV exports), load them, and write the full reconciliation — unified key, status CASE, summary GROUP BY. • Next in DSM: all joins so far connected two different tables — Self Joins shows what happens when a table needs to meet itself: hierarchies, sequences, and pair analysis.
Recap
✓ FULL OUTER JOIN keeps matches plus orphans from both sides — no input row is ever discarded. ✓ Row count = matches + left-only + right-only; duplicate keys still fan out matches exactly as in INNER JOIN. ✓ The NULL signature of the join keys classifies every row; COALESCE the keys for a unified column and CASE-label the populations. ✓ Filter WHERE either key IS NULL for a both-directions discrepancy report; test join keys, never data columns. ✓ NULL keys never match each other — they orphan on both sides and need their own handling lane. ✓ Where FULL OUTER JOIN is unsupported (MySQL), emulate with LEFT JOIN ∪ (RIGHT JOIN … WHERE left key IS NULL) via UNION ALL. Next up: Self Joins. Every join so far paired two different tables — next you'll alias one table twice and join it to itself, the trick behind org charts, event sequences, and same-customer comparisons.
Run your code to see the output here.