Financial Reconciliation with FULL OUTER JOIN in SQL

Question

You have `bank_settlements` and `internal_ledger`. Write a SQL query using a `FULL OUTER JOIN` to show all transactions from both sources, indicating whether each transaction exists in 'Both', 'Bank Only', or 'Internal Only'.

Accepted Answer

Optimal Solution:
SELECT 
  COALESCE(b.txn_id, i.txn_id) AS txn_reference,
  CASE 
    WHEN b.txn_id IS NOT NULL AND i.txn_id IS NOT NULL THEN 'Matched Both'
    WHEN b.txn_id IS NOT NULL THEN 'Bank Only'
    ELSE 'Internal Only'
  END AS reconciliation_status
FROM bank_settlements b
FULL OUTER JOIN internal_ledger i ON b.txn_id = i.txn_id
ORDER BY txn_reference ASC;

Technical Explanation:
A `FULL OUTER JOIN` preserves unmatched rows from both sides. `COALESCE` picks whichever transaction ID is present, and `CASE` evaluates the match status.

bank_txn_id	internal_txn_id
TXN_101	TXN_101
TXN_102	NULL
NULL	TXN_103

txn_reference	reconciliation_status
TXN_101	Matched Both
TXN_102	Bank Only
TXN_103	Internal Only