FULL OUTER JOIN in SQL

FULL OUTER JOIN returns every row from both tables — matched rows combined, and unmatched rows from either side filled with NULL on the missing side.

🔵 Shaded = everything from both tables is kept, matched or not.

Reference Tables Used in This Section

Every join note below uses these same two tables, so you can see exactly how each join type treats the same data differently.

employees

departments

Notice: Neha has no department (department_id is NULL), and Sales has no employees at all. These two "unmatched" rows are what make the differences between join types visible.

Syntax

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

Example

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Expected Output:

All 5 logical rows appear: 3 matched, Sales unmatched on the employee side, Neha unmatched on the department side.

Dialect Note: MySQL Has No Native FULL OUTER JOIN

PostgreSQL and SQL Server support FULL OUTER JOIN directly. MySQL does not — you have to simulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

UNION (not UNION ALL) also removes the duplicate matched rows that would otherwise appear from both halves. See UNION.

Practical Use Case

Reconciliation reports — comparing two datasets and surfacing everything that exists in either one, such as matching a payments table against an invoices table to find discrepancies on both sides.

Common Mistakes

• Trying to run FULL OUTER JOIN directly on MySQL — it will error; use the LEFT JOIN + UNION + RIGHT JOIN workaround above
• Using UNION ALL instead of UNION in the MySQL workaround — this duplicates every matched row

Interview Relevance

Q: "How would you write a FULL OUTER JOIN in MySQL?" is a genuinely common trick question specifically because MySQL doesn't support the keyword — knowing the LEFT+RIGHT+UNION workaround is a strong signal of hands-on MySQL experience.

Practice Question

Using the reference tables, write the MySQL-compatible FULL OUTER JOIN workaround and confirm it produces the same 5 rows shown above.

Related SQL Notes

• LEFT JOIN
• RIGHT JOIN
• UNION