FULL OUTER JOIN

Introduction

You've learned INNER JOIN (only matches), LEFT JOIN (all from left), and RIGHT JOIN (all from right). But what if you want all rows from both tables, regardless of whether they match? That's where FULL OUTER JOIN comes in!

FULL OUTER JOIN is less common than other JOINs, but invaluable for finding discrepancies, comparing datasets, and performing data reconciliation.

What is FULL OUTER JOIN?

FULL OUTER JOIN (or FULL JOIN) returns:

• All rows from the left table
• All rows from the right table
• Matched rows shown together
• Unmatched rows shown with NULL for missing side

It's essentially LEFT JOIN + RIGHT JOIN combined!

Syntax

SELECT columns
FROM left_table
FULL OUTER JOIN right_table ON left_table.column = right_table.column;

"FULL OUTER JOIN" and "FULL JOIN" are identical.

Example Setup

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INTEGER
);

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- Insert employees
INSERT INTO employees (id, name, department_id) VALUES
    (1, 'Alice', 10),
    (2, 'Bob', 20),
    (3, 'Carol', 30),
    (4, 'Dave', NULL);  -- No department

-- Insert departments
INSERT INTO departments (id, name) VALUES
    (10, 'Sales'),
    (20, 'Engineering'),
    (40, 'Marketing');  -- No employees

FULL OUTER JOIN Example

SELECT
    e.name AS employee,
    d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Result:

 employee  | department
-----------+-------------
 Alice     | Sales          ← Matched
 Bob       | Engineering    ← Matched
 Carol     | NULL           ← Employee without department (dept_id 30 doesn't exist)
 Dave      | NULL           ← Employee with NULL department
 NULL      | Marketing      ← Department without employees

Breakdown:

• Alice & Bob: Matched (employee + department)
• Carol: No matching department (department ID 30 doesn't exist)
• Dave: NULL department_id
• Marketing: No employees assigned

Visualizing FULL OUTER JOIN

Employees               Departments
┌────┬───────┬────┐     ┌────┬─────────────┐
│ id │ name  │dept│     │ id │    name     │
├────┼───────┼────┤     ├────┼─────────────┤
│ 1  │ Alice │ 10 │ ←─→ │ 10 │ Sales       │ Matched
│ 2  │ Bob   │ 20 │ ←─→ │ 20 │ Engineering │ Matched
│ 3  │ Carol │ 30 │  X  │ 40 │ Marketing   │ ← No employees
│ 4  │ Dave  │NULL│           └─────────────┘
└────┴───────┴────┘
   ↓                          ↓
ALL included              ALL included

Comparing with Other JOINs

INNER JOIN

SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Result:

 employee | department
----------+-------------
 Alice    | Sales
 Bob      | Engineering

Only matches (2 rows).

LEFT JOIN

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Result:

 employee | department
----------+-------------
 Alice    | Sales
 Bob      | Engineering
 Carol    | NULL
 Dave     | NULL

All employees + matches (4 rows).

RIGHT JOIN

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

Result:

 employee | department
----------+-------------
 Alice    | Sales
 Bob      | Engineering
 NULL     | Marketing

All departments + matches (3 rows).

FULL OUTER JOIN

SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Result:

 employee | department
----------+-------------
 Alice    | Sales
 Bob      | Engineering
 Carol    | NULL
 Dave     | NULL
 NULL     | Marketing

Everything from both sides (5 rows).

Use Cases for FULL OUTER JOIN

Use Case 1: Data Reconciliation

Compare two systems to find discrepancies:

-- System A: Expected inventory
CREATE TABLE expected_inventory (
    product_id INTEGER,
    quantity INTEGER
);

-- System B: Actual inventory
CREATE TABLE actual_inventory (
    product_id INTEGER,
    quantity INTEGER
);

-- Find discrepancies
SELECT
    COALESCE(e.product_id, a.product_id) AS product_id,
    e.quantity AS expected,
    a.quantity AS actual,
    COALESCE(a.quantity, 0) - COALESCE(e.quantity, 0) AS difference
FROM expected_inventory e
FULL OUTER JOIN actual_inventory a ON e.product_id = a.product_id
WHERE e.quantity IS NULL  -- In actual but not expected
   OR a.quantity IS NULL  -- In expected but not actual
   OR e.quantity <> a.quantity;  -- Quantities don't match

Use Case 2: Find Orphaned Records

-- Find customers without orders AND orders without customers
SELECT
    c.name AS customer,
    o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id
WHERE c.id IS NULL  -- Orders without customers
   OR o.id IS NULL; -- Customers without orders

Use Case 3: Merge Two Datasets

-- Combine user data from two sources
SELECT
    COALESCE(old.email, new.email) AS email,
    COALESCE(new.name, old.name) AS name,  -- Prefer new data
    COALESCE(new.phone, old.phone) AS phone
FROM old_users old
FULL OUTER JOIN new_users new ON old.email = new.email;

Finding Unmatched Rows

Only Left Unmatched

SELECT e.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;  -- Employees without departments

Result:

  name
--------
 Carol
 Dave

Only Right Unmatched

SELECT d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;  -- Departments without employees

Result:

    name
------------
 Marketing

All Unmatched (From Either Side)

SELECT
    e.name AS unmatched_employee,
    d.name AS unmatched_department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;

Result:

 unmatched_employee | unmatched_department
--------------------+----------------------
 Carol              | NULL
 Dave               | NULL
 NULL               | Marketing

Multiple FULL OUTER JOINs

SELECT
    COALESCE(e.name, d.name, l.name) AS entity,
    e.id AS employee_id,
    d.id AS department_id,
    l.id AS location_id
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
FULL OUTER JOIN locations l ON d.location_id = l.id;

Shows all employees, departments, and locations, even if unrelated.

Aggregating with FULL OUTER JOIN

Count Matched and Unmatched

SELECT
    COUNT(*) AS total_rows,
    COUNT(e.id) AS employees_with_dept,
    COUNT(d.id) AS departments_with_employees,
    COUNT(*) - COUNT(e.id) AS departments_without_employees,
    COUNT(*) - COUNT(d.id) AS employees_without_dept
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Handling NULLs in FULL OUTER JOIN

COALESCE for IDs

SELECT
    COALESCE(e.id, d.id) AS record_id,  -- Use whichever exists
    e.name AS employee,
    d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

CASE for Status

SELECT
    COALESCE(e.name, 'No employee') AS employee,
    COALESCE(d.name, 'No department') AS department,
    CASE
        WHEN e.id IS NOT NULL AND d.id IS NOT NULL THEN 'Matched'
        WHEN e.id IS NOT NULL THEN 'Employee only'
        ELSE 'Department only'
    END AS status
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

When NOT to Use FULL OUTER JOIN

Use INNER JOIN When

You only need matches:

-- ❌ Overkill
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NOT NULL AND d.id IS NOT NULL;

-- ✅ Simpler and faster
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Use LEFT JOIN When

You only need one side + matches:

-- ❌ Unnecessary
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NOT NULL;

-- ✅ More appropriate
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Performance Considerations

FULL OUTER JOIN can be slower than other JOINs:

-- Slower: Returns all rows from both tables
SELECT *
FROM large_table1
FULL OUTER JOIN large_table2 ON large_table1.id = large_table2.ref_id;

-- Faster: Only returns matches
SELECT *
FROM large_table1
INNER JOIN large_table2 ON large_table1.id = large_table2.ref_id;

Use FULL OUTER JOIN only when you genuinely need all rows from both tables.

Simulating FULL OUTER JOIN with UNION

Some databases don't support FULL OUTER JOIN. You can simulate it:

-- FULL OUTER JOIN
SELECT e.name, d.name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- Equivalent with UNION
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id

UNION

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

(We'll cover UNION in Module 6)

Common Patterns

Pattern 1: Audit Report

-- Compare expected vs actual
SELECT
    COALESCE(e.item, a.item) AS item,
    e.count AS expected,
    a.count AS actual,
    CASE
        WHEN e.count IS NULL THEN 'Unexpected item'
        WHEN a.count IS NULL THEN 'Missing item'
        WHEN e.count <> a.count THEN 'Count mismatch'
        ELSE 'OK'
    END AS status
FROM expected e
FULL OUTER JOIN actual a ON e.item = a.item;

Pattern 2: User Sync

-- Sync users between systems
SELECT
    COALESCE(sys1.email, sys2.email) AS email,
    sys1.id AS system1_id,
    sys2.id AS system2_id,
    CASE
        WHEN sys1.id IS NULL THEN 'Add to System 1'
        WHEN sys2.id IS NULL THEN 'Add to System 2'
        ELSE 'Synced'
    END AS sync_status
FROM system1_users sys1
FULL OUTER JOIN system2_users sys2 ON sys1.email = sys2.email;

Practice Exercises

Exercise 1

Find all employees and all departments, showing which are matched.

SELECT
    e.name AS employee,
    d.name AS department,
    CASE
        WHEN e.id IS NOT NULL AND d.id IS NOT NULL THEN 'Matched'
        WHEN e.id IS NOT NULL THEN 'No department'
        ELSE 'No employees'
    END AS status
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Exercise 2

Find orphaned records (employees without departments OR departments without employees).

SELECT
    e.name AS orphaned_employee,
    d.name AS empty_department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;

Exercise 3

Compare two inventory systems and flag discrepancies.

SELECT
    COALESCE(inv1.product_id, inv2.product_id) AS product_id,
    inv1.quantity AS warehouse1_qty,
    inv2.quantity AS warehouse2_qty,
    ABS(COALESCE(inv1.quantity, 0) - COALESCE(inv2.quantity, 0)) AS difference
FROM inventory_warehouse1 inv1
FULL OUTER JOIN inventory_warehouse2 inv2 ON inv1.product_id = inv2.product_id
WHERE inv1.quantity IS NULL
   OR inv2.quantity IS NULL
   OR inv1.quantity <> inv2.quantity;

Key Takeaways

• ✅ FULL OUTER JOIN returns all rows from both tables
• ✅ NULL values appear for unmatched sides
• ✅ Use for data reconciliation, finding orphans, merging datasets
• ✅ Less common than INNER, LEFT, RIGHT joins
• ✅ Can be slower than other JOIN types
• ✅ COALESCE helps handle NULLs from either side
• ✅ Filter with WHERE to find only unmatched rows

Next Steps

You've mastered all the basic JOIN types! In the next lesson, we'll explore Self Joins and Multiple Joins—advanced techniques for querying hierarchical data and combining many tables at once.

Keep going strong!