Introduction

MySQL does not support FULL OUTER JOIN as a native SQL clause, so you have to build the result from other join operations. The standard workaround is reliable, but it only stays correct if you think carefully about duplicates, composite keys, and how unmatched rows should appear.

What a Full Outer Join Should Return

A true full outer join includes three categories of rows:

1. rows with matching keys in both tables
2. rows that exist only in the left table
3. rows that exist only in the right table

In PostgreSQL or SQL Server, one clause can express that directly. In MySQL, you have to assemble the same effect manually.

Use LEFT JOIN Plus Right-Only Rows

The most common pattern is:

1. select all rows from the left table with matches from the right
2. add rows from the right table that had no match on the left
3. combine both result sets with UNION ALL

1SELECT
2  a.id AS a_id,
3  a.name AS a_name,
4  b.id AS b_id,
5  b.name AS b_name
6FROM table_a AS a
7LEFT JOIN table_b AS b
8  ON a.id = b.id
9
10UNION ALL
11
12SELECT
13  a.id AS a_id,
14  a.name AS a_name,
15  b.id AS b_id,
16  b.name AS b_name
17FROM table_b AS b
18LEFT JOIN table_a AS a
19  ON a.id = b.id
20WHERE a.id IS NULL;

The first branch returns matched rows plus left-only rows. The second branch adds right-only rows, filtered by WHERE a.id IS NULL.

Prefer UNION ALL Unless You Truly Need Deduplication

Many people reach for plain UNION, but that changes semantics because it removes duplicates. In join workloads, duplicates may be legitimate and may reflect real cardinality in the data.

UNION ALL is usually the right choice because:

1. it keeps the original row multiplicity
2. it avoids unnecessary deduplication cost
3. it matches the standard full-join emulation pattern

If you later need deduplicated business output, apply that explicitly after the join instead of mixing it into the join itself.

Keep Composite Keys Consistent

When the join key uses more than one column, the right-only filter must match the same logic as the join condition. Otherwise you will accidentally misclassify rows.

1SELECT
2  a.k1,
3  a.k2,
4  a.value AS a_value,
5  b.value AS b_value
6FROM a
7LEFT JOIN b
8  ON a.k1 = b.k1 AND a.k2 = b.k2
9
10UNION ALL
11
12SELECT
13  a.k1,
14  a.k2,
15  a.value AS a_value,
16  b.value AS b_value
17FROM b
18LEFT JOIN a
19  ON a.k1 = b.k1 AND a.k2 = b.k2
20WHERE a.k1 IS NULL AND a.k2 IS NULL;

That filter must be aligned with the actual join rule. If the key logic is more complex, the unmatched-row filter must be equally careful.

Use a Key-Set Strategy for Readability

For some reporting queries, it is easier to build the union of keys first and then join both tables to that key set. In MySQL 8, a common table expression can make that readable.

1WITH all_keys AS (
2  SELECT id FROM table_a
3  UNION
4  SELECT id FROM table_b
5)
6SELECT
7  k.id,
8  a.name AS a_name,
9  b.name AS b_name
10FROM all_keys AS k
11LEFT JOIN table_a AS a ON a.id = k.id
12LEFT JOIN table_b AS b ON b.id = k.id;

This can be easier to extend when more than two tables share the same business key.

Think About Performance Early

Full-join emulation can be expensive on large tables. The most important practical steps are:

1. index the join keys on both sides
2. avoid SELECT *
3. pre-filter rows before joining when possible
4. check the plan with EXPLAIN

1EXPLAIN
2SELECT
3  a.id,
4  a.name,
5  b.name
6FROM table_a AS a
7LEFT JOIN table_b AS b
8  ON a.id = b.id;

Performance problems are often caused by missing indexes, not by the emulation pattern itself.

Common Pitfalls

The most common mistake is assuming MySQL supports native FULL OUTER JOIN syntax and then debugging a parser error that will never go away. Another is using UNION by default and accidentally removing duplicates that should remain visible. Developers also forget that the unmatched-row filter must align with the exact join key, especially for composite keys.

Summary

• MySQL does not support native FULL OUTER JOIN, so you must emulate it.
• The standard approach is LEFT JOIN plus a right-only branch combined with UNION ALL.
• Keep unmatched-row filters aligned with the real join key logic.
• Consider a key-set CTE pattern when readability matters more than minimal SQL length.
• Index join columns and inspect execution plans before blaming the workaround itself.