FULL OUTER JOIN

Keep every row from both sides — the union of LEFT and RIGHT

Intermediate 13 minFULL OUTER JOINOUTER JOIN

The Union of Both Sides

FULL OUTER JOIN returns every row from both tables. Where the join key matches, the row contains data from both sides. Where there is no match on one side, that side fills with NULL — but the row still appears.

The mental model — Think of it as INNER JOIN ∪ LEFT-only-rows ∪ RIGHT-only-rows. Matched pairs appear once, unmatched left rows appear with NULL on the right, unmatched right rows appear with NULL on the left.

When to reach for it — Reconciling two parallel sources — orders vs. payments, customers vs. reviewers, expected vs. actual — where you need to see everything from both sets, including the asymmetry. Anything in only-A is a gap on side B and vice versa.

Watch the join key — When a row only exists on one side, the join key on the *other* side is NULL. Use COALESCE(a.key, b.key) in your SELECT and GROUP BY to get a single non-null key value per output row.

MySQL gap — MySQL does not support FULL OUTER JOIN syntax. Emulate it as LEFT JOIN ... UNION ALL ... RIGHT JOIN ... WHERE left.key IS NULL. Postgres, SQL Server, Oracle, SQLite 3.39+, Snowflake, and BigQuery all support it natively.

Knowledge Canvas

How FULL OUTER JOIN Works

Every row from both tables

= INNER JOIN ∪ LEFT-only ∪ RIGHT-only
Matched pairs appear once with both sides populated
Unmatched rows on either side appear with NULL on the other
FULL JOIN = FULL OUTER JOIN (OUTER is optional)

The Reconciliation Pattern

Comparing two parallel sources

Orders vs payments — find unpaid orders AND orphaned payments
Expected vs actual — find missing AND unexpected items
Two snapshots — see what was added, removed, and unchanged
Use COALESCE on the join key for a single non-null identifier per row

FULL OUTER Pitfalls

Easy mistakes

Forgetting COALESCE on the join key — review-only rows squash into a NULL key
MySQL does not support it — emulate with LEFT JOIN UNION ALL RIGHT JOIN
GROUP BY on one side's key alone loses the asymmetric rows
Without DISTINCT, double-joining (FULL OUTER twice) over-counts matched rows

When to Use Which

Picking the right outer join

INNER: only matched rows

LEFT/RIGHT: matched + one side's unmatched

FULL OUTER: matched + both sides' unmatched

Use when asymmetry on both sides matters

Rare in OLTP queries

Common in audits, ETL diffs, reconciliation

Performance Notes

Cost vs alternatives

Implemented as hash or merge join in most engines — comparable to LEFT/RIGHT JOIN
On indexed keys with low overlap: roughly LEFT + RIGHT cost combined
Materializing both sides via UNION ALL of LEFT/RIGHT is sometimes faster on small dataset
On non-indexed keys, prefer pre-aggregation with CTEs to limit the join size

EXPLAINOPTIMIZER

Syntax

Syntax Template

1-- Standard FULL OUTER JOIN

2SELECT

3 COALESCE(a.key, b.key) AS key,

4 a.col,

5 b.col

6FROM table_a a

7FULL OUTER JOIN table_b b

8 ON a.key = b.key;

10-- MySQL emulation (no native FULL OUTER)

11SELECT * FROM table_a a LEFT JOIN table_b b ON a.key = b.key

12UNION ALL

13SELECT * FROM table_a a RIGHT JOIN table_b b ON a.key = b.key WHERE a.key IS NULL;

FULL OUTER JOINEvery row from both tables; NULLs fill missing sides

COALESCEPick the non-null key value for output

Sample Data

customers

12 rows

id	name
C1001	Aarav Sharma
C1002	Sara Chen
C1003	James Wilson
C1004	Maria Garcia
C1005	Yuki Tanaka
C1006	Priya Patel
C1007	Alex Johnson
C1008	Chen Wei
C1009	Emma Brown
C1010	Omar Hassan
C1011	Lena Muller
C1012	Ravi Kumar

orders

12 rows

id	customer_id	total
5001	C1001	45.5
5002	C1002	449
5003	C1003	12.5
5004	C1004	128
5005	C1005	245
5006	C1001	89.99
5007	C1006	748
5008	C1007	59.99
5009	C1008	425
5010	C1009	18.99
5011	C1010	79.99
5012	C1012	45

reviews

12 rows

id	product_id	customer_id	rating
7001	P101	C1001	5
7002	P101	C1003	4
7003	P101	C1005	3
7004	P102	C1002	5
7005	P102	C1006	4
7006	P103	C1004	4
7007	P104	C1007	5
7008	P105	C1009	5
7009	P105	C1012	4
7010	P107	C1008	3
7011	P108	C1011	5
7012	P109	C1001	5

Worked Example

Show every customer who has either placed an order OR written a review, with counts of each. Highlight the asymmetry.

SQL

1SELECT

2 COALESCE(o.customer_id, rv.customer_id) AS customer_id,

3 COUNT(DISTINCT o.id) AS orders,

4 COUNT(DISTINCT rv.id) AS reviews

5FROM orders o

6FULL OUTER JOIN reviews rv

7 ON rv.customer_id = o.customer_id

8GROUP BY

9 COALESCE(o.customer_id, rv.customer_id)

10ORDER BY

11 customer_id;

The result contains 12 distinct customer_ids — every customer who appears in *either* table. C1010 (Omar Hassan) has 1 order and 0 reviews. C1011 (Lena Muller) has 0 orders and 1 review. Neither would appear if we used INNER JOIN, and we would need two separate queries (LEFT and RIGHT) to surface both gaps. COALESCE on the join key returns whichever side is non-null.

Output

12 rows

customer_id	orders	reviews
C1001	2	2
C1002	1	1
C1003	1	1
C1004	1	1
C1005	1	1
C1006	1	1
C1007	1	1
C1008	1	1
C1009	1	1
C1010	1	0
C1011	0	1
C1012	1	1

Common Mistakes

❌

Forgetting COALESCE on the join key

SQL

1SELECT

2 o.customer_id,

3 COUNT(DISTINCT o.id) AS orders,

4 COUNT(DISTINCT rv.id) AS reviews

5FROM orders o

6FULL OUTER JOIN reviews rv

7 ON rv.customer_id = o.customer_id

8GROUP BY

9 o.customer_id;

When a customer only appears in reviews, o.customer_id is NULL — so all the review-only customers get squashed into a single row with customer_id = NULL. The output is wrong: you lose the identity of every reviewer who never placed an order.

Use COALESCE(o.customer_id, rv.customer_id) in both SELECT and GROUP BY so every output row carries a real customer_id.

❌

Trying it on MySQL

MySQL is the major outlier — it does not support FULL OUTER JOIN syntax. Running the query above on MySQL raises a syntax error.

Emulate with LEFT JOIN ... UNION ALL ... RIGHT JOIN WHERE left.key IS NULL. Postgres, SQLite 3.39+, SQL Server, Oracle, Snowflake, and BigQuery all support FULL OUTER JOIN natively.

Key Concepts

1Returns rows from BOTH tables — matched rows joined, unmatched rows padded with NULL

2FULL OUTER JOIN = LEFT JOIN ∪ RIGHT JOIN — every row from either side appears at least once

3Use COALESCE on the join key to avoid NULL keys in the result

4Not supported in MySQL — emulate with UNION of LEFT and RIGHT JOINs

Pro Tip

INNER drops the asymmetry. LEFT shows half. RIGHT shows the other half. FULL OUTER JOIN is the only join that surfaces *everything* — the matched rows AND the gaps on both sides — in a single query.

When to Use

Data reconciliation (orders vs. payments), audit reports (expected vs. actual), comparing two snapshots, finding rows present in only-A or only-B.

Challenge

Solve the problem below

For every customer, show their order count and review count — including customers who only ordered (no reviews) and customers who only reviewed (no orders). Order by name.

Your Query

RIGHT JOIN

SELF JOIN