exist 和 in 的使用

首先需要说明一下的是，以下的文字基本上都是google来的，只是经过了我的整理而已。更加适合自己的胃口理解

 

关于exist对我来说有必要专门记一下，因为以前不是很熟悉

select * from 表A where exists(select * from 表B where 表B.id=表A.id)
这句相当于
select * from 表A where id in (select id from 表B)
对于表A的每一条数据,都执行select * from 表B where 表B.id=表A.id的存在性判断,如果表B中存在表A当前行相同的id,则exists为真

,该行显示,否则不显示
使用 EXISTS 和 NOT EXISTS 引入的子查询可用于两种集合原理的操作：交集与差集。两个集合的交集包含同时属于两个原集合的所有

元素。差集包含只属于两个集合中的第一个集合的元素。

从原理上说，in 是一个集合运算符。
a in {a,c,d,s,d....}
这个运算中,前面是一个元素,后面是一个集合,集合中的元素类型是和前面的元素一样的.
而exists是一个存在判断,如果后面的查询中有结果,则exists为真,否则为假。
in 运算用在语句中,它后面带的select 一定是选一个字段,而不是select *.

比如说你要判断某班是否存在一个名为"小明"的学生,你可以用in 运算:
"小明" in (select sname from student)
这样(select sname from student) 返回的是一个全班姓名的集合,in用于判断"小明"是否为此集合中的一个数据。
同时,你也可以用exists语句:exists (select * from student where sname="小明")

 处理方式上：in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。（于是想到了之前dba说过的——网上盛传的exists比in效率高的说法是不准确的。）

 

比如Select * from T1 where x in ( select y from T2 )

执行的过程相当于:

select *

  from t1, ( select distinct y from t2 ) t2

where t1.x = t2.y;

 

相对的

 

select * from t1 where exists ( select null from t2 where y = x )

执行的过程相当于:

for x in ( select * from t1 )

   loop

      if ( exists ( select null from t2 where y = x.x )

      then

         OUTPUT THE RECORD

      end if

end loop

表 T1 不可避免的要被完全扫描一遍

 

分别适用在什么情况?

以子查询 ( select y from T2 )为考虑方向

如果子查询的结果集很大需要消耗很多时间，但是T1比较小执行( select null from t2 where y = x.x )非常快，那么exists就比较适合用在这里。

相对应得子查询的结果集比较小的时候就应该使用in.

 

如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：

例如：表A（小表），表B（大表）

 

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列的索引。

相反的

 

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

如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；

而not extsts 的子查询依然能用到表上的索引。

所以无论那个表大，用not exists都比not in要快。

in 与 =的区别

select name from student where name in ('zhang','wang','li','zhao');

与

select name from student where name='zhang' or name='li' or name='wang' or name='zhao'

的结果是相同的。

 

总结：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

参考连接：http://kayo.javaeye.com/blog/192129