SQL FULL OUTER JOIN Example

FULL OUTER JOIN returns every row from both tables — matched where possible, NULL-padded where not. It's the join for reconciliation and migration-validation queries, where unmatched rows on either side are exactly what you're looking for.

Every row from both tables, matched where possible

A FULL OUTER JOIN (often just FULL JOIN) returns every row from both tables — rows that match on both sides, plus unmatched rows from the left (with NULLs for the right table's columns) and unmatched rows from the right (with NULLs for the left's). It's the union of what a LEFT JOIN and a RIGHT JOIN would each return separately.

Reconciling two datasets

Finding every employee/department pairing, including employees with no department and departments with no employees.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
-- Employees with no department: department_name is NULL
-- Departments with no employees: first_name/last_name are NULL

When you actually need FULL OUTER JOIN

If you only ever need unmatched rows from one specific side, a plain LEFT JOIN (or RIGHT JOIN) is simpler and usually cheaper — reach for FULL JOIN specifically when unmatched rows from either side matter.

Scenario	Why FULL JOIN fits
Data reconciliation	compare two systems' records and see rows that exist in only one side
Migration validation	confirm every source row landed in the target, and no extra target rows appeared
Comparing two time periods	show categories that existed in either period, even if absent from the other

A portability note

FULL OUTER JOIN is standard SQL and has long been supported by Oracle, PostgreSQL, and SQL Server. MySQL only added native support in version 8.0.31 (2022) — on older MySQL, the standard workaround is a LEFT JOIN ... UNION ... RIGHT JOIN combination (or a LEFT JOIN UNION a second LEFT JOIN with the table order swapped) to reproduce the same result manually.

Frequently asked questions

What's the difference between FULL OUTER JOIN and FULL JOIN?

They're the same thing — OUTER is optional in the keyword for LEFT, RIGHT, and FULL joins in every mainstream SQL dialect. FULL JOIN and FULL OUTER JOIN are identical.

How do I find only the unmatched rows from a FULL OUTER JOIN?

Add a WHERE clause checking that one side's join key is NULL: WHERE e.department_id IS NULL OR d.department_id IS NULL returns only rows that didn't match on either side, excluding the fully-matched rows.

Does MySQL support FULL OUTER JOIN?

Yes, as of MySQL 8.0.31 (2022). On earlier MySQL versions, the standard workaround is combining a LEFT JOIN and a RIGHT JOIN (or two LEFT JOINs with table order swapped) with UNION.

Is FULL OUTER JOIN slower than INNER JOIN?

It typically does more work, since it has to identify and NULL-pad unmatched rows from both sides rather than discarding them — but the real cost depends on table sizes, indexes, and how selective the join actually is. Don't avoid it on a performance assumption if it's the correct join for the question you're asking.