Sql - Not Exists Query With Millions Of Records

I'm trying to use the following SQL query (in SAS) to find any records from pool1 that do not exist in pool2. Pool1 has 11,000,000 records, pool2 has 700,000. This is where I run i

Solution 1:

PROC SQL;
CREATE TABLEALL AS
SELECT A.IDFROM
        POOL1 A
    WHERE A.ID NOT IN (SELECT B.IDFROM
                            POOL2 B)
                        ;

The above change should return the same result set but take considerably less time to run as you are not trying to join POOL2 back to POOL1 but simply excluding results which exist in POOL2.

As stated in another answer, an INDEX may help but if the ID fields are the primary keys it is likely they are already subject to in INDEX.

Solution 2:

Your query is fine. In most databases, not exists is the best way (or one of the best ways) to express this logic.

However, you need an index for performance:

create index idx_pool2_id onpool2(id);

Solution 3:

You're doing this in SAS, so why not use a data step?

data all;
  merge pool1(in = a) pool2(in = b keep = ID);
  by ID;
  if a and not(b);
run;

This requires that both datasets are either sorted or indexed by ID. If you have multiple records per ID in B then I would suggest deduplicating first via

proc sort data = pool2 out = temp nodupkey;
  by id;
run;

Solution 4:

PROC SQL;
CREATETABLEALLASSELECT A.ID
  FROM POOL1 A
       LEFTJOIN POOL2 B ON B.ID = A.ID
 WHERE B.ID ISNULL