Performance of (NOT) IN vs. (NOT) EXISTS

Hi all,

I have a query where I want to select records from (A) that exist (and in a later query, don't exist) in another table (B) like:

SELECT COUNT(*) FROM A
WHERE OBJECTID IN (

     SELECT FK_OBJEKT_ID
     FROM B);

This works fine and is actually faster than:

SELECT COUNT(*) FROM A
WHERE EXISTS (

     SELECT 1
     FROM B
     WHERE B.FK_OBJECTID = A.OBJECTID);

A.OBJECT_ID is PK and I have an index on B.FK_OBJECT_ID.

However, if I want the records from A that don't exist in B I run into problems using NOT IN like:

SELECT COUNT(*) FROM A
WHERE OBJECTID NOT IN (

     SELECT FK_OBJEKT_ID
     FROM B);

This statement takes forever to execute and the optimizer doesn't seem to use a temporary, indexed collection of FK_OBJECTID values, but rather using a full scan for each record in A. If the collection of FK_OBJECTID's was ordered/indexed, it really should not take more time to do a NOT IN than an IN. Btw, NOT EXISTS uses appr. the same amount of time as EXISTS because it is based on the same join, but I would appreciate if someone could shed some light on this NOT IN issue and its lack of performance.

Cheers,
Frank Received on Sun Oct 29 2000 - 12:25:42 CST