谈一谈in/exists ， not in/not exists

这个话题应该很多人写过了，多数是在分析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版本环境得出。