Full Outer Join
SQL · Joining Multiple Tables
Theory
FULL OUTER JOIN keeps every row from both tables. Matched pairs are combined as usual, left rows with no match get NULLs on the right, and right rows with no match get NULLs on the left. You can think of it as LEFT JOIN plus the unmatched rows a RIGHT JOIN would add.
It is the natural tool for comparing two lists or reconciling two systems: rows present in both, only in A and only in B all show up, and you tell them apart with IS NULL checks on the key columns. PostgreSQL, SQL Server, Oracle, Snowflake and recent SQLite support it. MySQL does not, so there you emulate it with a LEFT JOIN UNION a RIGHT JOIN (or a LEFT JOIN plus an anti-join).
Why we use it
Reconciliation tasks (source vs target, this month vs last month, system A vs system B) need to see everything on both sides, including what is missing from either one. FULL OUTER JOIN shows all of it in one result.
When to use it
Use FULL OUTER JOIN to compare two datasets or merge lists where either side may have extra rows. If only one side must be kept completely, use LEFT JOIN. On MySQL, emulate it with UNION.
How it works
- Match rows from both tables on the ON condition
- Output combined rows for every matched pair
- Any left rows left unmatched? Yes: Output them with NULLs on the right. No: Skip this step.
- Any right rows left unmatched? Yes: Output them with NULLs on the left. No: Skip this step.
- Return everything from both sides
Syntax
SELECT columns
FROM t1
FULL OUTER JOIN t2 ON t1.id = t2.t1_id;Example
SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;Key points
- Unmatched rows from either side appear with NULLs
- Not supported directly in MySQL: emulate with LEFT JOIN UNION RIGHT JOIN
- Ideal for reconciling two datasets
- Use IS NULL on each key to label rows as both / left only / right only
- Result size is at least the larger of the two tables