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

FULL OUTER JOIN: Keep Everything From Both Sides

A FULL OUTER JOIN returns all rows from both tables — matched rows joined together, plus unmatched rows from each side padded with NULL. It's the union of LEFT and RIGHT joins.

Join	Keeps unmatched from
INNER	neither (only matches)
LEFT	left table
RIGHT	right table
FULL OUTER	both

-- reconcile two systems: rows only in A, only in B, and in both
SELECT COALESCE(a.id, b.id) AS id, a.amount AS sys_a, b.amount AS sys_b
FROM system_a a
FULL OUTER JOIN system_b b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;   -- show only the mismatches

Prime use case: reconciliation — comparing two datasets to find what's in one but not the other (missing invoices, sync gaps). The WHERE a.id IS NULL OR b.id IS NULL filter surfaces exactly the discrepancies.

Portability gotcha: MySQL doesn't support FULL OUTER JOIN. Emulate it by UNION-ing a LEFT JOIN with a RIGHT JOIN: SELECT ... LEFT JOIN ... UNION SELECT ... RIGHT JOIN .... PostgreSQL, SQL Server, and Oracle support it directly. Because it keeps unmatched rows from both sides, always use COALESCE on the key column to get a non-NULL identifier.

🏋️ Practical Exercise

Write a FULL OUTER JOIN.
Identify rows unmatched on both sides.
Emulate FULL OUTER in MySQL with UNION.
Combine it with COALESCE.
Compare the result to an INNER JOIN.

🔥 Challenge Exercise

Given online_customers and store_customers, use a FULL OUTER JOIN to list everyone in either set, marking which source each came from. Explain how FULL OUTER differs from INNER and LEFT, and how to emulate it where unsupported.

📋 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.

Interview Questions

What does FULL OUTER JOIN return?
How does FULL OUTER differ from LEFT and INNER?
How do you emulate FULL OUTER JOIN in MySQL?
What appears for rows unmatched on one side?
When is FULL OUTER JOIN useful?

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.