Lesson 31 of 61

5 mins read

SQL FULL JOIN: Full Outer Join & Complete Reconciliation

In Plain English

The `FULL OUTER JOIN` (or `FULL JOIN`) combines the effects of both LEFT JOIN and RIGHT JOIN: it returns ALL records when there is a match in EITHER left or right table. Rows with no match on either side are padded with NULLs, making it the primary tool for data reconciliation audits.

The Mental Model

"`FULL JOIN` is a complete reunion of two separate class rosters: everyone who attended Class A and everyone who attended Class B is listed, showing who was in both classes, who was only in A, and who was only in B."

Deep Dive: How It Works

Full Outer Union: Produces the complete union (A ∪ B) with matched pairs aligned side-by-side.

Data Reconciliation: `WHERE A.id IS NULL OR B.id IS NULL` finds discrepancies where records exist in one system (e.g. Stripe) but not in another (e.g. internal database).

Emulation in SQLite / MySQL: If FULL JOIN is unsupported, emulate by combining `LEFT JOIN ... UNION ... LEFT JOIN` with reversed tables.

Syntax Blueprint

-- Full Outer Join Syntax (PostgreSQL / SQL Server / Oracle / SQLite 3.39+):
SELECT a.id, b.id
FROM table_a AS a
FULL OUTER JOIN table_b AS b ON a.key = b.key;

FULL OUTER JOIN preserves all unmatched rows from both tables simultaneously.

Core Rules to Remember

Complete Inclusion: No row from either table is dropped.

Reconciliation Audits: Essential for financial auditing and verifying data migration completeness.

Emulation with UNION: Can be emulated in any SQL engine using LEFT JOIN combined with UNION.

Common Beginner Traps & How to Fix Them

Mistake:Assuming MySQL natively supports the `FULL OUTER JOIN` keyword.

Why it happens: MySQL does not implement the FULL JOIN syntax natively.

How to fix: Emulate with `SELECT ... LEFT JOIN ... UNION SELECT ... RIGHT JOIN ...`.

Pro Tip: Use `FULL OUTER JOIN` when synchronizing two independent database tables to identify additions, deletions, and updates in one query.

Live Interactive Example

Hit Run Code to see it live

Financial Reconciliation with Emulated Full Outer Join

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The reconciliation revealed TX-1 missing from gateway, TX-2 matched, and TX-3 missing internally.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Reconcile Two Lists with UNION

Run the reconciliation union query to match list `a` and `b`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.