How can I do a FULL OUTER JOIN in MySQL?

In MySQL, there is no direct FULL OUTER JOIN keyword (unlike SQL Server or PostgreSQL). However, you can simulate a full outer join using a combination of LEFT JOIN, RIGHT JOIN, and UNION. Below is a detailed explanation with examples.

What is a FULL OUTER JOIN?

A FULL OUTER JOIN returns all records from both tables:

Rows that match in both tables.
Rows from the left table with no match in the right table.
Rows from the right table with no match in the left table.

Simulating FULL OUTER JOIN in MySQL

To achieve this in MySQL, use the following steps:

Perform a LEFT JOIN between the two tables.
Perform a RIGHT JOIN between the two tables.
Combine the results using UNION (which removes duplicates).

Syntax

SELECT * FROM table1
LEFT JOIN table2 ON table1.id = table2.id
UNION
SELECT * FROM table1
RIGHT JOIN table2 ON table1.id = table2.id;

Example

Let’s use two sample tables: employees and departments.

Tables

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT
);

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

INSERT INTO employees VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Carol', NULL); -- No department

INSERT INTO departments VALUES
(101, 'HR'),
(102, 'Engineering'),
(103, 'Marketing'); -- No employees

Step 1: LEFT JOIN

Returns all employees and their departments (including employees with no department):

SELECT employees.id AS emp_id, employees.name AS emp_name,
       departments.id AS dept_id, departments.name AS dept_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

Result:

emp_id	emp_name	dept_id	dept_name
1	Alice	101	HR
2	Bob	102	Engineering
3	Carol	NULL	NULL

Step 2: RIGHT JOIN

Returns all departments and their employees (including departments with no employees):

SELECT employees.id AS emp_id, employees.name AS emp_name,
       departments.id AS dept_id, departments.name AS dept_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

Result:

emp_id	emp_name	dept_id	dept_name
1	Alice	101	HR
2	Bob	102	Engineering
NULL	NULL	103	Marketing

Step 3: Combine with UNION

Merge the LEFT JOIN and RIGHT JOIN results:

SELECT employees.id AS emp_id, employees.name AS emp_name,
       departments.id AS dept_id, departments.name AS dept_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
UNION
SELECT employees.id AS emp_id, employees.name AS emp_name,
       departments.id AS dept_id, departments.name AS dept_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id;

Final Result (FULL OUTER JOIN):

emp_id	emp_name	dept_id	dept_name
1	Alice	101	HR
2	Bob	102	Engineering
3	Carol	NULL	NULL
NULL	NULL	103	Marketing

Handling Duplicates

UNION removes duplicate rows. If you want to keep duplicates (rarely needed for full joins), use UNION ALL.
Ensure the column names and order match in both SELECT statements.

Filtering Non-Matching Rows

To find rows that exist only in the left or right table (no matches), add a WHERE clause:

SELECT employees.id AS emp_id, employees.name AS emp_name,
       departments.id AS dept_id, departments.name AS dept_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NULL
UNION
SELECT employees.id AS emp_id, employees.name AS emp_name,
       departments.id AS dept_id, departments.name AS dept_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.id
WHERE employees.id IS NULL;

Result (Rows with no matches):

emp_id	emp_name	dept_id	dept_name
3	Carol	NULL	NULL
NULL	NULL	103	Marketing

Key Points

Performance: Simulating FULL OUTER JOIN with UNION can be slower for large datasets.
Column Alignment: Ensure both SELECT statements in the UNION have the same number and type of columns.
Alternatives: Use COALESCE() to handle NULL values if needed.

When to Use FULL OUTER JOIN?

Comparing two datasets for mismatches.
Merging data from two tables where either table may have missing entries.

By following this approach, you can effectively replicate FULL OUTER JOIN functionality in MySQL.