1、null在in、not in操作符的左边时，结果都不是true

SQL>select 1 from dual where null in (1,2,3,null);
no rows selected
SQL>select 1 from dual where null not in (1,2,3,null);
no rows selected

2、使用not in时，如果操作符右边的集合中有null，其返回结果结果始终不是true

SQL>select 1 from dual where 5 not in (1,2,3);
         1
----------
         1
SQL>select 1 from dual where 5 not in (1,2,3,null);
no rows selected

3、对包含null字段，not in和not exists操作不再等价，not exists返回结果更多，因为包含了null值

SQL>create table t1 (n number);

Table created.

SQL>insert into t1 values (1);

1 row created.

SQL>insert into t1 values (null);

1 row created.

SQL>commit;

Commit complete.

SQL>create table t2 (n number);

Table created.

SQL>insert into t2 values (3);

1 row created.

SQL>commit;

Commit complete.

SQL>select count(*) from t1 where n not in (select n from t2);
  COUNT(*)
----------
         1

SQL>select count(*) from t1 where not exists (select 1 from t2 where t2.n = t1.n);
  COUNT(*)
----------
         2

SQL>select nvl(n,9999) from t1 where not exists (select 1 from t2 where t2.n = t1.n);
NVL(N,9999)
-----------
       9999
          1

参考资料：
Oracle数据库中的DUAL表、NULL、IN和NOT IN的使用
Oracle not in查不到应有的结果(NULL、IN、EXISTS详解)