SQL中exist与in 的区别

今天在网上查资料看sql中exist与in 的区别,发现众说纷纭,有几种说法:

1. exist效率比in高,凡是in都应该用exist替换
2. 外大内小用in,外小内大用exist
3. 外大内小用exist,外小内大用in
 
一时分辨不了哪个正确,于是动手检验.
 
数据库: ORACLE 10G
客户端: PlSqlDev 7.1
 
1. 外大内小的情况:
history.tb_stk_cap_chg 记录数 > 100,000,000
history.tb_stk_cap_chg_test 记录数 = 20
 
--使用in
select count(*)
  from history.tb_stk_cap_chg a
 where a.tradedate = '20060712'
   and a.br_serial_no in
       (select b.br_serial_no from history.tb_stk_cap_chg_test b)
   
1 row selected in 21.188 seconds
 
--使用exists
select count(*)
  from history.tb_stk_cap_chg a
 where a.tradedate = '20060712'
   and exists (select null
          from history.tb_stk_cap_chg_test b
         where a.br_serial_no = b.br_serial_no)
 
 1 row selected in 0.5 seconds
 
可以看出外大内小用in效率极低,用exists效率很高
 
2. 外小内大的情况:
history.tb_stk_cap_chg 记录数 > 100,000,000
history.tb_stk_cap_chg_test 记录数 = 1,000,000
 
--使用in
 select count(*)
  from history.tb_stk_cap_chg_test a
 where a.br_serial_no in
       (select b.br_serial_no from history.tb_stk_cap_chg b)

 1 row selected in 9.187 seconds
 
--使用exists
select count(*)
  from history.tb_stk_cap_chg_test a
 where exists (select null
          from history.tb_stk_cap_chg b
         where a.br_serial_no = b.br_serial_no)
 
  1 row selected in 10.359 seconds
 
 注: 当history.tb_stk_cap_chg_test 记录数 < 1,000,000时得出的时间差别很小,不容易判断
 
可以看出外小内大时使用in比exists效率更高
 
----------------------------------------------------------------------------------
在改变history.tb_stk_cap_chg_test记录数(10-1,000,000)进行测试(>5次)得出的结论与上述结论符合,在此不一一列出
 
由此得出结论: exits适合内小外大的查询，in适合内大外小的查询
----------------------------------------------------------------------------------
 
附上一篇觉得正确的文章,欢迎探讨:
in 和 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="小明")

这两个涵数是差不多的, 但是由于优化方案的不同, 通常NOT EXISTS要比NOT IN 要快, 因为NOT EXISTS可以使用结合算法而NOT IN　就不行了，而EXISTS则不如IN快, 因为这时候IN可能更多的使用结合算法.

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为真,该行显示,否则不显示

exits适合内小外大的查询，in适合内大外小的查询

IN 
确定给定的值是否与子查询或列表中的值相匹配。

EXISTS 
指定一个子查询，检测行的存在。

比较使用 EXISTS 和 IN 的查询

这个例子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN。注意两个查询返回相同的信息。

USE pubs 
GO 
SELECT DISTINCT pub_name 
FROM publishers 
WHERE EXISTS 
(SELECT * 
FROM titles 
WHERE pub_id = publishers.pub_id 
AND type = 'business') 
GO

-- Or, using the IN clause:

USE pubs 
GO 
SELECT distinct pub_name 
FROM publishers 
WHERE pub_id IN 
(SELECT pub_id 
FROM titles 
WHERE type = 'business') 
GO

下面是任一查询的结果集：

pub_name 
---------------------------------------- 
Algodata Infosystems 
New Moon Books

(2 row(s) affected)

exits 相当于存在量词：表示集合存在,也就是集合不为空只作用一个集合.例如 exist P 表示P不空时为真; not exist P表示p为空时 为真 in表示一个标量和一元关系的关系。例如：s in P表示当s与P中的某个值相等时 为真; s not in P 表示s与P中的每一个值都不相等时 为真

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/change888/archive/2008/03/31/2232778.aspx