SQL FULL OUTER JOIN — Return All Rows from Both Tables

What Is a FULL OUTER JOIN?

A FULL OUTER JOIN combines the behavior of LEFT JOIN and RIGHT JOIN. It returns every row from both tables. Rows that match are combined normally; rows without a match on either side have their missing columns filled with NULL.

Syntax

SELECT a.id, a.name, b.id, b.project
FROM employees AS a
FULL OUTER JOIN projects AS b
  ON a.id = b.employee_id;

Result Structure

The result set contains three categories of rows:

Category	Left columns	Right columns
Matched	Real data	Real data
Left-only	Real data	NULL
Right-only	NULL	Real data

Venn Diagram Interpretation

FULL OUTER JOIN highlights both entire circles — the left-only region, the intersection, and the right-only region. Nothing is excluded.

MySQL Workaround

MySQL does not support FULL OUTER JOIN syntax. You must emulate it with a UNION of LEFT JOIN and RIGHT JOIN:

SELECT a.*, b.*
FROM table_a AS a
LEFT JOIN table_b AS b ON a.id = b.id
UNION
SELECT a.*, b.*
FROM table_a AS a
RIGHT JOIN table_b AS b ON a.id = b.id;

Using UNION (not UNION ALL) automatically removes duplicate rows that appear in both halves.

Data Reconciliation Pattern

FULL OUTER JOIN is the standard tool for comparing two datasets:

SELECT
  COALESCE(a.id, b.id) AS id,
  CASE
    WHEN a.id IS NULL THEN 'Missing in A'
    WHEN b.id IS NULL THEN 'Missing in B'
    ELSE 'Present in both'
  END AS status
FROM dataset_a AS a
FULL OUTER JOIN dataset_b AS b ON a.id = b.id;

This immediately shows which records are missing from each side.