PostgreSQL FULL OUTER JOIN

FULL OUTER JOIN combines LEFT JOIN and RIGHT JOIN into a single operation: it returns every row from both tables. Rows that match are combined into a single result row; rows from either table that have no counterpart on the other side appear with NULL in the columns of the unmatched table. It is the most complete join type and is especially useful for data reconciliation and gap analysis.

Syntax

SELECT select_list
FROM table1
FULL OUTER JOIN table2 ON table1.column = table2.column;

FULL JOIN and FULL OUTER JOIN are identical — the OUTER keyword is optional:

SELECT * FROM t1 FULL JOIN      t2 ON t1.id = t2.t1_id;
SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.t1_id;

Practical Example

The following schema tracks warehouses and the product stock lines assigned to them. Some warehouses have no stock lines and some stock lines may reference a warehouse that was decommissioned.

CREATE TABLE warehouses (
  warehouse_id   SERIAL PRIMARY KEY,
  warehouse_name VARCHAR(100) NOT NULL,
  location       VARCHAR(80)
);

CREATE TABLE stock_lines (
  stock_id       SERIAL PRIMARY KEY,
  product_sku    VARCHAR(50) NOT NULL,
  quantity       INT NOT NULL,
  warehouse_id   INT REFERENCES warehouses(warehouse_id)
);

INSERT INTO warehouses (warehouse_name, location) VALUES
  ('Austin Hub',    'Austin, TX'),
  ('Denver Cache',  'Denver, CO'),
  ('Seattle Depot', 'Seattle, WA'),
  ('Miami Port',    'Miami, FL');   -- no stock lines yet

INSERT INTO stock_lines (product_sku, quantity, warehouse_id) VALUES
  ('SKU-A101', 200, 1),
  ('SKU-B202', 450, 1),
  ('SKU-C303', 120, 2),
  ('SKU-D404',  80, 3),
  ('SKU-E505', 310, NULL);          -- orphaned: no warehouse assigned

Full picture — all warehouses and all stock lines

SELECT
  w.warehouse_name,
  w.location,
  sl.product_sku,
  sl.quantity
FROM warehouses w
FULL OUTER JOIN stock_lines sl ON sl.warehouse_id = w.warehouse_id
ORDER BY w.warehouse_name NULLS LAST, sl.product_sku;

The result includes:

• Austin Hub and Denver Cache with their matching stock lines.
• Seattle Depot with its stock line.
• Miami Port with NULL for product_sku and quantity (no stock lines).
• SKU-E505 with NULL for warehouse_name and location (no warehouse assigned).

Find gaps — warehouses with no stock

SELECT w.warehouse_name, w.location
FROM warehouses w
FULL OUTER JOIN stock_lines sl ON sl.warehouse_id = w.warehouse_id
WHERE sl.stock_id IS NULL;

Find orphaned stock lines — no warehouse assigned

SELECT sl.product_sku, sl.quantity
FROM warehouses w
FULL OUTER JOIN stock_lines sl ON sl.warehouse_id = w.warehouse_id
WHERE w.warehouse_id IS NULL;

Reconciliation — all mismatches at once

SELECT
  COALESCE(w.warehouse_name, '-- no warehouse --') AS warehouse,
  COALESCE(sl.product_sku,   '-- no stock --')     AS sku,
  sl.quantity
FROM warehouses w
FULL OUTER JOIN stock_lines sl ON sl.warehouse_id = w.warehouse_id
WHERE w.warehouse_id IS NULL OR sl.stock_id IS NULL
ORDER BY warehouse, sku;

FULL OUTER JOIN vs Other Join Types

Testing with Vela

FULL OUTER JOIN reconciliation queries are often used before and after data migrations to verify no rows were lost or duplicated. Vela’s database branching is ideal for this workflow: run the migration on a branch, execute the reconciliation query to compare the branch against production, and only promote the change when the diff is empty. This eliminates guesswork during cutovers.

Production Tips

• FULL OUTER JOIN can produce large result sets when both tables are large — always check estimated row counts with EXPLAIN ANALYZE before running on production.
• Index the join columns on both sides to help the planner choose a hash join strategy rather than a nested loop.
• Use COALESCE(t1.col, t2.col) to retrieve a non-NULL value from either side when you only care which side is populated, not which table it came from.
• FULL JOIN (without OUTER) is identical and slightly more concise — prefer the shorter form in team style guides.
• For large-scale table comparison, consider EXCEPT and EXCEPT ALL set operations as an alternative; they can be more memory-efficient than a FULL OUTER JOIN for simple row-equality checks.
• When both tables are large and the join column is not indexed, expect a hash join with significant memory usage — monitor work_mem settings and pg_stat_activity if queries are slow.