FULL OUTER JOIN

Keeping Everything from Both Sides

FULL OUTER JOIN is like running LEFT JOIN and RIGHT JOIN simultaneously. You get all rows from both tables: matches where they exist, NULLs where they don’t.

This join gets used maybe once a month in typical analytics work, but when you need it, nothing else works. The classic use case: data reconciliation between two systems.

SELECT
  a.customer_id AS system_a_id,
  b.customer_id AS system_b_id,
  a.name AS system_a_name,
  b.name AS system_b_name
FROM system_a_customers a
FULL OUTER JOIN system_b_customers b
  ON a.email = b.email;

When You Actually Need This

FULL OUTER JOIN shines for:

• Data reconciliation: “Show me what’s in System A but not B, what’s in B but not A, and what matches”
• Migration validation: comparing old and new databases
• Gap analysis: finding holes in both datasets

Finding Unmatched Records

SELECT *
FROM table_a a
FULL OUTER JOIN table_b b
  ON a.id = b.ref_id
WHERE a.id IS NULL
  OR b.ref_id IS NULL;

This gives you a clean list of everything missing on either side.

MySQL Doesn’t Support This

The workaround isn’t pretty, but it works:

-- MySQL: Simulate FULL OUTER JOIN
SELECT
  a.id,
  a.name,
  b.id AS b_id,
  b.name AS b_name
FROM table_a a
LEFT JOIN table_b b
  ON a.id = b.id

UNION

SELECT
  a.id,
  a.name,
  b.id AS b_id,
  b.name AS b_name
FROM table_a a
RIGHT JOIN table_b b
  ON a.id = b.id;

UNION removes the duplicates (rows that match appear in both queries). This is correct, but can be slow.

The Faster Version

For large tables, the UNION dedup overhead can be slow. The optimization: use UNION ALL instead, but add WHERE a.id IS NULL to the second query so it only returns rows from B that have no match in A. Combined with the first query (all of A’s perspective), you get the full result without duplicate checking.

Watch Out For: Same Entity, Different IDs

FULL OUTER JOIN can mislead you when reconciling systems that use different identifiers for the same entity:

• Customer ‘John Smith’ might be ID 123 in System A and ID 456 in System B
• Name variations (‘John Smith’ vs ‘J. Smith’) won’t match
• Typos and data quality issues cause false “no match” results

For serious reconciliation work, you often need fuzzy matching or a shared identifier. FULL OUTER JOIN is just the mechanical part.

Key Takeaways

• FULL OUTER JOIN returns all rows from both tables
• Use it for data reconciliation and finding gaps in both directions
• MySQL requires a UNION workaround (use UNION ALL + WHERE IS NULL for performance)

What’s Next

All the joins we’ve covered require an ON clause to define matching logic. But what if you want every possible combination? That’s CROSS JOIN, and while it’s dangerous, it has legitimate uses.