SQL - Full Join

SQL - The FULL OUTER JOIN

Understanding the FULL OUTER JOIN

The FULL OUTER JOIN, often written as FULL JOIN, combines the results of both a LEFT JOIN and a RIGHT JOIN. The resulting joined table will contain all records from both tables. For rows that have a match in the other table, the columns will be merged. For rows that do not have a match, the columns from the other table will be filled with NULL values.

Think of it as a way to see a complete, unified list from two tables, highlighting both the matching and non-matching records between them.

A FULL OUTER JOIN is particularly useful for data reconciliation tasks, where you need to find entities that exist in one system (table) but are missing from another.

Syntax and Example

Let’s consider two tables: employees who are assigned to projects, and projects which may or may not have an employee assigned.

employees table:

id | name
---+----------
1  | Alice
2  | Bob
3  | Charlie  -- Unassigned

projects table:

id | project_name  | employee_id
---+---------------+-------------
101| Project Alpha | 1
102| Project Beta  | 2
103| Project Gamma | NULL -- No employee assigned

Syntax

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
FULL OUTER JOIN projects AS p ON e.id = p.employee_id;

Output

The result shows all employees and all projects.

employee_name | project_name
---------------+----------------
Alice         | Project Alpha
Bob           | Project Beta
Charlie       | NULL             -- Employee with no project
NULL          | Project Gamma    -- Project with no employee

Emulating a FULL JOIN in MySQL

MySQL does not support the FULL OUTER JOIN syntax directly. However, you can achieve the exact same result by combining a LEFT JOIN and a RIGHT JOIN with the UNION operator.

Syntax (MySQL Emulation)

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
LEFT JOIN projects AS p ON e.id = p.employee_id

UNION

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
RIGHT JOIN projects AS p ON e.id = p.employee_id;

The LEFT JOIN gets all employees and their projects. The RIGHT JOIN gets all projects and their employees. UNION combines these two result sets and automatically removes the duplicate rows (the ones that matched in both joins), producing the same output as a true FULL OUTER JOIN.

Joining Multiple Tables

You can chain FULL JOINs to combine more than two tables. The logic extends sequentially: the result of the first join becomes the left side of the next join.

SELECT ...
FROM tableA
FULL JOIN tableB ON tableA.id = tableB.a_id
FULL JOIN tableC ON tableB.id = tableC.b_id;

Be cautious when joining multiple tables this way. The logic can become complex, and the number of NULL values can grow, making the result set difficult to interpret.

Using FULL JOIN with a WHERE Clause

You can use a WHERE clause to filter the results of a FULL OUTER JOIN. This is powerful for finding records that exist in one table but not the other.

Example: Find Employees Without a Project

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
FULL OUTER JOIN projects AS p ON e.id = p.employee_id
WHERE p.id IS NULL;

Output

employee_name | project_name
---------------+----------------
Charlie       | NULL

Example: Find Projects Without an Employee

SELECT
    e.name AS employee_name,
    p.project_name
FROM employees AS e
FULL OUTER JOIN projects AS p ON e.id = p.employee_id
WHERE e.id IS NULL;

Output

employee_name | project_name
---------------+----------------
NULL          | Project Gamma