How to Reconcile Two Tables in SQL When the Row Counts Match

1. The check that passes and proves nothing

Before the explanation: a table has been copied from one system to another. Say what you would check first.

Almost everybody says the row count, and here it is.

source_rows | target_rows
         16 |          16

Sixteen against sixteen. On most migrations that is where the checking stops, the ticket gets closed, and the problem surfaces six weeks later in a finance report.

Now one more query, on the same two tables.

source_revenue | target_revenue | difference
          9890 |           9490 |        400

Four hundred is missing, from a copy with exactly the right number of rows. Rows and values are two different things, and a count can only ever answer a question about rows.

The reason to lead with this is that the count is not useless, it is just first. A row count catches the biggest failures: nothing loaded, half loaded, everything loaded twice. It cannot catch anything that leaves the count intact, and the rest of this page is about how much that is.

2. Match on the key, in both directions

To compare rows you need a key: the column, or set of columns, that identifies the same real thing in both tables. Then a full outer join on that key, which keeps rows that fail to match from either side, and one look at where the nulls fall.

SELECT COALESCE(s.order_id, t.order_id) AS order_id,
       CASE WHEN t.order_id IS NULL THEN 'source only'
            WHEN s.order_id IS NULL THEN 'target only' END AS where_it_is
FROM orders s
FULL OUTER JOIN orders_target t USING (order_id)
WHERE s.order_id IS NULL OR t.order_id IS NULL;

1008 | source only
1017 | target only

Order 1008 exists in the source and not in the copy: it was lost. Order 1017 exists in the copy and not in the source: it was invented, which usually means a load ran twice or a test record escaped.

One lost and one gained, so the count came out even. That is not a coincidence dreamed up for this page; it is the ordinary shape of a botched rerun, and it is exactly why the count agreed.

The full outer join is doing the work here, and it is worth being clear about why an inner join would not: an inner join keeps only the rows that matched, which is the set that contains neither of your problems. If joins are not yet automatic, the joins guide covers which rows each kind keeps.

Some engines lack FULL OUTER JOIN. The portable substitute is two anti-joins, one in each direction, combined with UNION ALL, using NOT EXISTS rather than NOT IN for the reason in the NULL guide.

3. Compare the values on matched rows

Rows present on both sides can still disagree, so join them and compare every column.

SELECT s.order_id, s.region, t.region, s.units, t.units
FROM orders s JOIN orders_target t USING (order_id)
WHERE s.region     IS DISTINCT FROM t.region
   OR s.units      IS DISTINCT FROM t.units
   OR s.unit_price IS DISTINCT FROM t.unit_price
   OR s.product    IS DISTINCT FROM t.product
   OR s.rep        IS DISTINCT FROM t.rep
   OR s.order_date IS DISTINCT FROM t.order_date;

1003 | East | east | 3 | 3
1010 | East | East | 5 | 4

Two rows. Order 1003 has the same numbers and a different spelling of its region. Order 1010 has five units in the source and four in the copy, which is where the missing 400 went: one desk at 220 became four units instead of five, a loss of 220, plus order 1008's 480 lost and order 1017's 300 invented. That arithmetic is worth doing out loud, because being able to explain the 400 exactly is the difference between a finding and a worry: 480 out, 300 in, 220 shaved, which nets to 400.

IS DISTINCT FROM is the operator to use rather than <>, and the difference matters. A plain <> comparison involving a null returns unknown rather than true, so a value that was populated in the source and null in the copy would not be flagged. IS DISTINCT FROM treats null as a value like any other and returns true for that case, which is exactly what a reconciliation needs.

4. One summary that says everything

The two queries above are what you look at; this is what you report. One row, four numbers, and they have to add up.

matched | changed | source_only | target_only | total_keys
     13 |       2 |           1 |           1 |         17

Thirteen rows are identical on both sides. Two exist on both and disagree. One is only in the source and one only in the target. Seventeen distinct keys were involved, which is more than either table has, because the union of two sixteen-row tables that differ by one row each way is seventeen.

Check it in both directions: 13 + 2 + 1 source-only is 16, the source count. 13 + 2 + 1 target-only is 16, the target count. If those two additions do not come out to the two row counts, the key is not unique and section ten is about that.

This table is the deliverable. "The migration is fine" is not a statement anybody can act on; "thirteen rows identical, two changed, one lost, one added, and a net difference of 400 in revenue" is a handover note, and it takes one query.

5. Column by column

Once you know two rows disagree, the useful next question is which columns, because that points at the cause rather than the symptom.

order_date | rep | region | product | units | unit_price
         0 |   0 |      1 |       0 |     1 |          0

One disagreement in region and one in units, and nothing anywhere else. That shape tells you something a list of bad rows does not: this is not a broken load, because a broken load damages a column across many rows. Two different columns each wrong on one row looks like two separate incidents, probably a manual edit and a transcription.

Contrast it with the shape you would see if, say, every date were shifted by a day: order_date would read 16 and everything else 0, and you would go looking at time zones rather than at rows. Same query, completely different conclusion, and it is the column counts rather than the row list that distinguishes them.

Say out loud what you would suspect if unit_price came back as 14 out of 16. A rounding or currency difference applied at load, and you would go and read the load code rather than the data.

6. The differences an equals sign will not see

Before the explanation: the region on order 1003 is "East" in one table and "east" in the other. Say whether a SQL comparison notices.

'East' = 'east'                  false
LOWER('East') = LOWER('east')    true

In this engine string comparison is case sensitive, so it was caught. Not every engine or column collation behaves that way, and on a case-insensitive collation that row would have been reported as matching. Which is the general lesson: a reconciliation is only as strict as its comparison. Test the comparison itself on a known difference before trusting a clean result.

Four kinds of difference routinely slip through a naive comparison. Case, as above. Trailing whitespace, where "North " and "North" differ to a computer and not to a person. Numeric precision, where 10.00 and 10.004 both display as 10.00. And dates carrying a time, where the same day at different hours compares unequal.

There is also a subtler failure worth seeing, because it is what happens when people reconcile by grouping rather than by key. Comparing region counts on the two tables gives this:

region | source_rows | target_rows
East   |           4 |           3
North  |           5 |           5
South  |           4 |           4
West   |           3 |           3
east   |        NULL |           1

A fifth region has appeared. The grand totals still agree at sixteen, the individual rows are all present, and the report now has a category nobody has ever heard of. That is the same failure mode as any near-duplicate value, and the cleaning routine for it is in finding the character you cannot see.

7. Fingerprints: one number per table

Joining two tables row by row is fine at sixteen rows and expensive at two hundred million, especially when they live in different systems and one of them has to be shipped across a network to be compared.

The cheaper move is a fingerprint: hash each row into a number and add the numbers up. Addition does not care about order, so two tables holding the same rows in different orders produce the same total.

SELECT SUM(HASH(order_id, order_date, rep, region, product, units, unit_price)) FROM orders;

source  128147106948374048609
target  136663960016580492445

Different, so the tables differ, and that took one pass over each side with nothing to transfer but a single number. It tells you nothing about where, which is the trade.

You get the where back by fingerprinting groups instead of the whole table. Here, per region:

region | same
East   | false
North  | true
South  | true
West   | false
east   | false

North and South are provably identical and need no further checking. Everything different is in East, West and the phantom region. On a large table you would fingerprint by month or by key range, find the handful of buckets that differ, and only then do the row-level comparison on those. That is the whole strategy: compare cheaply, narrow, then look closely.

One caution. Hash functions differ between engines, so a fingerprint computed in Postgres will not match one computed in SQL Server, and comparing across systems needs a hash both can compute, usually MD5 of a concatenated, consistently formatted string. Getting the formatting identical on both sides is most of that job.

8. What to do with what you find

A reconciliation that ends in a list of differences is half a job. Each category has a different next move.

And write down what you found even when it is small, because the number that matters to the person receiving the system is the money, not the rows. "Four hundred out of 9,890, which is 4.0%, in two rows, both explained" is a sentence somebody can sign off. A migration dry run exists to produce exactly that sentence before anybody depends on the new system, and the dry run guide is where this fits in the wider process.

Edge cases that break a reconciliation

Six worth knowing.

The key is not unique. If either side has the key twice, the join multiplies and every count afterwards is wrong. Check both sides first: COUNT(*) against COUNT(DISTINCT key), on each table, before joining anything.

The key is not the same key. Systems renumber. If the target assigned its own identifiers, you are matching on a natural key, and if there is not one you have crossed into entity resolution.

Legitimate differences. The target may deliberately exclude cancelled records or hold a different date format. Agree the expected differences in writing before running the comparison, or you will report them as faults.

Comparing at different moments. A live source changes while you are checking it, so a difference of a few recent rows may be time rather than error. Snapshot both sides first, which is what a temp table is for.

Floating point. Two systems can compute the same total and disagree in the fifteenth decimal place. Round explicitly to the precision the business cares about before comparing money.

Nulls compared with the wrong operator. <> silently misses a value-to-null change. Use IS DISTINCT FROM, and if your engine lacks it, COALESCE both sides to a sentinel value that cannot occur in the data.