FULL OUTER JOIN in MySQL Using UNION

FULL OUTER JOIN is meant to return every row from both tables, matched where possible and NULL-padded where not — effectively combining LEFT JOIN and RIGHT JOIN into one comprehensive result. Standard SQL supports FULL OUTER JOIN as a native keyword, and databases like PostgreSQL and SQL Server implement it directly.

MySQL, however, does not support the FULL OUTER JOIN keyword at all. This surprises many learners and is a genuinely important, practical fact for any developer working with MySQL specifically. The good news is that FULL OUTER JOIN behavior can be reconstructed precisely using a combination of LEFT JOIN, RIGHT JOIN, and the UNION operator, and understanding exactly how this workaround works deepens your grasp of both JOIN and set operations simultaneously.

Key Definitions

• FULL OUTER JOIN: A JOIN that returns all rows from both tables, matching where possible and filling unmatched columns from either side with NULL.
• UNION: A SQL set operator that combines the results of two SELECT statements and removes duplicate rows from the combined output.
• UNION ALL: A SQL set operator that combines the results of two SELECT statements while keeping all duplicate rows.
• Simulated FULL OUTER JOIN: A MySQL workaround pattern combining a LEFT JOIN result and a RIGHT JOIN result via UNION to reproduce full outer join behavior.

What You'll Learn

• Explain what FULL OUTER JOIN is meant to return conceptually.
• Recognize that MySQL does not support FULL OUTER JOIN as a native keyword.
• Construct a FULL OUTER JOIN equivalent in MySQL using LEFT JOIN, RIGHT JOIN, and UNION.
• Understand why UNION (not UNION ALL) is used to avoid duplicating matched rows.
• Apply this pattern to a real reporting scenario requiring a complete two-sided view.

Detailed Explanation

Conceptually, FULL OUTER JOIN is the union of everything a LEFT JOIN would return and everything a RIGHT JOIN would return between the same two tables. A LEFT JOIN guarantees all rows from the left table; a RIGHT JOIN guarantees all rows from the right table. If you combine both result sets and eliminate the rows that appear in both (the ones that actually matched), you get exactly the behavior of a FULL OUTER JOIN: every row from both tables, matched where a relationship exists, and NULL-padded on whichever side has no corresponding match.

Since MySQL lacks a native FULL OUTER JOIN keyword, this is exactly the technique used to simulate it: write a LEFT JOIN query, write a RIGHT JOIN query using the same tables and columns in the SELECT list, and combine them with UNION. The UNION operator automatically removes exact duplicate rows, which is essential here because the matched rows (present in both the LEFT JOIN and RIGHT JOIN results) would otherwise appear twice. If UNION ALL were used instead, every genuinely matched row would be duplicated in the final output, which is incorrect.

This technique is a common interview question specifically for MySQL-focused roles, since interviewers want to confirm candidates know this is a real limitation of MySQL (as opposed to PostgreSQL or SQL Server) and know the standard workaround, rather than assuming FULL OUTER JOIN syntax will simply work.

Visual Summary

Draw two overlapping circles labeled 'employees' and 'departments', similar to previous JOIN diagrams, but this time shade the entire left circle, entire right circle, and the overlapping middle — the whole Venn diagram is shaded. Add a caption below: 'FULL OUTER JOIN = LEFT JOIN result UNION RIGHT JOIN result, with UNION removing duplicated matched rows.'

Quick Reference

JOIN Type	Rows Guaranteed	Native MySQL Support?
INNER JOIN	Only matched rows	Yes
LEFT JOIN	All left table rows	Yes
RIGHT JOIN	All right table rows	Yes
FULL OUTER JOIN	All rows from both tables	No — must simulate with UNION

SQL Example

-- Simulated FULL OUTER JOIN in MySQL
SELECT
  e.employee_name,
  d.department_name
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.department_id

UNION

SELECT
  e.employee_name,
  d.department_name
FROM employees e
RIGHT JOIN departments d
  ON e.department_id = d.department_id;

The first SELECT (LEFT JOIN) guarantees every employee appears, with NULL department_name for anyone without a matching department. The second SELECT (RIGHT JOIN) guarantees every department appears, with NULL employee_name for any department with no assigned employees. UNION combines both result sets and removes duplicate rows — meaning any employee-department pair that matched and therefore appeared identically in both queries is only listed once in the final output, exactly reproducing FULL OUTER JOIN behavior.

Real-World Examples

• Reconciliation reports comparing two systems (such as an internal CRM and an external payment gateway) use a simulated FULL OUTER JOIN to show every record from both sides, flagging mismatches where one side has no counterpart.
• HR audits comparing an employees table to a departments table use this pattern to find both employees without valid departments and departments with zero staff, in a single unified report.
• Data migration validation scripts use simulated FULL OUTER JOIN to compare old and new database tables, surfacing rows present in only one of the two systems.
• Financial audits comparing invoiced amounts against received payments use this technique to surface both unpaid invoices and unmatched payments in one combined view.
• Marketing teams comparing a leads table to a converted_customers table use it to see leads that never converted and converted customers with no recorded originating lead.

Common Mistakes to Avoid

• Writing FULL OUTER JOIN directly in MySQL and being surprised by a syntax error, without knowing MySQL lacks native support.
• Using UNION ALL instead of UNION when simulating FULL OUTER JOIN, causing duplicated matched rows in the result.
• Selecting different or mismatched columns in the LEFT JOIN and RIGHT JOIN halves of the simulation, which breaks UNION's ability to align and deduplicate rows correctly.
• Forgetting that this workaround exists and instead writing overly complex application-level code to merge two separate query results manually.

Interview Questions

Q1. Does MySQL support FULL OUTER JOIN natively?

No. MySQL does not implement the FULL OUTER JOIN keyword, unlike PostgreSQL or SQL Server. In MySQL, FULL OUTER JOIN behavior must be simulated using a combination of LEFT JOIN, RIGHT JOIN, and UNION.

Q2. How do you simulate a FULL OUTER JOIN in MySQL?

Write a LEFT JOIN query and a RIGHT JOIN query using the same two tables and identical SELECT columns, then combine them with UNION. UNION removes duplicate rows, ensuring matched rows that appear in both the LEFT and RIGHT JOIN results are not duplicated in the final output.

Q3. Why must UNION be used instead of UNION ALL when simulating FULL OUTER JOIN?

UNION ALL preserves every row from both queries, including exact duplicates. Since genuinely matched rows appear identically in both the LEFT JOIN and RIGHT JOIN results, using UNION ALL would duplicate them. UNION removes these duplicates, correctly reproducing true FULL OUTER JOIN behavior.

Q4. What rows appear in a FULL OUTER JOIN that would not appear in an INNER JOIN?

FULL OUTER JOIN includes unmatched rows from both the left and right tables, NULL-padded on the side with no match, whereas INNER JOIN excludes any row without a match on both sides entirely.

Practice MCQs

1. Which SQL keyword does MySQL lack that other databases like PostgreSQL support?

1. INNER JOIN
2. LEFT JOIN
3. FULL OUTER JOIN
4. CROSS JOIN

Answer: C. FULL OUTER JOIN

Explanation: MySQL does not implement FULL OUTER JOIN as a native keyword; it must be simulated using LEFT JOIN, RIGHT JOIN, and UNION.

2. To simulate FULL OUTER JOIN in MySQL, you combine a LEFT JOIN and a RIGHT JOIN using:

1. INTERSECT
2. UNION
3. EXCEPT
4. CROSS JOIN

Answer: B. UNION

Explanation: UNION combines the two result sets and removes duplicate matched rows, correctly reproducing full outer join behavior.

3. Why not use UNION ALL instead of UNION when simulating FULL OUTER JOIN?

1. UNION ALL is faster and always preferred
2. UNION ALL would duplicate rows that matched in both the LEFT and RIGHT JOIN queries
3. UNION ALL is not valid MySQL syntax
4. UNION ALL only works with INNER JOIN

Answer: B. UNION ALL would duplicate rows that matched in both the LEFT and RIGHT JOIN queries

Explanation: Matched rows appear identically in both the LEFT JOIN and RIGHT JOIN results; UNION removes this duplication while UNION ALL would not.

4. A FULL OUTER JOIN result includes:

1. Only matched rows
2. Only left table rows
3. Only right table rows
4. All rows from both tables, matched or not

Answer: D. All rows from both tables, matched or not

Explanation: FULL OUTER JOIN's defining behavior is including every row from both tables, filling in NULL wherever no match exists on either side.

Quick Revision Points

• MySQL does not support the FULL OUTER JOIN keyword natively; this is a frequently tested, MySQL-specific fact.
• The standard workaround is: (LEFT JOIN query) UNION (RIGHT JOIN query) using identical SELECT column lists.
• UNION removes duplicate rows, correctly avoiding double-counting matched rows; UNION ALL would incorrectly duplicate them.
• FULL OUTER JOIN conceptually equals the combined output of LEFT JOIN and RIGHT JOIN on the same two tables.

Conclusion

• MySQL's lack of native FULL OUTER JOIN support is a well-known limitation every MySQL developer should know.
• The LEFT JOIN + RIGHT JOIN + UNION pattern is the standard, reliable way to reproduce full outer join behavior in MySQL.
• UNION (not UNION ALL) is essential to avoid duplicating rows that matched on both sides.
• This technique is frequently asked in interviews specifically to test MySQL-specific knowledge beyond generic SQL theory.

FULL OUTER JOIN returns all rows from both joined tables, matched where possible and NULL-padded where not, but MySQL does not implement this as a native keyword. The standard workaround combines a LEFT JOIN query and a RIGHT JOIN query on the same tables using UNION, which removes duplicate matched rows and correctly reproduces full outer join behavior. This is a practical, frequently interviewed MySQL-specific technique every developer working with MySQL should know.