SQL Outer Join: LEFT, RIGHT, and FULL Explained with Examples

An outer join returns all rows from one or both tables, even when there is no matching row in the other table. Where no match exists, the result contains NULLs. This contrasts with an inner join, which only returns rows that match in both tables.

The Three Types of Outer Joins

LEFT OUTER JOIN (LEFT JOIN)

Returns all rows from the left table. Where no match exists in the right table, right-side columns are NULL.

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

Result: Every employee appears, even those not assigned to a department (they show NULL for department_name).

RIGHT OUTER JOIN (RIGHT JOIN)

Returns all rows from the right table. Where no match exists in the left table, left-side columns are NULL.

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

Result: Every department appears, even those with no employees (they show NULL for employee name).

FULL OUTER JOIN (FULL JOIN)

Returns all rows from both tables. Unmatched rows from either side show NULLs for the other side's columns.

SELECT e.name, d.department_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.id;

Result: Every employee AND every department appears. Unassigned employees show NULL department. Empty departments show NULL employee.

Visual Explanation

Consider these two tables:

employees: Alice (dept 1), Bob (dept 1), Charlie (dept NULL) departments: 1-Engineering, 2-Marketing, 3-Sales

Practical Examples

LEFT JOIN: Find Missing Relationships

-- Products never ordered
SELECT p.product_name
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.id IS NULL;

-- Leads that never converted to customers
SELECT l.email, l.source
FROM leads l
LEFT JOIN customers c ON l.email = c.email
WHERE c.id IS NULL;

RIGHT JOIN: Ensure Completeness

-- All categories with their product count (including empty categories)
SELECT c.category_name, COUNT(p.id) AS product_count
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id
GROUP BY c.category_name;

Most developers rewrite RIGHT JOINs as LEFT JOINs by swapping table order (more readable):

-- Equivalent, more common style
SELECT c.category_name, COUNT(p.id) AS product_count
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
GROUP BY c.category_name;

FULL JOIN: Data Reconciliation

-- Compare CRM records to billing system (find mismatches)
SELECT
    crm.account_name,
    crm.mrr AS crm_mrr,
    billing.account_name AS billing_name,
    billing.mrr AS billing_mrr,
    CASE
        WHEN crm.account_id IS NULL THEN 'Missing from CRM'
        WHEN billing.account_id IS NULL THEN 'Missing from billing'
        WHEN crm.mrr != billing.mrr THEN 'MRR mismatch'
        ELSE 'Matched'
    END AS status
FROM crm_accounts crm
FULL JOIN billing_accounts billing ON crm.account_id = billing.account_id
WHERE crm.account_id IS NULL
   OR billing.account_id IS NULL
   OR crm.mrr != billing.mrr;

FULL JOIN: Complete Timeline

-- Revenue and costs by month (even months with only one or the other)
SELECT
    COALESCE(r.month, c.month) AS month,
    COALESCE(r.revenue, 0) AS revenue,
    COALESCE(c.costs, 0) AS costs,
    COALESCE(r.revenue, 0) - COALESCE(c.costs, 0) AS profit
FROM (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
    FROM orders GROUP BY 1
) r
FULL JOIN (
    SELECT DATE_TRUNC('month', expense_date) AS month, SUM(amount) AS costs
    FROM expenses GROUP BY 1
) c ON r.month = c.month
ORDER BY month;

Multiple Outer Joins

You can chain outer joins:

SELECT
    c.customer_name,
    o.order_count,
    s.ticket_count,
    p.total_payments
FROM customers c
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY 1
) o ON c.id = o.customer_id
LEFT JOIN (
    SELECT customer_id, COUNT(*) AS ticket_count FROM support_tickets GROUP BY 1
) s ON c.id = s.customer_id
LEFT JOIN (
    SELECT customer_id, SUM(amount) AS total_payments FROM payments GROUP BY 1
) p ON c.id = p.customer_id;

Database Support

MySQL does not support FULL JOIN natively. Workaround:

-- MySQL FULL JOIN emulation
SELECT * FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id
UNION ALL
SELECT * FROM table_a a RIGHT JOIN table_b b ON a.id = b.a_id
WHERE a.id IS NULL;

Performance Considerations

1. Outer joins are slower than inner joins because the database must handle unmatched rows and produce NULLs.
2. Index the join columns on both tables for best performance.
3. Filter early using ON conditions rather than WHERE (especially for LEFT JOINs).
4. Avoid FULL JOINs on large tables unless necessary; they prevent certain optimizations.

When to Use Each Type

Platforms like Skopx handle join selection automatically. Ask "Which customers have never placed an order?" and the AI generates the appropriate LEFT JOIN with NULL filter, without you specifying the join type.

Summary

Outer joins preserve rows from one or both tables even when no match exists in the other table. LEFT JOIN (most common) keeps all left-table rows. RIGHT JOIN keeps all right-table rows. FULL JOIN keeps all rows from both. Use them when completeness matters more than strict matching.