CNDBA社区
本文已获原作者授权转载。文章出自微信公众帐号：老虎刘谈SQL优化
作者介绍：老虎刘,原oracle 研发部门 Real-World Performance TEAM 成员，现在售后部门SSC专职做数据库性能优化，主要为银行、通信、证券、制造等大型企业提供服务。
这个话题应该很多人写过了，多数是在分析in/exists哪个写法效率高；not in/not exists是否等价等问题。老虎刘这里再补充点内容。
先说说简单的in 和 exists：
in 和 exists 在目前的主流版本（10g及以上），是等价的。优化器会统一做查询转换。
但是如果屏蔽了某些隐含参数，还是会不一样，曾经在客户现场遇到一个case，使用exists的SQL，优化器没有自动做unnest，性能很差，加了unnest的hint后可以，改成in也可以不用加unnest的hint就能自动做unnest。这种情况比较少见，一般不用考虑。
需要注意的是，下面类似的写法会导致优化器不能做查询转换：
select object_id ,object_name from t1 where(object_name='T2' or exists (select 1 from t2 where t1.object_id=t2.object_id));就是exists 与其他谓词条件之间是OR的关系。这样就只能做filter操作，数据量大非常影响性能。
这种情况可以考虑使用union all改写SQL来提高性能。
再说说复杂一点的not in 和 not exists：
首先的结论是：
在两个表的关联字段定义都为not null的情况下，两者的结果集才是想同的，可以等价改写。
如果关联字段定义为null，那么即使没有为null的记录，在没有加is not null谓词条件的情况下，虽然结果集是一样的，但是执行计划是不同的，效率也相差较大。
更要注意的是：如果某个表的关联字段内容是null，还要看SQL的结果集是不是你要的结果集。
先做个test case：
create table tnull_1 (id1 number,name1 varchar2(20));
create table tnull_2 (id2 number,name2 varchar2(20));
insert into tnull_1 values(1,'a1');
insert into tnull_1 values(2,'b1');
insert into tnull_1 values(3,'c1');
insert into tnull_1 values(null,'d1');
commit;
insert into tnull_2 values(2,'b2_1');
insert into tnull_2 values(2,'b2_2');
insert into tnull_2 values(3,'c2');
insert into tnull_2 values(4,'d2');
insert into tnull_2 values(null,'e2');
commit;not exists的结果：注意返回了一条关联字段为null的记录
select id1,name1 from tnull_1 where not exists
(select 1 from tnull_2 where tnull_1.id1=tnull_2.id2 );
结果：
ID1 NAME1
---------- --------------------
d1
1 a1执行计划效率高，不用考虑子查询有空值的情况。建议写法。
not in的结果：因为子查询的结果集中有一条记录是null，则整个查询结果为空（这是否是你想要的结果？）
select id1,name1 from tnull_1
where id1 not in (select id2 from tnull_2 );
no rows selected执行计划的join 步骤包含ANTI NA关键字，效率低
not in子查询没有null记录的情况：返回结果比not exists时少了一条id1=null的记录
select id1,name1 from tnull_1 where id1 not in
(select id2 from tnull_2 where id2 is not null);
ID1 NAME1
---------- --------------------
1 a1执行计划的join 步骤包含ANTI SNA 关键字，效率低
下面sql的结果集与上面一样，但是执行计划却不一样，下面的执行计划效率高：
select id1,name1 from tnull_1 where id1 not in
(select id2 from tnull_2 where id2 is not null) and id1 is not null;
ID1 NAME1
---------- --------------------
1 a1执行计划的join 步骤只包含ANTI 关键字，效率高
建议：
如果你的表定义没有做not null的限制，那么建议你在写not in SQL时加两个is not null。
总结：
in 和 exists 是等价的，性能也是一样的，注意避免与其他谓词条件的OR 操作。
not in和not exists在关联字段都为not null时才等价（包括性能）。一般情况使用not exists比较保险，可以避免not in子查询返回包含null的记录时，整个结果集为空的情况（这种情况一般不是需要的结果），性能也有保障。
如果使用了not in，子查询的关联字段一般加上not null，主查询也加上not null，这样才能保证sql的高性能。
注：以上测试及结论在11.2.0.3版本环境得出。
