↔️FULL OUTER JOIN

Matches plus orphans on both sides

FULL OUTER JOIN returns the inner join pairs, then any left rows without a partner (NULLs on the right), then any right rows without a partner (NULLs on the left). It is the relational tool for reconciliation: compare staging to core, lab LIMS export to warehouse fact, or two vendor feeds keyed by the same business id.

It is uncommon in day-to-day OLTP APIs, most entity relationships are mandatory (INNER) or optional from one direction (LEFT). When you reach for FULL, you are usually answering “what is missing, extra, or mismatched between two snapshots?”

SQL

Diff titles when keys align (PostgreSQL: IS DISTINCT FROM)

SELECT COALESCE(s.experiment_id, c.experiment_id) AS experiment_id,
       s.title AS staging_title,
       c.title AS core_title
FROM ingest.experiments_staging s
FULL OUTER JOIN core.experiments c
  ON c.experiment_id = s.experiment_id
WHERE s.title IS DISTINCT FROM c.title;

✓Illustrative result set

experiment_id	staging_title	core_title
E-100	Pilot run	Pilot study
E-999	Ghost draft	NULL

FULL JOIN then WHERE filters out NULL pairs you meant to keep

A careless WHERE s.id IS NOT NULL AND c.id IS NOT NULL collapses the outer back toward INNER and hides rows that exist only on one side, the main reason you used FULL in the first place.

Classify row origin with CASE, filter deliberately

Push “only show mismatches” logic into explicit predicates on coalesced keys, or use a CASE column that labels rows as both, staging_only, or core_only, then filter on that label.

SQL

Label where each row came from

SELECT COALESCE(s.experiment_id, c.experiment_id) AS experiment_id,
       CASE
         WHEN s.experiment_id IS NULL THEN 'core_only'
         WHEN c.experiment_id IS NULL THEN 'staging_only'
         ELSE 'both'
       END AS origin
FROM ingest.experiments_staging s
FULL OUTER JOIN core.experiments c
  ON c.experiment_id = s.experiment_id;

✓Illustrative result set

experiment_id	origin
E-100	both
E-999	staging_only
E-200	core_only

🧱Engines without native FULL JOIN

Some dialects emulate with UNION of LEFT and RIGHT anti halves, plans can be heavy.

If portability matters, pre-filter both sides in CTEs (indexed keys, recent partitions) so each half stays small. Consider pushing reconciliation to ELT in the warehouse where columnar engines handle large anti-joins cheaply.

Coalesce keys

Outer rows have NULL on one side of the key, use COALESCE(s.id, c.id) for display and for joining out to a dimension table in the next step.

NULL = NULL is still unknown

Comparing nullable keys with = after FULL can drop rows. Use IS NOT DISTINCT FROM or two-sided IS NULL checks where your engine supports them.

⚡ Advanced / dialect note

IS DISTINCT FROM treats two NULLs as “not distinct”: ideal for row comparisons in diffs. On engines without it, expand to (a = b OR (a IS NULL AND b IS NULL)) for equality including NULL.

Cardinality warning

If keys are not unique on either side, FULL JOIN can explode just like INNER, dedupe each input to one row per business key before the FULL, or aggregate inside CTEs first.

TL;DR

FULL OUTER JOIN returns all rows from both tables, pairing on the join key and filling unmatched columns with NULL. It is useful for reconciling two datasets (diffs, ETL comparisons) but is not standard SQL everywhere (e.g. SQLite historically lacked it). Watch cardinality, non-unique keys still multiply rows.