FULL OUTER JOIN

Keeps all rows from both tables, matching where possible and padding the rest with NULLs.

Syntax

SELECT COALESCE(a.key, b.key) AS key, ...
FROM a
FULL [OUTER] JOIN b ON b.key = a.key

A full join = inner matches + unmatched left rows + unmatched right rows. It is the tool for reconciliation: comparing two lists and seeing what is only on one side, only on the other, or on both.

Use COALESCE(a.key, b.key) for a single key column, since either side may be NULL.
Classify rows with CASE WHEN a.key IS NULL THEN 'only in b' WHEN b.key IS NULL THEN 'only in a' ELSE 'both' END.
Aggregate both sides to the same grain before the full join, otherwise duplicates multiply.
WHERE conditions on one side discard the other side's unmatched rows; filter inside pre-aggregated CTEs.
PostgreSQL supports FULL JOIN only with merge- or hash-joinable conditions (plain equality is fine).

Examples

Budget vs actual by account ledger

WITH actual AS (
  SELECT jl.account_code,
         sum(CASE WHEN g.account_type IN ('asset', 'expense') THEN jl.debit - jl.credit
                  ELSE jl.credit - jl.debit END) AS actual
  FROM journal_lines jl
  JOIN gl_accounts g ON g.account_code = jl.account_code
  JOIN journal_entries je ON je.entry_id = jl.entry_id
  JOIN fiscal_periods fp ON fp.period_id = je.period_id
  WHERE je.status = 'posted' AND fp.fiscal_year = 2025
  GROUP BY jl.account_code
), budget AS (
  SELECT account_code, sum(amount) AS budget
  FROM budgets WHERE fiscal_year = 2025
  GROUP BY account_code
)
SELECT COALESCE(a.account_code, b.account_code) AS account_code,
       b.budget, a.actual,
       CASE WHEN b.account_code IS NULL THEN 'no budget'
            WHEN a.account_code IS NULL THEN 'no activity'
            ELSE 'both' END AS coverage
FROM actual a
FULL JOIN budget b ON b.account_code = a.account_code
ORDER BY account_code;

Actuals are signed by account type so they are comparable with the positive budgets. Accounts with activity but no budget, and budgeted accounts without postings, both appear.

Reconcile two lists starter

SELECT COALESCE(s.city, c.city) AS city,
       s.city IS NOT NULL AS has_customers_starter,
       c.city IS NOT NULL AS in_list
FROM (SELECT DISTINCT city FROM customers) s
FULL JOIN (VALUES ('Riyadh'), ('Jeddah'), ('Tabuk')) AS c(city) ON c.city = s.city
ORDER BY city;

Cities only in the data, only in the hard-coded list, or in both.

Pitfalls

Selecting a.key instead of COALESCE(a.key, b.key) (NULL for right-only rows).
Full-joining detail rows instead of pre-aggregated ones (duplicates multiply).
A WHERE filter on one side that removes the other side's unmatched rows.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: No FULL OUTER JOIN; emulate with LEFT JOIN UNION ALL (RIGHT JOIN ... WHERE left key IS NULL).
SQLite: RIGHT and FULL OUTER JOIN are supported since 3.39.