MySQL - Full Join

MySQL - Emulating a FULL OUTER JOIN

A FULL OUTER JOIN combines the results of both a LEFT JOIN and a RIGHT JOIN. The resulting set contains all records from both tables. For rows where there is a match in the join condition, the columns from both tables are combined. For rows where there is no match, the columns of the missing table are filled with NULL.

Emulating FULL JOIN in MySQL

Unlike some other SQL databases like PostgreSQL or SQL Server, MySQL does not have a built-in FULL OUTER JOIN keyword. However, this operation can be perfectly emulated by combining a LEFT JOIN and a RIGHT JOIN with the UNION operator.

Syntax

The standard pattern to emulate a FULL OUTER JOIN is:

SELECT ...
FROM table1
LEFT JOIN table2 ON table1.common_field = table2.common_field

UNION

SELECT ...
FROM table1
RIGHT JOIN table2 ON table1.common_field = table2.common_field;

The UNION operator combines the result sets of the two SELECT statements and removes duplicate rows. This is important because rows that match in both tables would otherwise appear twice (once from the LEFT JOIN and once from the RIGHT JOIN). If you want to keep all rows, including duplicates, you can use UNION ALL, but this is generally not the desired behavior for a FULL JOIN emulation.

Example: Combining Customers and Orders

Let’s consider two tables: Customers (listing all customers) and Orders (listing all orders). Some customers may not have placed any orders, and (hypothetically) some orders might not be linked to a customer.

First, create and populate the Customers table:

CREATE TABLE Customers (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO Customers (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

Next, create and populate the Orders table. Note that Bob (customer ID 2) has an order, Charlie (ID 3) does not, and order 103 has an invalid customer ID.

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2)
);

INSERT INTO Orders (order_id, customer_id, amount) VALUES
(101, 1, 50.00),
(102, 1, 75.00),
(103, 99, 200.00); -- Orphaned order

Now, let’s perform the emulated FULL OUTER JOIN to see all customers and all orders.

SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id

UNION

SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
RIGHT JOIN Orders o ON c.id = o.customer_id;

Output

The result shows all customers and all orders. Charlie has no order data (NULL), and the orphaned order has no customer data (NULL).

id	name	order_id	amount
1	Alice	101	50.00
1	Alice	102	75.00
2	Bob	NULL	NULL
3	Charlie	NULL	NULL
NULL	NULL	103	200.00

Filtering the Results of a FULL JOIN

When filtering the results of an outer join, be careful where you place your conditions. Placing a filter on the ‘outer’ table in the WHERE clause can unintentionally convert the query into an INNER JOIN.

Common Pitfall

For example, if you want to see all customers and their orders, but only for orders with an amount greater than 60.00, this query is incorrect:

-- INCORRECT: This will behave like an INNER JOIN
SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id
WHERE o.amount > 60.00;

The WHERE o.amount > 60.00 condition filters out any rows where o.amount is NULL, effectively removing all customers without matching orders.

Correct Method

To filter correctly, the condition should be part of the ON clause, or applied after the join using a subquery or Common Table Expression (CTE).

-- CORRECT: Move the filter to the ON clause
SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id AND o.amount > 60.00

UNION

SELECT c.id, c.name, o.order_id, o.amount
FROM Customers c
RIGHT JOIN Orders o ON c.id = o.customer_id AND o.amount > 60.00;

Output

This correctly returns all customers, showing only the relevant orders for Alice and NULL for the others.

id	name	order_id	amount
1	Alice	102	75.00
2	Bob	NULL	NULL
3	Charlie	NULL	NULL
NULL	NULL	103	200.00