I have a large table (TableA) with about 120 million. Records. This table contains a single primary key column (type: int).
Another large table (Table B) contains the same ID column with some other fields. There is currently no foreign key restriction or unique index defined in this table (although I definitely want to consider adding a primary key restriction here).
The main task is to determine whether one of the ID values in Table A is NOT shown in Table B.
SELECT * FROM TableA WHERE Id NOT IN (SELECT Id FROM TableB)
I have this task on our database server for about 4 minutes. Now I wondered if there was a way to improve it. As far as I know, the lists may not be orderable, BUT it is guaranteed that the ID is unique for both tables. Is there a method I can use to sort these fields first (or better still have a sorted index) and then simply compare those sorted lists, or is there another approach to this?