I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int).
Another large table (TableB) contains the same ID column with some other fields. Currently there is no foreign key constraint or unique index defined on this table (although I'm thinking about adding a primary key constraint here in any case).
The key task is to determine if any of the ID values in TableA do NOT appear in TableB.
SELECT * FROM TableA WHERE Id NOT IN (SELECT Id FROM TableB)
I timed this task on our database server to take approx. 4 minutes. Now I was wondering if there was a way to improve this.
From what I understand the lists may not be ordered BUT it's guaranteed that the Id is Unique for both tables. Is there a method I can first sort these fields (or even better have a sorted index of some kind) and then just compare those sorted lists or is there another approach to this?