SQL FULL OUTER JOIN

FULL OUTER JOIN keeps everything: matched pairs, left rows without a partner and right rows without a partner, padded with NULL.

pick a join type

customers
id	name
1	Ada
2	Grace
3	Linus
4	Hedy

orders
id	customer_id
101	1
102	2
103	1
108	9

SELECT c.name, o.id, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

Everything from both sides: matches, customers without orders and orders without customers. Not supported by MySQL (emulate it with LEFT JOIN UNION RIGHT JOIN).

Result · 6 rows
name	order id	total
Ada	101	131
Ada	103	9.5
Grace	102	34.5
Linus	NULL	NULL
Hedy	NULL	NULL
NULL	108	42

Run this on the full sample database

FULL OUTER JOIN example, row by row

Customers without orders and the order without a customer are all kept, so you can see both kinds of mismatch at once, which is handy for reconciling two lists.

SELECT c.name, o.id, o.total
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

▶ Run it on the full sample database

What to know about FULL OUTER JOIN

FULL JOIN and FULL OUTER JOIN are the same.
MySQL has no FULL OUTER JOIN. Emulate it: SELECT … FROM a LEFT JOIN b ON … UNION SELECT … FROM a RIGHT JOIN b ON ….
PostgreSQL, SQL Server, Oracle and SQLite 3.39+ support it directly.
Filter with WHERE a.key IS NULL OR b.key IS NULL to list only the rows that failed to match on either side.

Other join types

LEFT JOIN — all left rows + matching right rows (null when unmatched)
INNER JOIN — only rows that match in both tables
RIGHT JOIN — all right rows + matching left rows (null when unmatched)
All SQL joins compared, including anti-joins, CROSS JOIN and self joins

Syntax for everything else, from GROUP BY to window functions, is on the SQL cheat sheet.

FULL OUTER JOIN questions

What does FULL OUTER JOIN do?

It returns all rows from both tables. Where rows match on the ON condition they are combined; where they do not, the missing side is filled with NULL.

What is the difference between a LEFT JOIN and a FULL JOIN?

LEFT JOIN preserves unmatched rows from the left table only. FULL OUTER JOIN preserves unmatched rows from both tables.

How do I do a FULL OUTER JOIN in MySQL?

Combine a LEFT JOIN and a RIGHT JOIN with UNION: SELECT … FROM a LEFT JOIN b ON a.id = b.a_id UNION SELECT … FROM a RIGHT JOIN b ON a.id = b.a_id. UNION removes the duplicated matched rows.

Is FULL OUTER JOIN the same as CROSS JOIN?

No. CROSS JOIN pairs every row with every row and has no ON condition (4 × 4 = 16 rows in the example). FULL OUTER JOIN only pairs matching rows and keeps the leftovers.