FULL OUTER JOIN

Hook: Interviewers love this one because it reveals whether you can keep both matched and unmatched rows without accidentally turning the query into an inner join.

Question: What is a FULL OUTER JOIN?

Answer: A FULL OUTER JOIN returns every row from both tables. Where the join condition matches, the two rows are combined into one result row; where there is no match, the missing side is filled with NULL values, which means 'no value'.

Interview-Ready Answer: I use a FULL OUTER JOIN when I want a complete picture from both sides, not just the overlap. It keeps matched rows together, but it also preserves left-only and right-only rows by padding the missing side with NULL. That makes it ideal for reconciliation, audits, and spotting missing data, and I always remember that filtering in WHERE can accidentally remove those unmatched rows.

What it means

A join combines rows from two tables using a condition in the ON clause. In a FULL OUTER JOIN, the database keeps the matched pairs and also keeps rows that do not find a partner on either side. The unmatched side is padded with NULL columns, so the row still appears even though one table had no match.

How it works under the hood

1. The database reads rows from both inputs and evaluates the ON condition for each potential match.
2. If two rows satisfy the condition, they are merged into one output row.
3. If a row from the left table finds no match, it is still output once, with NULL values for the right table columns.
4. If a row from the right table finds no match, it is still output once, with NULL values for the left table columns.
5. If there are duplicate keys, the result can multiply: 2 matching rows on the left and 3 on the right can produce 6 paired rows.
6. Important: standard SQL equality does not match NULL to NULL, because NULL means 'unknown', not 'equal'.

When and why to use it

• Reconciliation: compare two systems, like orders vs payments.
• Audit checks: find records that exist in one feed but not the other.
• Data drift: compare yesterday's and today's snapshots.
• Completeness: answer 'what is missing on either side?' in one query.

Compared with other joins

Performance and practical notes

The optimizer may implement a FULL OUTER JOIN with a hash join (build an in-memory lookup, then probe the other side) or a merge join (walk sorted inputs together). In rough terms, a hash-based plan is often close to O(n + m) for the scan work, while a sort/merge plan is closer to O(n log n + m log m) if sorting is needed. Memory use can jump quickly on wide tables or million-row inputs, so indexes on the join keys and narrow projections help. Also, not every database supports FULL OUTER JOIN directly: PostgreSQL and SQL Server do, while MySQL still does not, so you may need to emulate it with a LEFT JOIN plus an anti-join UNION ALL.

Important edge cases

1. Filters in WHERE can break the outer join. If you write WHERE right_table.status = 'paid', then left-only rows vanish because NULL = 'paid' is not true.
2. Duplicate keys multiply rows. A FULL OUTER JOIN is not 'one row per key' unless your data is unique on the join columns.
3. NULL keys do not match. If you need null-safe matching, you need a dialect-specific approach or explicit handling.
4. Column names need care. After the join, both sides may have same-named columns, so use aliases or COALESCE for the display key.

Memory hook: think of two guest lists after a party: FULL OUTER JOIN keeps everyone from both lists, pairs up the people who match, and leaves blank name tags for the missing side.

Real-world story

Imagine a checkout service that reconciles orders with payments every night. The product team wants to know three things: paid orders, orders that never got paid, and payment records that do not belong to any order. A FULL OUTER JOIN is perfect here because it exposes all three groups in one result set.

What goes wrong if someone misunderstands it? A junior engineer swaps in an INNER JOIN because 'we only care about matches'. The dashboard suddenly looks healthy, but it is lying: unpaid orders disappear, orphaned payments disappear, and finance thinks the pipeline is clean. In practice, support tickets spike with symptoms like 'customer charged but order missing', reconciliation logs say '0 mismatches', and the nightly report undercounts exceptions by a large margin.

Follow-up & Tricky Questions:

• How do you find only the unmatched rows? Add a filter like WHERE c.customer_id IS NULL OR o.order_id IS NULL. That keeps the 'orphans' from either side and removes the matched pairs.
• How do you emulate FULL OUTER JOIN in MySQL? Use a LEFT JOIN plus a reversed LEFT JOIN for the right-only rows, then combine them with UNION ALL and an anti-join condition so matched rows are not duplicated.
• What happens with duplicate join keys? The join is many-to-many for those keys, so every matching left row pairs with every matching right row. That can increase the output much more than beginners expect.
• Should filters go in ON or WHERE? Put match logic in ON; use WHERE only for final result filtering when you are sure you do not want to lose unmatched rows.
• Can FULL OUTER JOIN return rows with two NULL keys matched together? Not with normal equality. NULL means unknown, so NULL = NULL is not true in standard SQL.
• Is FULL OUTER JOIN the same as LEFT JOIN plus RIGHT JOIN? Not by itself. If you just glue those results together, matched rows appear twice; you need deduping or an anti-join pattern.
• Tricky: if I swap table order, do I get the same result? The set of preserved rows is logically symmetric, but the output columns, aliases, and any duplicate-matching details can differ, so it is not 'the same query' in a practical sense.
• Tricky: does a WHERE clause on one side turn it into an INNER JOIN? It can, effectively. A predicate like WHERE right_table.status = 'paid' removes rows where the right side is NULL, which kills the unmatched left rows.

Common Mistakes:

• Mistake: Using INNER JOIN when you need all rows. Correction: Use FULL OUTER JOIN when you must keep matched and unmatched rows from both sides.
• Mistake: Putting right-side filters in WHERE. Correction: Put match conditions in ON, or you may delete the very unmatched rows you wanted to keep.
• Mistake: Assuming NULL keys match each other. Correction: Standard SQL does not treat NULL as equal to NULL.
• Mistake: Forgetting duplicate keys multiply rows. Correction: Check uniqueness first if you expect one row per business key.

Memory Hook: 'Two guest lists, one party' — keep every guest from both lists, pair the overlaps, and leave blanks where one side had no guest.

Cheat Sheet:

• FULL OUTER JOIN keeps all rows from both tables.
• Matched rows are combined; unmatched rows get NULL on the missing side.
• WHERE can accidentally remove unmatched rows.
• NULL does not match NULL with normal equality.
• Great for reconciliation, audits, and orphan detection.
• MySQL does not support it directly; emulate with joins plus UNION ALL.

Practice Tasks:

• Write a FULL OUTER JOIN between employees and badges to find people without badges and badges without people.
• Add a filter that returns only unmatched rows, then move the same logic from WHERE into ON and observe the difference.
• Test duplicate keys by inserting two matching rows on each side and count how many output rows you get.