FULL OUTER JOIN

Return all rows from both tables, with NULLs where no match exists on either side.

Overview

FULL OUTER JOIN (or FULL JOIN) returns all rows from both tables. Where there is a match, columns from both are populated. Where there is no match on either side, the unmatched table's columns are NULL. It is essentially a LEFT JOIN + RIGHT JOIN combined you see all rows from both tables. FULL OUTER JOIN is less common than LEFT or INNER JOIN but is extremely useful for data reconciliation: comparing two datasets to find rows unique to each (a "full outer anti-join"), merging two tables that may have non-overlapping rows, or identifying gaps in related data.

Key Points

•Returns ALL rows from both tables
•NULL for columns from the table that had no match
•Equivalent to LEFT JOIN UNION ALL RIGHT JOIN (minus duplicates)
•Full anti-join: WHERE left.id IS NULL OR right.id IS NULL
•Use for data reconciliation between two datasets

Examples

Full outer join showing all matches and mismatches

SELECT
  a.id  AS a_id,  a.val AS a_val,
  b.id  AS b_id,  b.val AS b_val
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

Full outer anti-join rows unique to either table

SELECT
  COALESCE(a.id, b.id) AS id,
  a.val AS in_a,
  b.val AS in_b
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;

FULL OUTER JOIN

Overview

Key Points

Examples

Exercises

Knowledge Check

FULL OUTER JOIN returns rows from which tables?