Joins · Advanced

FULL OUTER JOIN

Keeps every row from both sides of a join.

MySQL has no native FULL OUTER JOIN, so this page emulates it with LEFT JOIN and UNION ALL.

Overview

FULL OUTER JOIN keeps every matched and unmatched row from both tables. Databases such as PostgreSQL support it natively. MySQL does not, so this page combines two LEFT JOIN queries with UNION ALL and excludes already matched rows from the second branch.

Basic syntax

SELECT a.id AS a_id, a.name AS a_name, b.id AS b_id, b.name AS b_name
FROM a
LEFT JOIN b ON a.id = b.id
UNION ALL
SELECT a.id AS a_id, a.name AS a_name, b.id AS b_id, b.name AS b_name
FROM b
LEFT JOIN a ON a.id = b.id
WHERE a.id IS NULL;

Examples

FULL OUTER JOIN example

Join a and b, keeping the id=2 match as well as rows that exist on only one side.

Sample table

Table：a

a
id	name
1	A1
2	A2

Table：b

b
id	name
2	B2
3	B3

Run result

result
a_id	a_name	b_id	b_name
1	A1	NULL	NULL
2	A2	2	B2
NULL	NULL	3	B3

Common issues

Does MySQL support FULL OUTER JOIN directly?

No. MySQL commonly emulates it with LEFT JOIN queries in both directions combined with UNION ALL.

Why must the second query exclude matched rows?

The first query already returns matches; the second should add only unmatched right-table rows.

Why use UNION ALL in the emulation?

After matched rows are excluded from the second part, UNION ALL avoids unnecessary deduplication and preserves legitimate duplicate rows.

Was this explanation clear?

← SELF JOIN