Reconcile two systems with a full outer join

Reconciliation asks which business records agree, which differ and which exist in only one source. A full outer join preserves both sides, making it useful for this task. Before joining, align the business key, record grain, currency and reporting cutoff; otherwise the output mixes genuine discrepancies with definition differences.

Equal grand totals do not prove that the underlying records agree. Two systems can contain offsetting errors or entirely different records with the same combined value.

Start with a deliberately deceptive example

System A contains K1=100, K2=200 and K3=300. System B contains K1=100, K2=250 and K4=250. Both total 600 units, yet only K1 matches. K2 differs by 50, K3 is absent from B and K4 is absent from A.

Use a consistent monetary unit in actual reporting. The small integers here are synthetic values, not prices or real accounting records. The following SQLite-compatible query expresses full-outer behavior through a left join plus unmatched right-side rows, so it also illustrates the logic behind the operation:

WITH a(business_key, amount) AS (
    VALUES ('K1',100),('K2',200),('K3',300)
), b(business_key, amount) AS (
    VALUES ('K1',100),('K2',250),('K4',250)
), reconciled AS (
    SELECT a.business_key, a.amount AS amount_a, b.amount AS amount_b,
           CASE WHEN b.business_key IS NULL THEN 'A only'
                WHEN a.amount = b.amount THEN 'match'
                ELSE 'amount mismatch' END AS result
    FROM a LEFT JOIN b ON b.business_key = a.business_key
    UNION ALL
    SELECT b.business_key, NULL, b.amount, 'B only'
    FROM b LEFT JOIN a ON a.business_key = b.business_key
    WHERE a.business_key IS NULL
)
SELECT *, COALESCE(amount_a,0) - COALESCE(amount_b,0) AS signed_difference
FROM reconciled ORDER BY business_key;

Expect four rows: K1 match with difference 0, K2 mismatch −50, K3 A only +300, K4 B only −250. The signed differences sum to zero while three of four keys require explanation.

The SQL table-expression documentation describes native FULL OUTER JOIN semantics. Check your database's support and syntax before replacing the portable pattern with a native join.

Why UNION ALL matters here

The first branch includes every A record and any matching B record. The second branch includes only B records without an A match. Those populations are disjoint by construction, so UNION ALL preserves them without an unnecessary duplicate-removal step.

This depends on valid, non-NULL, unique business keys within each source. If A contains two K2 rows and B contains three, a direct join can create six combinations. It has not reconciled five source records; it has multiplied them.

Validate key uniqueness first. If the actual grain is invoice line, use the full invoice-and-line key. If the business requires invoice-level reconciliation, aggregate both sources independently to one invoice row before joining, retaining line-level evidence for investigation.

Separate missing records from missing amounts

The example assumes each present record has a non-NULL amount. In a real system, a record can exist while its amount is unknown. Use key presence to classify one-sided records and a separate status for missing amounts.

COALESCE in the signed-difference column treats an absent side as zero for arithmetic only. It does not establish that a missing record legitimately has zero value. Keep the classification beside the numeric difference so the accounting interpretation is not lost.

For decimal amounts, use an agreed exact representation or a documented tolerance. A tolerance must reflect business and source precision, not conceal unexplained discrepancies. Match currencies before comparing values; identical numeric amounts in different currencies do not reconcile.

Create an exception workflow

Record source snapshot times and preserve the unmatched key lists. A payment may arrive after the sales extract cutoff, so a temporary A-only result can be expected. Label timing differences separately from unexplained missing records, then rerun after the agreed settlement window.

Track resolved exceptions by key and explanation rather than manually editing source amounts until totals match. Report both the count of discrepant keys and the absolute value of differences; signed differences alone allow positive and negative errors to cancel.

Exercise: duplicate K2 in system B and observe the join expansion. Then add an existing K5 record with a NULL amount. Extend the classification so it distinguishes unknown amount from absent record without turning both into zero.

Run the query in the advanced SQL lab. NeuraPath's Data Analytics with Generative AI course connects these reconciliation patterns with practical reporting. A strong deliverable includes the exception table and resolution rules, not merely two equal totals.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in SQL slowly changing dimensions in a historical customer report.
• Continue with SQL recursive CTEs for an organization hierarchy.