SQL Full Join

The FULL JOIN (or FULL OUTER JOIN) combines the behaviors of both a LEFT JOIN and a RIGHT JOIN. It returns all records from both tables, matching them where possible.

If there is a match, the rows are linked. If there is no match, the missing fields from either the left or right table are filled with NULL values. It represents a complete merge of two datasets.

Basic Syntax Blueprint

SELECT t1.column1, t2.column2
FROM table1 AS t1
FULL OUTER JOIN table2 AS t2 ON t1.common_column = t2.common_column;

Note: The word OUTER is optional. Writing FULL JOIN or FULL OUTER JOIN executes the exact same way.

A Real-World Walkthrough

Let's use our standard employees and departments tables to see how a FULL JOIN displays all unmatched data fields at once.

employees Table (Left)

departments Table (Right)

The Full Join Query:

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

The Output Result:

What happened here?

• Rahul and Priya are matched normally because their IDs exist in both tables.
• Amit is included even though his department ID is missing (the right-side fields are set to NULL).
• Finance is included even though no employee works there (the left-side fields are set to NULL).

⚠️ Critical Compatibility Warning: MySQL

MySQL does not support FULL JOIN directly. If you try to execute a FULL JOIN query in MySQL, it will throw a syntax error.

The MySQL Workaround (UNION Check)

To mimic a FULL JOIN in MySQL, you must run a LEFT JOIN and a RIGHT JOIN separately, and then stitch them together using the UNION keyword (which automatically filters out the duplicate matched rows).

-- MySQL Full Join Simulation
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.department_id

UNION

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

Real-World Use Case: Data Auditing

FULL JOIN is highly useful for spotting data discrepancies or tracking missing linkages across two disjointed inventory or logging systems.

Example: Find mismatched or missing links between accounts

SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
FULL JOIN orders AS o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL OR o.customer_id IS NULL;
-- This returns users with no orders AND orders with missing user profiles