Part 65: FULL (OUTER) JOIN: Combining Both Sides

A FULL OUTER JOIN returns all records when there is a match in either the left OR right table.

1. Syntax

-- Reconciling two payment ledgers:
SELECT 
  COALESCE(b.transaction_id, s.transaction_id) AS tx_id,
  b.amount AS bank_amount,
  s.amount AS stripe_amount
FROM bank_statement b
FULL OUTER JOIN stripe_payouts s ON b.transaction_id = s.transaction_id
WHERE b.transaction_id IS NULL OR s.transaction_id IS NULL;

Key Takeaways

• FULL OUTER JOIN is essential for financial reconciliation and finding discrepancies between two systems.

In Part 66, we explore CROSS JOIN: Cartesian Products!