PostgreSQL Full Join

How FULL JOIN Works

FULL JOIN, also called FULL OUTER JOIN, is the union of what LEFT JOIN and RIGHT JOIN would each produce. Matched rows appear once with data from both sides; left-only rows appear with NULLs on the right; right-only rows appear with NULLs on the left. No row from either table is ever dropped.

SELECT c.name AS customer, o.id AS order_id
FROM customers AS c
FULL JOIN orders AS o ON o.customer_id = c.id;

Finding All Unmatched Rows on Either Side

FULL JOIN is especially useful for reconciliation tasks, like comparing two datasets that should mostly overlap and surfacing records present in only one of them.

SELECT COALESCE(a.sku, b.sku) AS sku,
       a.quantity AS warehouse_qty,
       b.quantity AS storefront_qty
FROM warehouse_stock AS a
FULL JOIN storefront_stock AS b ON a.sku = b.sku
WHERE a.sku IS NULL OR b.sku IS NULL;

Full Join vs Union of Left and Right

Conceptually, FULL JOIN a b ON condition produces the same rows as a LEFT JOIN of a to b combined with a RIGHT JOIN of a to b, deduplicated. PostgreSQL computes this directly and efficiently rather than literally running two joins.

SELECT COALESCE(c.name, 'Unknown') AS customer,
       COUNT(o.id) AS order_count
FROM customers AS c
FULL JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.name;