oracle中exists和in的问题

今天在做sql练习的时候遇到了查询出客户表中重名的人(以lastname进行判断)的问题，本来第一感觉是应该用in来做，但是突然又想起昨天看了一篇有关sql优化的文章，其中建议用exists代替in()以提高速度。虽然很容易的写出了用exists的查询，但是其中的道理依然没有弄明白。

现在查阅了几篇前辈的博文算是有点头绪了，下面就这个查询重名的问题为例

--1.  请查询出客户表中重名的人(以lastname进行判断)
--使用exits
select * from customers c1
where exists(
      select lastname 
      from customers c2
      group by c2.lastname
      having count(*)>1 and c1.lastname = c2.lastname
);

--使用in()
select * from customers c1
where c1.lastname in(
      select lastname 
      from customers c2
      group by c2.lastname
      having count(*)>1
);

一、使用in（）语句，程序的过程可理解为：

List resultSet=[];
Array c1=(select * from customers);Array c2=(select lastname from customers group by lastname having count(*)>1);
for(int i=0;i<c1.length;i++) {
   for(int j=0;j<c2.length;j++) {
      if(c1[i].id==c2[j].id) {
         resultSet.add(c1[i]);
         break;
      }
   }
}
return resultSet;

在这里in()子句中的查询只会执行一次，将查找到的lastnam缓存起来，然后与c1中的lastname逐一比对，如果相等，则将这条数据存入结果集，直到遍历完c1中的所有记录。

如:c1表有10000条记录,c2表有1000000条记录,那么最多有可能遍历10000*1000000次,效率很差.

再如:c1表有10000条记录,c2表有100条记录,那么最多有可能遍历10000*100次,遍历次数大大减少,效率大大提升.

结论:in()适合C2表比C1表数据小的情况可以看出,当C2表数据较大时不适合使用in(),因为它会C2表数据全部遍历一次.

二、使用in（）语句，程序的过程可理解为：

List resultSet=[];
Array c1=(select * from customers)

for(int i=0;i<c1.length;i++) {
    if(exists(c1[i].lastname) {    //执行select 字句是否有记录返回
       resultSet.add(c1[i]);
    }
}
return resultSet;

当C2表比C1表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.
如:C1表有10000条记录,C2表有1000000条记录,那么exists()会执行10000次去判断C1表中的lastname是否与C2表中的lastname相等.
如:C1表有10000条记录,C2表有100000000条记录,那么exists()还是执行10000次,因为它只执行C1.length次,可见B表数据越多,越适合exists()发挥效果.
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

结论:exists()适合B表比A表数据大的情况

对于in 和 exists的性能区别: 

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。 

其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了