FULL OUTER JOIN

A FULL OUTER JOIN returns every row from both tables. Where a row on either side has a match on the other, the columns are combined normally. Where a row has no match, the columns coming from the other table are filled with NULL. In effect, a FULL OUTER JOIN is what you get by combining a LEFT JOIN and a RIGHT JOIN into one result — nothing from either table is ever dropped.

Worked example

Every customer and every order, matched where possible

SQL

SELECT c.name, c.customer_id, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

name   | customer_id | order_id | amount
-------+-------------+----------+-------
Alice  | 1           | 101      | 250.00
Alice  | 1           | 102      | 75.50
Bob    | 2           | 103      | 300.00
Carol  | 3           | 104      | 120.00
Dave   | 4           | NULL     | NULL
NULL   | NULL        | 105      | 45.00

This single query surfaces both problem cases at once: Dave (customer_id 4) has no orders, and order 105 has no matching customer. Neither LEFT JOIN nor RIGHT JOIN alone would show both of these gaps in one result set — you would need to run two separate queries and compare them.

Use case: a two-way data-integrity check

FULL OUTER JOIN is especially handy as a one-shot sanity check when reconciling two tables that are supposed to be consistent with each other, such as verifying that every order has a valid customer and every customer's orders are accounted for. Filtering for rows where either key is NULL isolates exactly the mismatches on both sides.

Isolating mismatches in either direction

SQL

SELECT c.customer_id, c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o
  ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
   OR o.order_id IS NULL;

customer_id | name | order_id
------------+------+---------
4           | Dave | NULL
NULL        | NULL | 105

MySQL does not support FULL OUTER JOIN

Dialect gap: MySQL has no FULL OUTER JOIN

PostgreSQL, SQL Server, Oracle, and SQLite (3.39+) all support FULL OUTER JOIN directly. MySQL does not implement it at all — attempting to use it raises a syntax error. The standard workaround is to UNION together a LEFT JOIN and a RIGHT JOIN (or a second LEFT JOIN with the tables swapped), since a UNION automatically removes the duplicate rows that matched on both sides.

FULL OUTER JOIN workaround for MySQL

SQL

-- Standard FULL OUTER JOIN emulation on MySQL:
SELECT c.name, c.customer_id, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.name, c.customer_id, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

Why UNION, not UNION ALL

UNION (rather than UNION ALL) is used deliberately here — it deduplicates rows, so the matched rows produced by both the LEFT JOIN and the RIGHT JOIN halves only appear once in the final result, exactly mimicking a real FULL OUTER JOIN.

FULL OUTER JOIN returns every row from both tables, with NULLs filling in unmatched columns on either side.
It is the union of what a LEFT JOIN and a RIGHT JOIN would each produce.
Filtering for a NULL key on either side is a fast way to spot mismatches in both directions at once.
MySQL has no native FULL OUTER JOIN — emulate it with a UNION of a LEFT JOIN and a RIGHT JOIN.