FULL OUTER JOIN

-- FULL OUTER JOIN (available in PostgreSQL, SQL Server, and Oracle).
SELECT column FROM table1
FULL OUTER JOIN table2 ON table1.join_column = table2.join_column;

-- MySQL does not support FULL OUTER JOIN, so use UNION as a workaround.
SELECT column FROM table1
LEFT JOIN table2 ON table1.join_column = table2.join_column
UNION
SELECT column FROM table1
RIGHT JOIN table2 ON table1.join_column = table2.join_column;

-- FULL OUTER JOIN example for PostgreSQL / SQL Server / Oracle.
-- Joins employees and departments, including employees with no department
-- and departments with no employees.
SELECT e.name, d.department_name
FROM employees AS e
FULL OUTER JOIN departments AS d ON e.department_id = d.id;

-- MySQL workaround: combine LEFT JOIN and RIGHT JOIN with UNION.
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;

-- Retrieve only rows that exist in just one of the tables (unmatched rows).
-- Example for PostgreSQL.
SELECT e.name, d.department_name
FROM employees AS e
FULL OUTER JOIN departments AS d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;

-- Example result of FULL OUTER JOIN
-- +----------+------------------+
-- | name     | department_name  |
-- +----------+------------------+
-- | Tanaka   | Development      | ← Exists in both tables
-- | Suzuki   | Sales            | ← Exists in both tables
-- | Takahashi| NULL             | ← Employees table only (no department assigned)
-- | NULL     | Legal            | ← Departments table only (no employees)
-- +----------+------------------+

PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN natively.

-- PostgreSQL / SQL Server / Oracle
SELECT e.name, d.department_name
FROM employees AS e
FULL OUTER JOIN departments AS d ON e.department_id = d.id;

MySQL does not support FULL OUTER JOIN. Use a combination of LEFT JOIN and RIGHT JOIN with UNION as a workaround.

-- MySQL workaround
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;

SQLite also does not support FULL OUTER JOIN in versions prior to 3.39.0. For older versions, use the same LEFT JOIN + UNION approach as MySQL. Note that RIGHT JOIN was also added in SQLite 3.39.0, so for versions before that, swap the table order and use two LEFT JOINs combined with UNION.

-- SQLite (before 3.39.0) workaround
SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id
UNION
SELECT e.name, d.department_name
FROM departments AS d
LEFT JOIN employees AS e ON e.department_id = d.id;

FULL OUTER JOIN retains all rows from both tables. It is commonly used to verify data integrity (detecting orphaned records) or to find differences between two datasets.

MySQL does not support FULL OUTER JOIN. To achieve the equivalent result in MySQL, combine LEFT JOIN and RIGHT JOIN using UNION (removes duplicates) or UNION ALL (keeps duplicates). If you use UNION ALL, remove duplicates manually or switch to UNION.

Columns with no matching row in the other table will be NULL. Combine with IS NULL / IS NOT NULL to identify rows that exist in only one of the tables. See also INNER JOIN and LEFT JOIN / RIGHT JOIN for related join types.