SQL FULL OUTER JOIN – All Rows from Both Tables

FULL OUTER JOIN Syntax

SQL

SELECT columns
FROM table_a
FULL OUTER JOIN table_b ON table_a.col = table_b.col;

-- OUTER keyword is optional (same result)
SELECT columns
FROM table_a
FULL JOIN table_b ON table_a.col = table_b.col;

SQL

-- All employees AND all departments — matched where possible
SELECT e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- Result (PostgreSQL / SQL Server):
-- name  | dept_name
-- Alice | Marketing
-- Bob   | Engineering
-- Carol | Engineering
-- Dave  | HR
-- Eve   | NULL          ← Employee with no department
-- NULL  | Finance       ← Department with no employees

-- All 5 employees + Finance department = 6 rows total

NULLs on Both Sides

In a FULL OUTER JOIN result, NULLs can appear in columns from either the left table or the right table. Use IS NULL checks on each side to categorize the rows.

SQL

SELECT
  e.id       AS emp_id,
  e.name     AS emp_name,
  d.id       AS dept_id,
  d.dept_name,
  CASE
    WHEN e.id IS NULL  THEN 'Department with no employees'
    WHEN d.id IS NULL  THEN 'Employee with no department'
    ELSE 'Matched'
  END AS match_status
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- Result breakdown:
-- match_status                    count
-- Matched                           4   (Alice, Bob, Carol, Dave)
-- Employee with no department       1   (Eve)
-- Department with no employees      1   (Finance)

MySQL Workaround

MySQL does not support FULL OUTER JOIN. The standard workaround is to UNION a LEFT JOIN with a RIGHT JOIN (or LEFT JOIN with tables swapped), using IS NULL to avoid duplicating matched rows.

SQL

-- MySQL FULL OUTER JOIN emulation using UNION
-- Step 1: LEFT JOIN gets all employees + matched departments
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id

UNION

-- Step 2: RIGHT JOIN (or swapped LEFT JOIN) gets all departments
-- Use IS NULL to exclude already-matched rows (avoid duplicates)
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.id IS NULL;

-- Alternative: swap tables in the second LEFT JOIN
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id

UNION

SELECT e.name, d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;

ℹ️

UNION vs UNION ALL

The workaround uses UNION (not UNION ALL) so that exactly-matched rows from both halves are not duplicated. However, since the second half explicitly filters with WHERE e.id IS NULL, using UNION ALL is also correct and faster (skips the deduplication step).

Use Cases for FULL OUTER JOIN

SQL

-- Data reconciliation: compare two monthly reports
-- Find orders that appear in one month but not the other
SELECT
  COALESCE(jan.order_id, feb.order_id) AS order_id,
  jan.amount                           AS jan_amount,
  feb.amount                           AS feb_amount,
  CASE
    WHEN jan.order_id IS NULL THEN 'New in Feb'
    WHEN feb.order_id IS NULL THEN 'Only in Jan'
    ELSE 'Both months'
  END AS status
FROM january_orders jan
FULL OUTER JOIN february_orders feb ON jan.order_id = feb.order_id;

-- Merge two user tables from different systems
SELECT
  COALESCE(a.email, b.email) AS email,
  a.name AS system_a_name,
  b.name AS system_b_name
FROM system_a_users a
FULL OUTER JOIN system_b_users b ON a.email = b.email;

-- Find discrepancies: rows only in one table
SELECT *
FROM system_a_users a
FULL OUTER JOIN system_b_users b ON a.email = b.email
WHERE a.email IS NULL OR b.email IS NULL;

Summary

📋 Summary

FULL OUTER JOIN — Returns all rows from both tables. NULLs on either side for unmatched rows.
Combines LEFT + RIGHT — Eve (no dept) and Finance (no employees) both appear.
MySQL limitation — Not supported. Emulate with LEFT JOIN UNION RIGHT JOIN (+ IS NULL filter).
PostgreSQL / SQL Server / Oracle — FULL OUTER JOIN is natively supported.
Use CASE + IS NULL — Categorize rows as matched, left-only, or right-only.
Best use case — Data reconciliation, finding mismatches between two datasets.

FAQ

Which databases support FULL OUTER JOIN natively?+

PostgreSQL, SQL Server, Oracle, DB2, SQLite (version 3.39+), and most enterprise databases support FULL OUTER JOIN natively. MySQL and MariaDB do not — use the LEFT JOIN UNION RIGHT JOIN workaround shown in this lesson.

How do I avoid duplicates in the MySQL FULL OUTER JOIN workaround?+

Matched rows appear in both the LEFT JOIN half and the RIGHT JOIN half. To avoid duplicates in the second half, add WHERE right_table.id IS NULL (or whichever side's key is not the driving table). This ensures only the unmatched rows from the right table are included in the UNION. Alternatively, use UNION (not UNION ALL) to deduplicate automatically, though this is slower.

Is FULL OUTER JOIN commonly used in practice?+

FULL OUTER JOIN is less common than INNER JOIN or LEFT JOIN in day-to-day application queries. It is most useful in data engineering tasks: reconciling records between two datasets, ETL (extract-transform-load) processes, auditing data migrations, and comparing staging vs production tables. If you find yourself using FULL OUTER JOIN in a regular application query, reconsider whether your data model needs adjustment.

Can FULL OUTER JOIN be used with GROUP BY?+

Yes. After a FULL OUTER JOIN, you can GROUP BY and aggregate as normal. Use COALESCE on the key columns before grouping if you want to merge NULLs from both sides: GROUP BY COALESCE(a.id, b.id). Be careful about which columns you aggregate — NULL columns from unmatched rows are ignored by SUM/AVG/MIN/MAX.