FULL JOIN

Explanation

A FULL JOIN (FULL OUTER JOIN) is the union of a left and right join: it keeps all rows from both tables. Where a match exists, the two sides are combined into one row. Where a row on either side has no partner, the opposite side's columns are filled with NULL.

It's useful for reconciliation — "show me everything in table A and everything in table B, and tell me what's missing on each side."

Syntax

SELECT a.col, b.col
FROM table_a AS a
FULL JOIN table_b AS b
  ON a.key = b.key;

Interactive Example

Join departments and employees with a FULL JOIN so any department without employees and any employee without a department would both still appear. Then isolate the unmatched rows.

Common Mistakes

• Thinking FULL JOIN drops nothing — true, but it may duplicate logic if your join key isn't unique on both sides, producing more rows than expected.
• Using it when INNER or LEFT would do. FULL JOIN returns the largest result set, which is rarely what a report needs.
• Assuming MySQL supports it. It doesn't; emulate with LEFT JOIN ... UNION RIGHT JOIN.

Best Practices

• Reach for FULL JOIN specifically when you must surface mismatches in both directions.
• Pair it with COALESCE so NULLs read as "—" or "none" in output.
• Use the IS NULL filter trick to produce a clean "missing on either side" exception report.

Practice Question

Using a FULL JOIN between departments and employees, count how many departments have at least one employee versus how many employees have no department (hint: use the IS NULL filter on each side separately).

Summary

FULL JOIN preserves every row from both tables, merging matches and padding unmatched sides with NULL. It's the go-to tool for finding what exists in one table but not the other — in either direction.

FAQ