SQL>create table test1(a number);
SQL>create table test2(b varchar2(20));
SQL>insert into test1 select object_id from user_objects;
SQL>insert into test2 select object_id from user_objects;
SQL>update test2 set b = null where b >70000;
SQL>select count(1) from test1 t1 where not exists
(select 1 from test2 t2 where t1.a=t2.b);
COUNT(1)
----------
110
SQL>select count(1) from test1 where a not in
(select b from test2);
COUNT(1)
----------
0
看似相同的语义实质查询的结果不一致，原因是单列的NULL，如果非相关子查询的结果有NULL，那么整个条件为，FALSE/UNKNOWN，也就是没有结果的原因，如果深入分析下，等价于SELECT .... WHERE ID <> NULL AND ID <>....根据NULL的比较和逻辑运算规则，可以知道整个条件要么是false，要么是unknown,所以没有结果。
多说一句，IN子查询相当于OR条件，根据NULL的逻辑运算规则，哪个条件为TRUE的行就返回那个行。
通过10053事件跟踪得出以上的语句转换
not exists:
SELECT COUNT(*) "COUNT(1)"
FROM "TEST"."TEST2" "T2", "TEST"."TEST1" "T1"
WHERE "T1"."A" = TO_NUMBER("T2"."B");
not in:
SELECT COUNT(*) "COUNT(1)"
FROM "TEST"."TEST1" "SYS_ALIAS_1"
WHERE NOT EXISTS
(SELECT /*+ */ 0 FROM "TEST"."TEST2" "TEST2"
WHERE LNNVL(TO_NUMBER("TEST2"."B") <> "SYS_ALIAS_1"."A"));
注：lnnvl用于某个语句的where子句中的条件，如果条件为true就返回false；如果条件为UNKNOWN或者false就返回true。该函数不能用于复合条件如AND, OR, or BETWEEN中。
根据10053事件可以推导出，如果一定要用not in,可以改写为
SQL> select count(1) from test1
where a not in (select b from test2 where test1.a=test2.b);
COUNT(1)
----------
110