not in 不能使用在子查询结果为null

not in 不能使用在子查询结果为null，导致查询没结果，又是一新发现

ask tom的测试如下

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)