FULL OUTER JOIN in SQL: Syntax and When to Use It

FULL OUTER JOIN returns every row from both tables — matched pairs where the join condition holds, plus the leftovers from each side padded with NULLs. It's the join you reach for when the question is "what's in A, what's in B, and where do they disagree?", which makes it the workhorse of reconciliation and data-quality checks.

What does FULL OUTER JOIN actually return?

Picture the classic two-circle Venn diagram. An INNER JOIN returns only the overlapping middle. A FULL OUTER JOIN returns the entire diagram: the overlap, the left-only crescent, and the right-only crescent. Rows in the crescents get NULLs for every column that belongs to the other table.

SELECT e.name, e.dept_id, d.dept_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.dept_id = d.dept_id;

-- Result contains three kinds of rows:
-- 1. Employee + department      (matched: overlap)
-- 2. Employee + NULL dept_name  (employee with no valid department)
-- 3. NULL name + department     (department with no employees)

Most engines accept FULL JOIN as shorthand — the OUTER keyword is optional, just as it is for LEFT and RIGHT joins.

How do I find rows that exist in only one table?

Filter on NULL in a column that can't legitimately be NULL — usually the join key from the other side. Left-only rows have NULL in the right table's key; right-only rows have NULL in the left table's key; checking both with OR gives you every mismatch in a single reconciliation query.

-- All disagreements between two systems, one query
SELECT
    COALESCE(a.invoice_id, b.invoice_id) AS invoice_id,
    a.amount AS system_a_amount,
    b.amount AS system_b_amount,
    CASE
        WHEN b.invoice_id IS NULL THEN 'only in A'
        WHEN a.invoice_id IS NULL THEN 'only in B'
        WHEN a.amount <> b.amount THEN 'amount mismatch'
    END AS issue
FROM billing_a a
FULL OUTER JOIN billing_b b
    ON a.invoice_id = b.invoice_id
WHERE a.invoice_id IS NULL
   OR b.invoice_id IS NULL
   OR a.amount <> b.amount;

The COALESCE on the key is important: whichever side is missing contributes NULL, so coalescing the two keys guarantees a usable identifier on every output row. Test the NULL check against join-key columns, not data columns that might be genuinely NULL in the source.

How do I do a FULL OUTER JOIN in MySQL?

MySQL (and SQLite before version 3.39) has no FULL OUTER JOIN. The standard workaround is a LEFT JOIN unioned with a RIGHT JOIN that keeps only the rows the LEFT JOIN missed — i.e., right-side rows with no match. Filtering the second query prevents matched rows from appearing twice.

-- MySQL emulation of FULL OUTER JOIN
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id

UNION ALL

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.dept_id IS NULL;   -- only right-side-only rows

Use UNION ALL plus the WHERE filter rather than plain UNION: it's faster (no dedup sort) and it doesn't accidentally collapse legitimately duplicate data rows. If you're shaky on the LEFT/RIGHT halves of this trick, our LEFT JOIN vs RIGHT JOIN guide covers them in detail.

Is FULL OUTER JOIN the same as UNION of LEFT and RIGHT joins?

Almost — and the difference bites. A plain LEFT UNION RIGHT without the NULL filter relies on UNION's duplicate removal to merge the matched rows that both halves produce. That works only when result rows are genuinely identical; if your SELECT list makes matched rows distinct (say, it includes a computed column), duplicates survive, and UNION's dedup may also erase real duplicate rows from your data.

-- Fragile: relies on UNION dedup to remove double-counted matches
SELECT ... FROM a LEFT  JOIN b ON a.id = b.id
UNION
SELECT ... FROM a RIGHT JOIN b ON a.id = b.id;

-- Robust: UNION ALL + explicit anti-join filter (previous section)

The filtered UNION ALL version is semantically identical to a true FULL OUTER JOIN, so prefer it whenever you must emulate one.

When should you actually use FULL OUTER JOIN?

Three situations come up repeatedly: reconciling two systems (billing vs. ledger, CRM vs. warehouse), comparing before/after snapshots during a migration, and combining two metric tables that each may have dates or keys the other lacks. If you find yourself writing one for a routine "enrich A with B" query, that is usually a sign you wanted a LEFT JOIN instead — full outer joins on large tables can be expensive, since neither side can be filtered early.

-- Metric merge: neither side is "primary"
SELECT
    COALESCE(w.metric_date, m.metric_date) AS metric_date,
    w.web_sessions,
    m.mobile_sessions
FROM web_daily w
FULL OUTER JOIN mobile_daily m
    ON w.metric_date = m.metric_date
ORDER BY metric_date;