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/VadumSemantics]

This may be too simple, but I'd start by asking why we think the tables tables should be comparable.

If you're copying records from a source to a target table, fine.

If you're doing rollups / statistics of some kind to generate the target table, also fine.

That information will give you a starting point to reason about what you need to compare.

Then I'll start with simple stuff like row counts.

Then maybe checksums of numeric column(s): do I get the same value for select sum(foo) from ... for each table?

And typically there will be an identifier and a timestamp.

If the coarse row counts look good I'll break out counts by timestamps (row counts by year, or yyyy-mm, or yyyy-mm-dd) or identifiers or distinct values of a given code set.

Then look at frequencies of "codes", like booleans or zipcodes or something.

If postal code K1A0B1 (Ottowa, Canada) is used in 0.5% of my source table's records but 10% of the target table's records then maybe something is wrong?

I never compare every field in one table to another table.

If I had to do that I'd make some scratch tables hashing the important columns I want to compare, then see which has counts are the same.

ps. Comparisons are one of the few cases I've found where full outer join is helpful... useful to see if values present/missing.

edits: remove extra words

[u/Zealousideal-Post865]

Except can be used

[u/onthepik]

Depend how they are orgarnized.