SQL FULL OUTER JOIN Explained

A LEFT JOIN keeps every row from the left table; a RIGHT JOIN keeps every row from the right table. A FULL OUTER JOIN does both at once — it returns every row from both tables, matching them up where it can and filling in NULL on whichever side has no match.

Using the customers (id, name, city) and orders (id, customer_id, amount, order_date) tables, a full outer join answers a question neither a left nor a right join can answer alone: "show me every customer and every order, matched where possible, with nothing left out on either side."

SQL FULL OUTER JOIN Syntax

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id
ORDER BY c.id;

Result:

name | order_id | amount
---------|----------|-------
Alice | 101 | 250.00
Alice | 104 | 75.50
Bob | 102 | 120.00
Charlie | NULL | NULL
NULL | 105 | 60.00

Notice the last row: an order (105) with no matching customer — perhaps the customer record was deleted, or the customer_id was entered incorrectly. A full outer join surfaces exactly this kind of orphaned data, which makes it a useful tool for data-quality checks even outside everyday reporting.

MySQL Doesn't Support FULL OUTER JOIN — Here's the Workaround

PostgreSQL and SQL Server support FULL OUTER JOIN directly, but MySQL does not. The standard workaround is to combine a LEFT JOIN and a RIGHT JOIN with UNION, which automatically removes the duplicate rows that matched on both sides:

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id

UNION

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

The LEFT JOIN half captures every customer (matched or not), the RIGHT JOIN half captures every order (matched or not), and UNION merges the two result sets while deduplicating the rows that appeared in both — giving you the exact same output as a native FULL OUTER JOIN.

When You'd Actually Use This

Full outer joins come up less often than inner or left joins in everyday application code, but they're genuinely useful for reconciliation tasks: comparing two datasets to find what's missing from each side, auditing for orphaned foreign keys, or merging two overlapping customer lists from different systems. If you only need unmatched rows and don't care about the matched ones, you can also add a WHERE c.id IS NULL OR o.id IS NULL filter to the full outer join to isolate just the mismatches.

---