SQL Difference between NOT IN / NOT Exist

SQL Difference between NOT IN / NOT Exist

It truly depends on the query and the data as to which is BEST.

Note that in general, NOT IN and NOT EXISTS are NOT the same!!!

SQL> select count(*) from emp where empno not in ( select mgr from emp );

COUNT(*)
----------
0

apparently there are NO rows such that an employee is not a mgr -- everyone is a mgr (or are they)

SQL> select count(*) from emp T1
2 where not exists ( select null from emp T2 where t2.mgr = t1.empno );

COUNT(*)
----------
9

Ahh, but now there are 9 people who are not managers. Beware the NULL value and NOT IN!! (also the reason why NOT IN is sometimes avoided).

NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER even -- if an "anti-join" can be used (if the subquery is known to not return nulls)

[AskTom]

Some more useful links:

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server.

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle.