in,exists,not in ,not exists效率

一、in和exists效率说明

in

确定给定的值是否与子查询或列表中的值相匹配。
系统在执行in子查询时，首先执行子查询，并将获得的结果 列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。

exists

指定一个子查询，检测行的存在。
通过使用 exists，系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。

一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，驱动表小的用in；in 使用了驱动表的索引，exists 使用了查询表的索引。
例如：表A（小表），表B（大表）

1、in 适用于外表小，内表大

select * from A where cc in (select cc from B)

效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)

效率高，用到了B表上cc列的索引。

2、exists 适用于外表小，内表大

select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。

二、not in 和 not exists说明

1、not in 并不等价于not exists

例如下面这两个句子，只有在t1.tcode不为NULL的时候，两者返回的记录才是相同的，大家在用not exists来替换not in 时可能会忽略掉这一点

 select t1.*
  from test1 t1
 where not exists (select t2.tcode from test2 t2 where t2.tcode = t1.tcode);

 select t1.* from test1 t1 where t1.tcode not in (select t2.tcode from test2 t2);

 分析一下原因：假如 select t2.tcode from test2 t2 的查询结果是 tcode1 和 null
 将句子 select t1.* from test1 t1 where t1.tcode not in('tcode1',null);
 可以转换为 select t1.* from test1 t1 where t1.code <> 'tcode1' and t1.code <> null;
 t1.code <> null 会将所有结果不为空的过滤掉，那么结果自然为空了。
 not exists语句不会出现这种情况，因为not exists子句中写的是t2.tcode与t1.tcode的关联，null是不参与等值关联的，所以test2的tcode存在空值对最终的查询结果没有任何影响。
 
 由此可以得出结论：
 (1)、对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。
 (2)、对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。

 2、效率上使用not exists来代替not in

效率上应尽可能使用not exists来代替not in，尽管二者都使用了not（不能使用索引而降低速度），not exists要比not in查询效率更高。
not exists语句是一个简单的两表关联，内表与外表中存在空值本身就不参与关联，在CBO(基于成本的优化器)中常用的执行计划是hash join；
not in 中因为内表或外表中存在空值对最终结果产生的影响是hash join无法实现的，因为hash join不支持把空值放到hash中，所以它没办法处理外表和内表中存在的空值;not in 的执行计划是filter，类似nested loop；

对于hash join来说，它的时间复杂度大致相当于两个for循环（并非嵌套）O(n+m)(n,m表示内外表扫描的时间复杂度，也可以简单的理解为是内外表符合条件的行数，虽然有些不恰当)，为什么这么说呢？因为hash join是先把内表的结果集全部算出来，完了之后再对外表做一个全表扫描。所以说是O(n+m)。nested loops，对于内表的符合条件的每行，都会在外表去扫描一下，看是外表否有符合条件的行。这个就相当于两个for循环嵌套了时间复杂度O(n*m)。所以在一般情况下O(n*m) 肯定是大于 O(n+m) 的，所以通常not exists要比not in查询效率更高。