FULL OUTER JOIN

Introduction

FULL OUTER JOIN keeps unmatched rows from both sides. Used for reconciliation: 'show me every user and every order, marking which side is missing.'

Understanding the topic

Core concepts to understand:

• FROM a FULL OUTER JOIN b ON ....
• NULLs on the side with no match.
• Find mismatches: WHERE a.id IS NULL OR b.id IS NULL.
• MySQL doesn't support it directly — emulate with LEFT JOIN UNION RIGHT JOIN.
• Heavier than INNER/LEFT — use only when needed.

Syntax reference

Visual workflow / architecture:

users           orders
 1 ──── matches ──── 1, 2
 2 ──── matches ──── 3
 3 ────              (no order)
        ────         99 (orphan order)

FULL OUTER → 4 rows including orphans on both sides

SELECT <cols>
FROM   <a> [AS a]
FULL OUTER JOIN <b> [AS b]
       ON  <a.key> = <b.key>;

SELECT u.name, o.amount
FROM   users  u
FULL OUTER JOIN orders o ON o.user_id = u.id;

Real-world use

Daily reconciliation jobs ('our records vs partner records') and data migration audits use FULL OUTER JOIN heavily.

Best practices

• Use only when both sides matter.
• MySQL: emulate or use Postgres for analytics.
• Always show which side was missing in the result.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Full outer vs inner vs left.
• Q2. Emulating FULL JOIN in MySQL.
• Q3. Use case for FULL JOIN.
• Q4. Find mismatches on either side.
• Q5. Performance considerations.