第六种情况：in/exists的子查询中无NULL值，外层查询也无NULL值
linuxidc@ORA11G>select * from t1 where id in (select id from t2);
ID
----------
3
4
2 rows selected.
linuxidc@ORA11G>select * from t1 where exists(select 1 from t2 where t1.id=t2.id);
ID
----------
3
4
2 rows selected.
第七种情况：in/exists的子查询中有NULL值，外层查询无NULL值
linuxidc@ORA11G>insert into t2 values(null);
1 row created.
linuxidc@ORA11G>commit;
Commit complete.
linuxidc@ORA11G>select * from t1 where id in (select id from t2);
ID
----------
3
4
2 rows selected.
linuxidc@ORA11G>select * from t1 where exists(select 1 from t2 where t1.id=t2.id);
ID
----------
3
4
2 rows selected.
第八种情况：not in/not exists的子查询中有NULL值，外层查询无NULL值
linuxidc@ORA11G>select * from t1 where id not in (select id from t2);
no rows selected
linuxidc@ORA11G>select * from t1 where not exists(select 1 from t2 where t1.id=t2.id);
ID
----------
1
2
2 rows selected.
从上面的八种情况我们可以总结如下：
1、in和exists在有无NULL的情况下可以相互转换。
2、not in和not exists在都没有NULL值的情况下才可以相互转换。