Emulating FULL OUTER JOIN in MySQL0%

Emulating FULL OUTER JOIN in MySQL

Intermediate12 min read•Updated: 2026-09-12

Emulating FULL OUTER JOIN in MySQL: The UNION Technique

A FULL OUTER JOIN returns all rows from Table A and all rows from Table B. If there is a match, columns from both tables are displayed. If a row in Table A has no match in Table B, Table B's columns are filled with NULL. If a row in Table B has no match in Table A, Table A's columns are filled with NULL.

While databases like PostgreSQL, Oracle, and Microsoft SQL Server natively support the FULL OUTER JOIN syntax, MySQL does NOT natively support FULL OUTER JOIN. In this tutorial, you will master the industry-standard technique to emulate it in MySQL using UNION.

1. The Mathematical Concept of FULL OUTER JOIN

 Table A (Employees)             Table B (Departments)
 +----+----------+---------+     +---------+-------------------+
 | id | name     | dept_id |     | dept_id | dept_name         |
 +----+----------+---------+     +---------+-------------------+
 |  1 | Aarav    |      10 |     |      10 | Engineering       |
 |  2 | Priya    |      20 |     |      20 | Marketing         |
 |  3 | Rohan    |    NULL |     |      30 | Research (Empty!) |
 +----+----------+---------+     +---------+-------------------+
9 
 FULL OUTER JOIN Result:
 +----------+-------------------+
 | name     | dept_name         |
 +----------+-------------------+
 | Aarav    | Engineering       | <-- Matched in both
 | Priya    | Marketing         | <-- Matched in both
 | Rohan    | NULL              | <-- In Employee, NOT in Department!
 | NULL     | Research          | <-- In Department, NOT in Employee!
 +----------+-------------------+

2. Emulating FULL OUTER JOIN in MySQL

Because writing FULL OUTER JOIN triggers syntax error 1064, MySQL engineers emulate it by combining a LEFT JOIN and an anti-matching RIGHT JOIN using UNION:

1-- Step 1: All rows from LEFT table (plus matched right rows)
2SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
3FROM employees e
4LEFT JOIN departments d ON e.dept_id = d.department_id
5 
6UNION
7 
8-- Step 2: All rows from RIGHT table (plus matched left rows)
9SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
10FROM employees e
11RIGHT JOIN departments d ON e.dept_id = d.department_id;

3. Why `UNION` (and NOT `UNION ALL`) is Mandatory Here

Notice that:

The LEFT JOIN in Step 1 returns the matched rows (Aarav in Engineering, Priya in Marketing).
The RIGHT JOIN in Step 2 also returns those exact same matched rows!
If you use UNION ALL, those matching rows will appear twice (duplicates) in your output grid!
Using UNION (without ALL) causes MySQL to run an automatic deduplication pass, preserving unique records and yielding a mathematically perfect FULL OUTER JOIN!

4. The High-Performance Optimization (UNION ALL + Anti-Join)

While standard UNION works, deduplicating millions of rows in memory can be slow. A seasoned database engineer writes the emulation using UNION ALL paired with an Anti-Join:

1-- Step 1: All records from LEFT table:
2SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
3FROM employees e
4LEFT JOIN departments d ON e.dept_id = d.department_id
5 
6UNION ALL
7 
8-- Step 2: ONLY unmatched records from the RIGHT table (Avoids duplicate overlap!):
9SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
10FROM employees e
11RIGHT JOIN departments d ON e.dept_id = d.department_id
12WHERE e.dept_id IS NULL; -- Anti-join condition!

Why this is faster:

By explicitly filtering for WHERE e.dept_id IS NULL in Step 2, there is zero overlap between Step 1 and Step 2. This allows using UNION ALL, which bypasses the expensive temporary table sorting and deduplication step!

5. Best Practices & Common Pitfalls

Column Lists Must Match: Both SELECT queries joined by UNION must have the exact same number of columns in the exact same order with compatible data types.
When is a FULL JOIN Needed? Common use cases include reconciling two financial ledgers (finding transactions present in the bank statement but missing from accounting software, and vice versa).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Does MySQL natively support the FULL OUTER JOIN keyword syntax?

Question 2

How is a FULL OUTER JOIN emulated in standard MySQL?

Question 3

Why does standard emulation require UNION instead of UNION ALL if the second query is not an anti-join?

Question 4

How can the FULL OUTER JOIN emulation be optimized to safely use UNION ALL without producing duplicates?

Question 5

What is a primary real-world use case for a FULL OUTER JOIN?

Next Lesson

CROSS JOIN & Cartesian Products

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
RIGHT JOIN (Right Outer Join)	CROSS JOIN & Cartesian Products

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

CROSS JOIN & Cartesian Products

Emulating FULL OUTER JOIN in MySQL

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Emulating FULL OUTER JOIN in MySQL: The UNION Technique

1. The Mathematical Concept of FULL OUTER JOIN

 Table A (Employees)             Table B (Departments)
 +----+----------+---------+     +---------+-------------------+
 | id | name     | dept_id |     | dept_id | dept_name         |
 +----+----------+---------+     +---------+-------------------+
 |  1 | Aarav    |      10 |     |      10 | Engineering       |
 |  2 | Priya    |      20 |     |      20 | Marketing         |
 |  3 | Rohan    |    NULL |     |      30 | Research (Empty!) |
 +----+----------+---------+     +---------+-------------------+
9 
 FULL OUTER JOIN Result:
 +----------+-------------------+
 | name     | dept_name         |
 +----------+-------------------+
 | Aarav    | Engineering       | <-- Matched in both
 | Priya    | Marketing         | <-- Matched in both
 | Rohan    | NULL              | <-- In Employee, NOT in Department!
 | NULL     | Research          | <-- In Department, NOT in Employee!
 +----------+-------------------+

2. Emulating FULL OUTER JOIN in MySQL

Because writing FULL OUTER JOIN triggers syntax error 1064, MySQL engineers emulate it by combining a LEFT JOIN and an anti-matching RIGHT JOIN using UNION:

1-- Step 1: All rows from LEFT table (plus matched right rows)
2SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
3FROM employees e
4LEFT JOIN departments d ON e.dept_id = d.department_id
5 
6UNION
7 
8-- Step 2: All rows from RIGHT table (plus matched left rows)
9SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
10FROM employees e
11RIGHT JOIN departments d ON e.dept_id = d.department_id;

3. Why `UNION` (and NOT `UNION ALL`) is Mandatory Here

Notice that:

The LEFT JOIN in Step 1 returns the matched rows (Aarav in Engineering, Priya in Marketing).
The RIGHT JOIN in Step 2 also returns those exact same matched rows!
If you use UNION ALL, those matching rows will appear twice (duplicates) in your output grid!
Using UNION (without ALL) causes MySQL to run an automatic deduplication pass, preserving unique records and yielding a mathematically perfect FULL OUTER JOIN!

4. The High-Performance Optimization (UNION ALL + Anti-Join)

While standard UNION works, deduplicating millions of rows in memory can be slow. A seasoned database engineer writes the emulation using UNION ALL paired with an Anti-Join:

1-- Step 1: All records from LEFT table:
2SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
3FROM employees e
4LEFT JOIN departments d ON e.dept_id = d.department_id
5 
6UNION ALL
7 
8-- Step 2: ONLY unmatched records from the RIGHT table (Avoids duplicate overlap!):
9SELECT e.employee_id, e.full_name, d.department_id, d.dept_name
10FROM employees e
11RIGHT JOIN departments d ON e.dept_id = d.department_id
12WHERE e.dept_id IS NULL; -- Anti-join condition!

Why this is faster:

5. Best Practices & Common Pitfalls

Column Lists Must Match: Both SELECT queries joined by UNION must have the exact same number of columns in the exact same order with compatible data types.
When is a FULL JOIN Needed? Common use cases include reconciling two financial ledgers (finding transactions present in the bank statement but missing from accounting software, and vice versa).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Does MySQL natively support the FULL OUTER JOIN keyword syntax?

Question 2

How is a FULL OUTER JOIN emulated in standard MySQL?

Question 3

Why does standard emulation require UNION instead of UNION ALL if the second query is not an anti-join?

Question 4

How can the FULL OUTER JOIN emulation be optimized to safely use UNION ALL without producing duplicates?

Question 5

What is a primary real-world use case for a FULL OUTER JOIN?

Next Lesson

CROSS JOIN & Cartesian Products

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
RIGHT JOIN (Right Outer Join)	CROSS JOIN & Cartesian Products

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Emulating FULL OUTER JOIN in MySQL: The UNION Technique

1. The Mathematical Concept of FULL OUTER JOIN

2. Emulating FULL OUTER JOIN in MySQL

3. Why UNION (and NOT UNION ALL) is Mandatory Here

4. The High-Performance Optimization (UNION ALL + Anti-Join)

Why this is faster:

5. Best Practices & Common Pitfalls

Multiple Choice Questions

CROSS JOIN & Cartesian Products

Related Lessons

Practice Quiz

Loading Page Content...

Emulating FULL OUTER JOIN in MySQL: The UNION Technique

1. The Mathematical Concept of FULL OUTER JOIN

2. Emulating FULL OUTER JOIN in MySQL

3. Why UNION (and NOT UNION ALL) is Mandatory Here

4. The High-Performance Optimization (UNION ALL + Anti-Join)

Why this is faster:

5. Best Practices & Common Pitfalls

Multiple Choice Questions

CROSS JOIN & Cartesian Products

Related Lessons

Practice Quiz

3. Why `UNION` (and NOT `UNION ALL`) is Mandatory Here

3. Why `UNION` (and NOT `UNION ALL`) is Mandatory Here