How do you perform a FULL OUTER JOIN in MySQL?

Question

Accepted Answer

37
 How do you perform a FULL OUTER JOIN in MySQL?
 Level: Intermediate to Advanced — A classic "gotcha" question, since MySQL lacks native FULL OUTER JOIN support.

 A FULL OUTER JOIN returns all rows from both tables — matched rows are combined, and unmatched rows from either side appear with NULLs. However, MySQL does not support the FULL OUTER JOIN syntax directly (unlike PostgreSQL or SQL Server).

 
 The solution: Simulate a FULL OUTER JOIN by combining a LEFT JOIN and a RIGHT JOIN using the UNION operator. UNION automatically removes the duplicate matched rows.
 

 Prerequisites
 
 To follow this, you should understand: (1) how LEFT and RIGHT JOINs work, and (2) the difference between UNION (removes duplicates) and UNION ALL (keeps duplicates).
 

 The Logic (3 Parts Combined)
 
 LEFT JOIN: Gets all left-table rows + matches.
 RIGHT JOIN: Gets all right-table rows + matches.
 UNION: Merges both and removes the duplicated matching rows.
 

 Quick Example — Simulating FULL OUTER JOIN
-- Method 1: LEFT JOIN UNION RIGHT JOIN
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

 If your data may contain genuine duplicate rows that you want to preserve, use UNION ALL with a filter to avoid double-counting the matched rows:
-- Method 2: UNION ALL (preserves duplicates, avoids re-matching)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION ALL

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.dept_id IS NULL; -- only the right-only rows

 
 Performance note: UNION is slower than UNION ALL because it performs a duplicate-removal (sort/dedupe) step. Prefer Method 2 with UNION ALL for large datasets when you can filter correctly.
 

 
 Interviewer tip: The one-liner they want — "MySQL has no native FULL OUTER JOIN, so you emulate it by combining a LEFT JOIN and a RIGHT JOIN with UNION, which merges all rows from both tables and removes duplicates."

How do you perform a FULL OUTER JOIN in MySQL?

Answer with Explanation

How do you perform a FULL OUTER JOIN in MySQL?

Prerequisites

The Logic (3 Parts Combined)

Quick Example — Simulating FULL OUTER JOIN