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

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

这个话题应该很多人写过了，多数是在分析in/哪个写法效率高；not in/not 是否等价等问题。老虎刘这里再补充点内容。

先说说简单的in 和 ：

in 和 在目前的主流版本（10g及以上），是等价的。优化器会统一做查询转换。

但是如果屏蔽了某些隐含参数谈一谈in/exists ， not in/not exists，还是会不一样，曾经在客户现场遇到一个caseoracle not exists，使用的SQL，优化器没有自动做，性能很差，加了的hint后可以，改成in也可以不用加的hint就能自动做。这种情况比较少见，一般不用考虑。

需要注意的是，下面类似的写法会导致优化器不能做查询转换：

, from t1

where(=’T2′ or (  1 from  t2 where t1.=t2.));

就是 与其他谓词条件之间是OR的关系。这样就只能做操作，数据量大非常影响性能。

这种情况可以考虑使用union all改写SQL来提高性能。

再说说复杂一点的not in 和 not ：

首先的结论是：

在两个表的关联字段定义都为not null的情况下，两者的结果集才是想同的，可以等价改写。

如果关联字段定义为null，那么即使没有为null的记录，在没有加is not null谓词条件的情况下，虽然结果集是一样的，但是执行计划是不同的，效率也相差较大。

更要注意的是：如果某个表的关联字段内容是nulloracle not exists，还要看SQL的结果集是不是你要的结果集。

先做个test case：

table (id1 ,name1 (20));

table (id2 ,name2 (20));

into (1,’a1′);

into (2,’b1′);

into (3,’c1′);

into (null,’d1′);

into (2,’b2_1′);

into (2,’b2_2′);

into (3,’c2′);

into (4,’d2′);

into (null,’e2′);

not 的结果：注意返回了一条关联字段为null的记录

id1,name1  from where not

( 1 from where .id1=.id2 );

结果：

ID1 NAME1

———- ——————–

d1

1 a1

执行计划效率高，不用考虑子查询有空值的情况。建议写法。

not in的结果：因为子查询的结果集中有一条记录是null，则整个查询结果为空（这是否是你想要的结果？）

id1,name1  from

where id1 not in ( id2 from );

no rows

执行计划的join 步骤包含ANTI NA关键字，效率低

not in子查询没有null记录的情况：返回结果比not 时少了一条id1=null的记录

id1,name1  from where id1 not in

( id2 from where id2 is not null);

ID1 NAME1

———- ——————–

1 a1

执行计划的join 步骤包含ANTI SNA 关键字，效率低

下面sql的结果集与上面一样，但是执行计划却不一样谈一谈in/exists ， not in/not exists，下面的执行计划效率高：

id1,name1  from where id1 not in

( id2 from 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 和 是等价的，性能也是一样的，注意避免与其他谓词条件的OR 操作。

not in和not 在关联字段都为not null时才等价（包括性能）。一般情况使用not 比较保险，可以避免not in子查询返回包含null的记录时oracle not exists，整个结果集为空的情况（这种情况一般不是需要的结果），性能也有保障。

如果使用了not in，子查询的关联字段一般加上not null，主查询也加上not null，这样才能保证sql的高性能。

注：以上测试及结论在11.2.0.3版本环境得出。