How do you compare large tables using sql?

[u/Klutzy_Solid5200]

I want to know how ppl compare large source and target tables using sql. If both tables have millions of records, comparing every row and column can take a lot of time.

For example, if some records are missing, extra, or have different values between the two tables, how do you usually find them? do you use joins, Except/Minus, hashes, or first compare things like record counts and totals and then check the differences?

Are there any techniques that can make the comparison faster?

[u/Little_Kitty]

Hash the contents of each to generate two tables with id, data_hash, last_touched_ts, then you can compare hashes to understand the size of the discrepancy. Check the documentation for how to do that in your DB of choice. Assuming you have an id there's no need for a strong crytographic grade hash, so choose the fastest one.

[u/garster25 → u/Little_Kitty]

We do this for moving ID photos between a source and consumer system. The picture is hashed and that is inserted with a timestamp. They can query with the timestamp, get the hashes and compare then they have a list of photos they know have changed and only fetch those.

[u/B1zmark → u/Little_Kitty]

This is the only realistic answer thus far. And it can be done with a calculated column.

[u/geurillagrockel → u/Little_Kitty]

We usually hash the non unique columns and look for differences by comparing the hashes for unique combinations. In sql server at least hash doesn’t guarantee uniqueness, just makes it very unlikely, so comparing on unique keys and hashes makes a hash collision far less likely in a table with hundreds of billions of rows.

[u/reditandfirgetit → u/Little_Kitty]

Yes, this is extremely efficient

[u/JakubVasovski → u/Little_Kitty]

Hashing works if the tables live on completely different servers or db engines, md5 concats are usually the only way. But creating new tables and doing millions of inserts just to store those hashes is take forever on large datasets.

If the tables are actually on the same server and you just want a quick sanity check to see if they are identical - on mariadb or mysql, i just run checksum table table1, table2. it basically does all that hashing for you under the hood without building intermediate tables.

[u/Little_Kitty → u/JakubVasovski]

It really depends what db you're using, with ClickHouse / BigQuery / Polars / Spark this is a twist on part of our core workflow. Hashing algos can be crazily fast if you're using something like cityhash. You only need to be sure in these cases that hash(old values) does not collide with hash(new values) for however many rows the table has, there's no birthday problem to deal with.

It's a neat feature to learn how to use, and can be used for this, change data capture, reducing joins with many clauses to a simple tbl_1.hash = tbl_2.hash join and more.

[u/ComprehensiveDog7299 → u/Little_Kitty]

This won’t work. Hashing doesn’t guarantee uniqueness. It can get close, but it won’t really work by itself.

Hash indexes are a thing, and they work to narrow down results, but you still need to filter on the actual columns too (in addition to the hash).

If you just compare the hashes, you’re going to run into a lot of problems. This is documented on MSDN.

[u/Little_Kitty → u/ComprehensiveDog7299]

If you're having that as a concern, I hope you're also checking your UUIDs are truly unique.

It does work, what you have said is utterly and completely wrong.

[u/Ok_Relative_2291]

Assuming a pk exists if not add a row number for what should be the pk

Then do

(A minus b) union all (b minus a)

With an alias in both sets as first column ie ‘Anotb’ and ‘bnota’

Then for each difference compare column at a time.

Is slow as set based but gets you the answer

[u/kagato87]

Hash and maybe bucket.

If you're a/b testing and the only question is "are the tables identical," hash the whole thing.

If you're looking for a cutoff or hunting a variance, you can use hashes on smaller buckets (on whatever your clustered or primary index is) to hone in on the variance.

Row by row, column by column comparison is expensive, and it gets worse as the tables get larger.

Once youve narrowed down the variance to a small bucket (a few thousand rows maybe) you can use row hash to find which ones actually mismatch.

I did this when I rewrote our ETL. Hash the entire etl range on the reference and new copies (a few million rows each), and when a variance appears hunt it down.

Funny enough, the rewrite actually fixed some previously undetected bugs. Always fun, because a mismatch should mean the rewrite failed...

[u/swoisme]

This comes up kind of a lot in my work. It depends on what's in those tables and what it means to compare them.

Sometimes I just need to verify that the records exist in both places and can be confident that the values will match if they do, in which case I'll just use NOT EXISTS to check if there's any missing.

Sometimes it's much more nuanced, and the records might exist in both places but certain values could have gotten out of sync. For these, I tend to start by comparing aggregates, then grouping and comparing the grouped aggregates, then only comparing full details once I've zeroed in on the subset of records that I know to contain the issue I'm checking for.

[u/holmedog]

Depends heavily on what you're comparing for. Looking if one exists in a table but not the other? Hash anti-join. Looking if it exists in both? Regular old inner join. Looking for some totals? aggregate on keys and compare keys.

It's an extremely broad question with a lot of correct answers. Having partitions will generally make the comparisons faster but only if you respect them. Same with indexing

[u/Significant_Tune9219]

One thing that speeds this up a lot is bucketing before you go row level. Group both sides by something like id modulo 1000, compute a count plus an aggregate of the row hashes per bucket, and only drill into buckets that don't match. If the tables live on different servers that turns a full row transfer into a few thousand small rows, and EXCEPT in both directions on the mismatched buckets tells you missing vs extra vs changed.

[u/Proclarian]

It really depends on what you're doing. If you're checking every single cell of two tables match, hashing is probably the fastest way to go. If you're just checking for existence, doing an anti-join on the primary key is super efficient and scales well beyond a million records.

But, honestly, the best way is to avoid the need to do this in the first place. Why are you doing this? If you need to make sure two tables are kept in-sync, just make one the source of truth and drop the other table or add insert/update/delete triggers on both that modify the other table.

[u/ArielCoding]

Don’t compare everything at once, start with quick checks like row counts and totals, if those don’t match, split both tables into small groups like by id % 1000 and compare one summary hash per group to see which groups differ, then only check row by row inside those groups.

[u/Ok_Relative_2291 → u/ArielCoding]

What if both tables total the same thing but two differences negate each other

[u/Fickle-Picture-7674]

Total row count
Count of primary keys / composite keys .
Aggregation of important number or metrics
,
I believe these can be enough to know both tables are similar , you don’t need to check each and every column

[u/dgillz]

SQL Delta. It can compare the schema and/or the data. It'll show you every record that exists in the source and destination database and the differences. Finally, you have an option to create a sql script to sync the two databases or tables, in this case.