SQL Data Reconciliation: Why Two Tables Don't Match

Step 1: Compare row counts

Before diffing any content, confirm a difference actually exists at the count level — it's the cheapest possible check and immediately tells you whether you're dealing with missing/extra rows, value differences, or both.

SELECT 'system_a' AS source, COUNT(*) AS row_count FROM orders_system_a
UNION ALL
SELECT 'system_b' AS source, COUNT(*) AS row_count FROM orders_system_b;

If counts match, don't stop here — matching counts only mean the same number of rows exist, not that their values agree.

Step 2: Find missing/extra rows with EXCEPT / MINUS

-- Rows in system A that are missing entirely from system B
SELECT order_id, customer_id, amount, order_date
FROM orders_system_a
EXCEPT
SELECT order_id, customer_id, amount, order_date
FROM orders_system_b;

-- Rows in system B that are missing entirely from system A
SELECT order_id, customer_id, amount, order_date
FROM orders_system_b
EXCEPT
SELECT order_id, customer_id, amount, order_date
FROM orders_system_a;
-- Oracle: replace EXCEPT with MINUS — identical behavior, different keyword

This finds two things at once: rows entirely missing from one side, and rows whose values differ on any compared column, since EXCEPT compares full rows, not just keys.

Step 3: Find value-level differences with FULL OUTER JOIN

EXCEPT tells you a row differs somewhere — it doesn't tell you which column. For that, join on the shared key and compare each column explicitly:

SELECT
  COALESCE(a.order_id, b.order_id) AS order_id,
  a.amount AS amount_system_a,
  b.amount AS amount_system_b,
  CASE
    WHEN a.order_id IS NULL THEN 'missing_in_a'
    WHEN b.order_id IS NULL THEN 'missing_in_b'
    WHEN a.amount IS DISTINCT FROM b.amount THEN 'amount_mismatch'
    ELSE 'match'
  END AS diff_reason
FROM orders_system_a a
FULL OUTER JOIN orders_system_b b ON a.order_id = b.order_id
WHERE a.order_id IS NULL
   OR b.order_id IS NULL
   OR a.amount IS DISTINCT FROM b.amount;

IS DISTINCT FROM (PostgreSQL, SQL Server via a workaround, and several others) treats NULL = NULL as equal — a plain != comparison silently drops rows where either side is NULL, which is a common source of missed diffs.

Real-world example: reconciling order totals across two systems

A finance team reports that total revenue in the reporting warehouse is $4,300 lower than the number in the source order-management system for the same day. Running the row-count query above shows the warehouse has 3 fewer rows. The EXCEPT query identifies the 3 missing order_ids. Checking the ETL logs for those specific IDs shows they were inserted in the source system one minute after that day's incremental load watermark had already advanced — a textbook late-arriving-data case (see Incremental Loading Strategies in SQL). The fix isn't a one-off manual insert; it's adding a lookback window to the load so this class of drift stops recurring.

Common root causes of table drift

• A sync/ETL job that failed partway through and never fully completed or retried.
• Rows updated in one system after the last sync ran, so the copy is simply stale.
• Timezone differences causing timestamp-based joins or date filters to misalign by hours.
• Duplicate or missing keys causing a row to never be compared against its intended counterpart at all.

Common mistakes

• Using != instead of a NULL-safe comparison, silently missing diffs where one side is NULL.
• Stopping at the row-count check and declaring the data "fine" when counts happen to match but values don't.
• Comparing on a non-unique key, causing the FULL OUTER JOIN to fan out and produce misleading extra rows (see Why Two SQL Queries Return Different Row Counts).
• Not accounting for floating-point precision when comparing monetary or decimal columns across systems that store them differently.