Python SQL FULL JOIN: Full Outer Join & Complete Reconciliation
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.
"`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
Common Beginner Traps & How to Fix Them
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 ...`.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Reconcile Two Lists with UNION
- Run the reconciliation union query to match list `a` and `b`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.