FULL OUTER JOIN
Keep every row from both sides, matching where possible and NULL-filling the gaps. The shape you reach for when both sides are sources of truth (orders the warehouse shipped vs orders the payments system recorded) and you need to surface reconciliation gaps in both directions.
Syntax #
sql
SELECT a.id, b.id
FROM a
FULL OUTER JOIN b ON a.key = b.key;Example #
Loading SQL editor...
Info
Check IS NULL on both sides separately — that's usually the actual report you wanted. The raw FULL OUTER JOIN shows matched + left-only + right-only rows all mixed together. The two filtered views (WHERE left_key IS NULL for "only on the right" and vice versa) are what reconciliation reports need. Keep them as separate CTEs or separate queries for clarity.