How to Use FULL OUTER JOIN in MySQL

Key Insights

MySQL lacks native FULL OUTER JOIN support, but you can emulate it by combining LEFT JOIN and RIGHT JOIN results with UNION
Use UNION ALL with a WHERE clause exclusion for better performance on large datasets instead of plain UNION
FULL OUTER JOIN emulation is essential for data reconciliation, finding mismatches between tables, and merging datasets from disparate sources

What Is a FULL OUTER JOIN?

A FULL OUTER JOIN combines two tables and returns all rows from both sides, matching them where possible and filling in NULL values where no match exists. Unlike an INNER JOIN that only returns matching rows, or LEFT/RIGHT JOINs that preserve one side, a FULL OUTER JOIN preserves everything.

This operation is invaluable when you need to see the complete picture of two related datasets—identifying what matches, what exists only in the first table, and what exists only in the second.

Here’s the conceptual result of a FULL OUTER JOIN between tables A and B:

Rows that match in both A and B (with data from both)
Rows in A with no match in B (B columns are NULL)
Rows in B with no match in A (A columns are NULL)

The Problem: MySQL Doesn’t Support FULL OUTER JOIN

If you’ve worked with PostgreSQL, SQL Server, or Oracle, you’re probably used to writing queries like this:

SELECT *
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Try this in MySQL and you’ll get a syntax error. MySQL simply doesn’t implement the FULL OUTER JOIN syntax. This has been a known limitation for years, and while MariaDB (MySQL’s fork) added support in version 10.6, standard MySQL still lacks it as of version 8.0.

This isn’t a theoretical inconvenience. You’ll hit this wall the moment you need to:

Compare two datasets to find discrepancies
Merge data from different sources while preserving unmatched records
Generate reports showing both matched and unmatched items from two tables

Fortunately, there’s a reliable workaround.

The Solution: UNION with LEFT and RIGHT JOINs

The standard technique for emulating a FULL OUTER JOIN in MySQL combines a LEFT JOIN and a RIGHT JOIN using UNION. The logic is straightforward:

A LEFT JOIN returns all rows from the left table plus matches from the right
A RIGHT JOIN returns all rows from the right table plus matches from the left
UNION combines these results and removes duplicates

Here’s the basic pattern:

SELECT *
FROM table_a a
LEFT JOIN table_b b ON a.key = b.key

UNION

SELECT *
FROM table_a a
RIGHT JOIN table_b b ON a.key = b.key;

This gives you the exact same result as a native FULL OUTER JOIN would. The LEFT JOIN captures all rows from table_a (including those without matches in table_b), and the RIGHT JOIN captures all rows from table_b (including those without matches in table_a). The UNION merges them and eliminates the duplicate matched rows that appear in both result sets.

Practical Example with Sample Data

Let’s work through a concrete example. Imagine you’re managing a company where employees should be assigned to departments, but your data has some inconsistencies—some employees have no department, and some departments have no employees.

First, create the tables and insert sample data:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT
);

INSERT INTO departments (id, name) VALUES
(1, 'Engineering'),
(2, 'Marketing'),
(3, 'Finance'),
(4, 'Research');  -- No employees assigned

INSERT INTO employees (id, name, department_id) VALUES
(101, 'Alice Chen', 1),
(102, 'Bob Smith', 1),
(103, 'Carol White', 2),
(104, 'David Brown', NULL),  -- No department
(105, 'Eve Johnson', 5);      -- Invalid department_id

Now run the FULL OUTER JOIN emulation:

SELECT 
    e.id AS employee_id,
    e.name AS employee_name,
    d.id AS department_id,
    d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id

UNION

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

The result shows the complete picture:

+-------------+---------------+---------------+-----------------+
| employee_id | employee_name | department_id | department_name |
+-------------+---------------+---------------+-----------------+
|         101 | Alice Chen    |             1 | Engineering     |
|         102 | Bob Smith     |             1 | Engineering     |
|         103 | Carol White   |             2 | Marketing       |
|         104 | David Brown   |          NULL | NULL            |
|         105 | Eve Johnson   |          NULL | NULL            |
|        NULL | NULL          |             3 | Finance         |
|        NULL | NULL          |             4 | Research        |
+-------------+---------------+---------------+-----------------+

You can immediately see:

Alice, Bob, and Carol are properly assigned to existing departments
David has no department assignment (NULL department_id)
Eve references department 5, which doesn’t exist (orphaned reference)
Finance and Research departments have no employees

Handling Duplicates: UNION vs UNION ALL

The basic UNION approach works correctly, but it has a performance cost. UNION performs an implicit DISTINCT operation, which requires sorting and comparing all rows to remove duplicates. On large tables, this becomes expensive.

A more efficient approach uses UNION ALL (which keeps all rows) combined with a WHERE clause that explicitly excludes the duplicate matched rows from the second query:

SELECT 
    e.id AS employee_id,
    e.name AS employee_name,
    d.id AS department_id,
    d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id

UNION ALL

SELECT 
    e.id AS employee_id,
    e.name AS employee_name,
    d.id AS department_id,
    d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;  -- Only include rows with no match in employees

The key difference is the WHERE e.id IS NULL clause in the second query. This ensures we only include rows from the RIGHT JOIN that have no corresponding employee—exactly the rows that wouldn’t have appeared in the LEFT JOIN. Since we’re no longer including duplicates, UNION ALL safely combines the results without the deduplication overhead.

For small tables, the difference is negligible. For tables with millions of rows, this optimization can reduce query time significantly. Always benchmark with your actual data volumes.

Common Use Cases

Data Reconciliation Between Systems

When migrating data or syncing between systems, you need to identify records that exist in one system but not the other:

-- Compare products between old and new inventory systems
SELECT 
    old.sku AS old_sku,
    old.name AS old_name,
    old.quantity AS old_qty,
    new.sku AS new_sku,
    new.name AS new_name,
    new.quantity AS new_qty,
    CASE 
        WHEN old.sku IS NULL THEN 'New product'
        WHEN new.sku IS NULL THEN 'Discontinued'
        WHEN old.quantity != new.quantity THEN 'Quantity mismatch'
        ELSE 'Matched'
    END AS status
FROM old_inventory old
LEFT JOIN new_inventory new ON old.sku = new.sku

UNION ALL

SELECT 
    old.sku,
    old.name,
    old.quantity,
    new.sku,
    new.name,
    new.quantity,
    'New product' AS status
FROM old_inventory old
RIGHT JOIN new_inventory new ON old.sku = new.sku
WHERE old.sku IS NULL;

Finding Orphaned Records

Identify data integrity issues where foreign key relationships are broken:

-- Find orders with invalid customer references and customers with no orders
SELECT 
    c.id AS customer_id,
    c.email,
    o.id AS order_id,
    o.total,
    CASE 
        WHEN c.id IS NULL THEN 'Orphaned order'
        WHEN o.id IS NULL THEN 'Customer with no orders'
        ELSE 'Valid'
    END AS status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id

UNION ALL

SELECT 
    c.id,
    c.email,
    o.id,
    o.total,
    'Orphaned order' AS status
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL;

Merging Datasets for Reporting

Combine data from different sources while preserving all records:

-- Merge online and in-store sales for complete customer view
SELECT 
    COALESCE(online.customer_email, store.customer_email) AS email,
    online.total_spent AS online_spent,
    store.total_spent AS store_spent,
    COALESCE(online.total_spent, 0) + COALESCE(store.total_spent, 0) AS total_spent
FROM online_sales online
LEFT JOIN store_sales store ON online.customer_email = store.customer_email

UNION ALL

SELECT 
    store.customer_email,
    NULL AS online_spent,
    store.total_spent,
    store.total_spent AS total_spent
FROM online_sales online
RIGHT JOIN store_sales store ON online.customer_email = store.customer_email
WHERE online.customer_email IS NULL;

Summary

MySQL’s lack of native FULL OUTER JOIN support is an inconvenience, not a blocker. The UNION technique combining LEFT JOIN and RIGHT JOIN results gives you identical functionality. Use plain UNION for simplicity on smaller datasets, and switch to UNION ALL with a WHERE exclusion clause when performance matters on larger tables.

Keep these points in mind:

Always test with representative data volumes before deploying to production
Index your join columns for better performance
Consider whether you actually need a FULL OUTER JOIN—often a LEFT JOIN or INNER JOIN is sufficient

This workaround has been the standard approach for years and remains reliable. While future MySQL versions might add native support, the UNION technique will continue to work regardless of version changes.