Compare two tables for discrepencies

Question

I am trying to compare two tables in two separate databases (same instance) for a new data extract The tables have around 4 millions rows and due to how they've been extracted are not in the same order in each table, as they are just staging tables (there is no PKs).

I have tried using (select * from table 1 Except select * from table 2) UNION ALL (select * from table 2 Except select * from table 1) however this results in around 10 millions rows so is not working as expected.

I would guess the results should be around a few thousand.

Answer 1

If the records are unique in each table you can do something like this:

SELECT COUNT(*), FIELD1, FIELD2, FIELD3 FROM (
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 
UNION ALL 
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE2) SUBSEL
GROUP BY FIELD1, FIELD2, FIELD3
HAVING COUNT(*) = 1

Answer 2

First of all you have to define your primary key - one or several fields what will unique identify the record. All fields as primary key is possible but not recommended

Second, you have to decide what you mean as 'compare':

• records exists in first table but not in second
• records exists in both tables but has difference in non primary key fields
• records exists in the second table only