Oracle数据库中 in与exists语句的区别

（一）in的理解

如sql：Select * from T1 where x in ( select y from T2 )中，先执行select y from T2，等价于Select* from T1,( select distinct y from T2) T2 whereT1.x=T2.y。可以理解为：先进行子查询，结果集合放到缓存中，进行一定的处理（evaluated, distincted, indexed），然后于T1表连接查询，也可以理解为：

 

定义：优化（）={ evaluated,distincted, indexed }

--在缓存中进优化处理，如果集合比较小，优化代价很小，不用每次都到表T2中取值，直接在内存获取，比较快。

List resultSet=[];
Array T1=(select * from T1);
Array T2=(select y from T2);

Array T3=优化（T3）--网上资料很多都没有这一步，这一步其实是 in的优势所在，也是缺陷所在，随着子表的增大，优化的代价越来越高，因为T3的每个y值都要对T1进行全表扫描。

for(int i=0;i<T1.length;i++) {--每次循环，T1全表扫面
      for(int j=0;j<T3.length;j++){--如果y为主键时，T3.length等于T2.length，但是在取y值从内存取，速度要比从表快。
         if(T1 [i].x ==T3 [j].y) {
            resultSet.add(T1 [i]);
            break;
      }
   }
}
return resultSet;

分析：

遍历次数=T3.length*T1.length，in（）比较适合用于外查询大于内查询的。建议在外查询建立索引，因为避免每次对T1进行全表扫描。

（二）exists的理解

如sql：Select * from T1 where exists  ( select 1 from T2 where y=x )中，可以理解为

for x in（select* from T1）

   loop

     if (exists  ( select 1 from T2 where y=x.x ))

      then

          (....outputthe record)

     end if;

   end loop;

---

（1）对T1中每条记录执行exists  ( select 1 from T2 where y=x.x )返回值为true或者false。

（2）如果select 1 from T2 where y=x.x查询有存在值则返回true，并把T1中对应的行记录放入结果表（临时表）中；

（3）如果返回false，T1中记录不作处理，指针指向下一条T1中的记录；

（4）重复（1）（2）（3）直到遍历完T1表，输出结果表中的记录。

分析：

T1中有几条记录就需要执行几次select 1 from T2 where y=x.x，如果T1表很大，而且T2没有索引的话需要对T2表进行全表遍历T1.length（T1表中记录数）次，代价好大。如果T1较小，T2有索引则exists效率比较高。

（三）总结

（1）外大于内时用IN，这种说法不恰当，确切的说应该是外查询数量远大于内查询，而且外查询有索引时用IN，数据数量级越大效果越明显。

（2）内大于外用EXISTS也不恰当，应该是内查询数量远大于外查询，同时内查询有索引的有索引的情况下，EXISTS效率要高，数据数量级越大效果越明显。

（3）如果内外数据量接近，IN和EXISTS效率应该看索引、服务器性能（如缓存大小、cpu性能）等情况。

 (4)在10g之后的版本，默认都是以基于成本的方式进行，这时候，oracle会先找出可能的执行方式，然后计算出每个执行计划的成本，再选择以较低成本的方式进行计算，这样子在对in和exists的分析中，这两种写法会相互转换，那个统计的成本信息低则会选择那种方式。当然由于oracle的成本信息并不是全量统计得出来的结果，也会有一定的误差，再统计信息是需要人工（或定时）去执行统计的，如果操作大量数据后，没有进行统计，偏差也会很大。