FULL OUTER JOIN
Return every row from both tables, matched or not — and how to get the same result on engines that don't support it.
Syntax
SELECT ...
FROM left_table lt
FULL OUTER JOIN right_table rt
ON lt.key = rt.key;What it does
Preserves every row from both tables, filling in NULL on whichever side
has no match. It's LEFT JOIN and RIGHT JOIN at once.
Loading editor…
Engine support
MySQL has no FULL OUTER JOIN (as of MySQL 8). Neither does older SQLite
(added in 3.39, 2022) or SQLite compiled without it. Check before you ship one.
| Engine | Native FULL OUTER JOIN |
|---|---|
| PostgreSQL | yes |
| SQL Server | yes |
| Oracle | yes |
| SQLite 3.39+ | yes |
| MySQL (all versions) | no |
Emulating it where it's missing
LEFT JOIN the two tables, then UNION a RIGHT-style query filtered to the
rows the first side missed:
SELECT c.customer_id, c.name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT c.customer_id, c.name, o.order_id, o.status
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;UNION (not UNION ALL) is required — it's what dedupes the rows that matched
and would otherwise appear from both halves.
When to use it
Reconciling two datasets: rows only on the left, rows only on the right, and rows in both — a full diff in one query.
Common risk
Easy to overread. The result can be much larger than a LEFT or INNER JOIN
over the same tables, and a careless COUNT(*) on it mixes three different
kinds of row (matched, left-only, right-only) into one number.
- LEFT JOIN — half of a full outer join.
- Set operations — UNION, the operator the emulation is built on.
- Rows missing in another table — when you only need one side's unmatched rows, this is usually simpler.
Safety check before shipping
- confirm the business rule actually requires unmatched rows from both sides
- inspect row counts before using the result for dashboards or reports
- decide whether
NULLplaceholders should be coalesced or left explicit
LEFT JOIN
Preserve every row on the left and match records on the right when they exist — and avoid the WHERE clause that quietly turns it back into an INNER JOIN.
APPLY (CROSS APPLY / OUTER APPLY)
Run a correlated subquery or table-valued function once per row of the left side — a SQL Server–specific join that a regular JOIN's ON clause can't express.