IN 和EXISTS 究竟有啥区别？

aoyihuashao 2010-06-01 03:24:08
查了很多资料，不能自圆其说。

下面是我的实验，能把各种情况都解释清楚的回答，随便复制的就算啦。

三个表 T1（key1 pk,col1）,T2(key2 pk,key1 fk,col2),T3(key3 pk,key1 fk).
插入数据，T1 10000条 T2 20000条 T3 100条
测试数据在后面。

执行语句：
SET STATISTICS IO ON

SELECT * FROM T3 WHERE KEY1 IN (SELECT KEY1 FROM T1 WHERE KEY1 = T3.KEY1)

SELECT * FROM T3 WHERE EXISTS (SELECT 1 FROM T1 WHERE KEY1 = T3.KEY1 )

SELECT * FROM T1 WHERE KEY1 IN (SELECT KEY1 FROM T3 WHERE KEY1 = T1.KEY1)

SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T3 WHERE KEY1 = T1.KEY1 )



SELECT * FROM T1 WHERE KEY1 IN (SELECT KEY1 FROM T2 WHERE KEY1 = T1.KEY1)

SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE KEY1 = T1.KEY1 )

SELECT * FROM T2 WHERE KEY1 IN (SELECT KEY1 FROM T1 WHERE KEY1 = T2.KEY1)

SELECT * FROM T2 WHERE EXISTS (SELECT 1 FROM T1 WHERE KEY1 = T2.KEY1 )

SET STATISTICS IO OFF

结果：

（所影响的行数为 99 行）

表 'T1'。扫描计数 99，逻辑读 213 次，物理读 0 次，预读 0 次。
表 'T3'。扫描计数 1，逻辑读 1 次，物理读 0 次，预读 0 次。

（所影响的行数为 99 行）

表 'T1'。扫描计数 99，逻辑读 213 次，物理读 0 次，预读 0 次。
表 'T3'。扫描计数 1，逻辑读 1 次，物理读 0 次，预读 0 次。

（所影响的行数为 99 行）

表 'T3'。扫描计数 1，逻辑读 1 次，物理读 0 次，预读 0 次。
表 'T1'。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。

（所影响的行数为 99 行）

表 'T1'。扫描计数 99，逻辑读 213 次，物理读 0 次，预读 0 次。
表 'T3'。扫描计数 1，逻辑读 1 次，物理读 0 次，预读 0 次。

（所影响的行数为 9999 行）

表 'T1'。扫描计数 1，逻辑读 27 次，物理读 0 次，预读 0 次。
表 'T2'。扫描计数 1，逻辑读 63 次，物理读 0 次，预读 0 次。

（所影响的行数为 9999 行）

表 'T1'。扫描计数 1，逻辑读 27 次，物理读 0 次，预读 0 次。
表 'T2'。扫描计数 1，逻辑读 63 次，物理读 0 次，预读 0 次。

（所影响的行数为 19998 行）

表 'T2'。扫描计数 1，逻辑读 63 次，物理读 0 次，预读 0 次。
表 'T1'。扫描计数 1，逻辑读 27 次，物理读 0 次，预读 0 次。

（所影响的行数为 19998 行）

表 'T2'。扫描计数 1，逻辑读 63 次，物理读 0 次，预读 0 次。
表 'T1'。扫描计数 1，逻辑读 27 次，物理读 0 次，预读 0 次。



执行计划：

2%
2%
6%
3%
23%
23%
20%
20%


测试数据
declare @i int
set @i = 1
declare @s varchar(20)

while(@i<10000)
begin
set @s = CAST(@i as varchar(20))
insert into t1 values(@i,@s)
insert into t2 values(@i*2,@i,@s)
insert into t2 values(@i2*2+1,@i,@s)
set @i = @i + 1
end

set @i = 1
while(@i<100)
begin
insert into t3 values(@i*3,@i)
end
...全文
226 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
njlywy 2010-06-03
  • 打赏
  • 举报
 回复
确定值与范围…
东那个升 2010-06-03
  • 打赏
  • 举报
 回复
2005以后in和exists 的查询计划一样的。没区别了
jianuMan 2010-06-03
  • 打赏
  • 举报
 回复
IN 和 exists不同
in 说明 某个字段的值 在in后面的一个集合里面 将符合该条件的行 筛选出来

exists 表示 存在某些条件的数据
brownhwy 2010-06-03
  • 打赏
  • 举报
 回复
mark.我也为in 和 exists迷惑，不过我没有楼主研究得那么深入。在此学习。
ShenLiang2025 2010-06-01
  • 打赏
  • 举报
 回复 






MS SQL 2005上 测试结果没有什么差别.上面的测试查询可以等价于Join.



1)简单的说,In相当于OR.而查询的结果是依赖于Inner的查询遍历而遍历Onter的.

而Exists则是根据Outer的查询而逐条执行.一旦查到符合的即结束,不会遍历后面的.



2)针对上述的查询 可见不论T1 T2还是T3随便2者的组合,Exists和In时,MS SQL选择的都是对两表的

Table Scan.并选择了Hash Join,并最终实现了Right Semi Join(右半连接).返回结果.
sp4 2010-06-01
  • 打赏
  • 举报
 回复
实现返回集合的方式可能有多种！
建议exists尽量替代in
feilniu 2010-06-01
  • 打赏
  • 举报
 回复 


SELECT * FROM T1 WHERE KEY1 IN (SELECT KEY1 FROM T3)

SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T3 WHERE KEY1 = T1.KEY1 )

SELECT * FROM T3 WHERE KEY1 IN (SELECT KEY1 FROM T1)

SELECT * FROM T3 WHERE EXISTS (SELECT 1 FROM T1 WHERE KEY1 = T3.KEY1 )

SELECT * FROM T1 WHERE KEY1 IN (SELECT KEY1 FROM T2)

SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T2 WHERE KEY1 = T1.KEY1 )

SELECT * FROM T2 WHERE KEY1 IN (SELECT KEY1 FROM T1)

SELECT * FROM T2 WHERE EXISTS (SELECT 1 FROM T1 WHERE KEY1 = T2.KEY1 )

以上测试语句的查询计划：
3%
3%
2%
2%
23%
23%
23%
23%

至少我根据LZ的数据测试的结果：IN和EXISTS性能相同，查询计划也是一样的。
--小F-- 2010-06-01
  • 打赏
  • 举报
 回复 
　本文主要分析了in和exists的区别与执行效率的问题： 



　　in可以分为三类： 

　　1、形如select * from t1 where f1 in ( ' a ' , ' b ' )，应该和以下两种比较效率。 



select * from t1 where f1= ' a ' or f1= ' b ' 

或者 



select * from t1 where f1 = ' a ' 

union all select * from t1 f1= ' b ' 



　　你可能指的不是这一类，这里不做讨论。 

　　2、形如 



select * from t1 where f1 in 

(select f1 from t2 where t2.fx= ' x ' )，

　　其中子查询的where里的条件不受外层查询的影响，这类查询一般情况下，自动优化会转成exist语句，也就是效率和exist一样。 

　　3、形如 



select * from t1 where f1 in 

(select f1 from t2 where t2.fx=t1.fx)， 



　　其中子查询的where里的条件受外层查询的影响，这类查询的效率要看相关条件涉及的字段的索引情况和数据量多少，一般认为效率不如exists。 



　　除了第一类in语句都是可以转化成exists 语句的，一般编程习惯应该是用exists而不用in. 

　　A，B两个表， 

　　(1)当只显示一个表的数据如A，关系条件只一个如ID时，使用IN更快：

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



　　(2)当只显示一个表的数据如A，关系条件不只一个如ID，col1时，使用IN就不方便了，可以使用EXISTS： 



　　select * from A

　　where exists (select 1 from B where id = A.id and col1 = A.col1)



　　(3)当只显示两个表的数据时，使用IN，EXISTS都不合适，要使用连接： 

select * from A left join B on id = A.id



　　所以使用何种方式，要根据要求来定。 

　　这是一般情况下做的测试： 



　　测试结果: 



set statistics io on 

select * from sysobjects where exists 

(select 1 from syscolumns where id=syscolumns.id) 

select * from sysobjects where id in 

(select id from syscolumns ) 

set statistics io off 

(47 行受影响)



　　表 ' syscolpars ' 。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 2 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。



　　表 ' sysschobjs ' 。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 



　　(1 行受影响) 

　　(44 行受影响) 

　　表 ' syscolpars ' 。扫描计数 47，逻辑读取 97 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 

　　表 ' sysschobjs ' 。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 



　　(1 行受影响) 

set statistics io on 

select * from syscolumns where exists 

(select 1 from sysobjects where id=syscolumns.id) 

select * from syscolumns where id in 

(select id from sysobjects ) 

set statistics io off



　　(419 行受影响)

　　表 ' syscolpars ' 。扫描计数 1，逻辑读取 10 次，物理读取 0 次，预读 15 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 



　　表 ' sysschobjs ' 。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 

(1 行受影响) 

(419 行受影响)



　　表 ' syscolpars ' 。扫描计数 1，逻辑读取 10 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 



　　表 ' sysschobjs ' 。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 



　　(1 行受影响) 

　　测试结果(总体来讲exists比in的效率高): 



　　效率:条件因素的索引是非常关键的 



　　把syscolumns 作为条件:syscolumns 数据大于sysobjects 



　　用in 

　　扫描计数 47，逻辑读取 97 次， 

　　用exists 

　　扫描计数 1，逻辑读取 3 次 

把sysobjects作为条件:sysobjects的数据少于syscolumns 

exists比in多预读 15 次 



　　对此我记得还做过如下测试： 

　　表 



　　test 



　　结构 

id int identity(1,1), --id主键\自增 

sort int, --类别,每一千条数据为一个类别 

sid int --分类id 

插入600w条数据 



　　如果要查询每个类别的最大sid 的话 

select * from test a 

where not exists(select 1 from test where sort = a.sort and sid > a.sid)



比 

select * from test a 

where sid in (select max(sid) from test where sort = a.sort)的效率要高三倍以上。具体的执行时间忘记了。但是结果我记得很清楚。在此之前我一直推崇第二种写法，后来就改第一种了。 

　　再举一个例子： 



SQL code



declare @t table(id int identity(1,1), v varchar(10))

insert @t select 'a'

union all select 'b'

union all select 'c'

union all select 'd'

union all select 'e'

union all select 'b'

union all select 'c'

--a语句

select * from @t where v in (select v from @t group by v having count(*)＞1)

--b语句

select * from @t a where exists(select 1 from @t where id!=a.id and v=a.v)



　　两条语句功能都是找到表变量@t中,v含有重复值的记录. 



　　第一条语句使用in,但子查询中与外部没有连系. 

　　第二条语句使用exists,但子查询中与外部有连系. 



　　大家看SQL查询计划,很清楚了. 

　　再复述一次。 

　　selec v from @t group by v having count(*)＞ 1 



　　这条语句,它的执行不依赖于主查询主句。 

　　那么,SQL在查询时就会优化,即将它的结果集缓存起来。 

v

b

c 



　　后续的操作,主查询在每处理一步时,相当于在处理 where v in( b , c ) 当然,语句不会这么转化, 只是为了说明意思,也即主查询每处理一行(记为currentROW时,子查询不会再扫描表, 只会与缓存的结果进行匹配。



select 1 from @t where id!=a.id and v=a.v 



　　而实用上面的语句,它的执行结果依赖于主查询中的每一行. 



　　当处理主查询第一行时 即 currentROW(id=1)时, 子查询再次被执行 select 1 from @t where id!=1 and v= a 扫描全表,从第一行记 currentSubROW(id=1) 开始扫描,id相同,过滤,子查询行下移,currentSubROW(id=2)继续,id不同,但v值不匹配,子查询行继续下移...直到currentSubROW(id=7)没找到匹配的, 子查询处理结束,第一行currentROW(id=1)被过滤,主查询记录行下移 



　　处理第二行时,currentROW(id=2), 子查询 select 1 from @t where id!=2 and v= b ,第一行currentSubROW(id=1)v值不匹配,子查询下移,第二行,id相同过滤,第三行,...到第六行,id不同,v值匹配, 找到匹配结果,即返回,不再往下处理记录. 主查询下移.
jwwyqs 2010-06-01
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 dawugui 的回复:]
最简单的解释就是:
in表示必须在里面.
exists表示只要存在即可.
[/Quote]
up
另外in 的效率没有 exists高
feilniu 2010-06-01
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 aoyihuashao 的回复:]

引用 5 楼 feilniu 的回复:

LZ想比较的是性能。

T2和T3的KEY1字段上有没有索引关系很大。

个人感觉：
1. 影响性能的关键因素是索引，IN和EXISTS的差异倒在其次。
2. 相同条件下，IN和EXISTS没有本质差异，主要是考虑代码逻辑的清晰。

另外：
SELECT * FROM T3 WHERE KEY1 IN (SELECT KEY1 FR……
[/Quote]

LZ这样比较一下：
SELECT * FROM T1 WHERE KEY1 IN (SELECT KEY1 FROM T3)
SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T3 WHERE KEY1 = T1.KEY1)
aoyihuashao 2010-06-01
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 feilniu 的回复:]

LZ想比较的是性能。

T2和T3的KEY1字段上有没有索引关系很大。

个人感觉：
1. 影响性能的关键因素是索引，IN和EXISTS的差异倒在其次。
2. 相同条件下，IN和EXISTS没有本质差异，主要是考虑代码逻辑的清晰。

另外：
SELECT * FROM T3 WHERE KEY1 IN (SELECT KEY1 FROM T1 WHERE KEY1 = T3.K……
[/Quote]

恩，我是考虑的性能方面的。

但相同条件下，有些时候没区别，有些时候区别很大，我上面的例子就是相同条件下的，其中这两句，区别很大：
SELECT * FROM T1 WHERE KEY1 IN (SELECT KEY1 FROM T3 WHERE KEY1 = T1.KEY1)
SELECT * FROM T1 WHERE EXISTS (SELECT 1 FROM T3 WHERE KEY1 = T1.KEY1 )
aoyihuashao 2010-06-01
  • 打赏
  • 举报
 回复
不是的，结果我知道的，IN和EXISTS的结果没什么区别的。

关键是执行效率问题。
feilniu 2010-06-01
  • 打赏
  • 举报
 回复
LZ想比较的是性能。

T2和T3的KEY1字段上有没有索引关系很大。

个人感觉：
1. 影响性能的关键因素是索引，IN和EXISTS的差异倒在其次。
2. 相同条件下，IN和EXISTS没有本质差异，主要是考虑代码逻辑的清晰。

另外：
SELECT * FROM T3 WHERE KEY1 IN (SELECT KEY1 FROM T1 WHERE KEY1 = T3.KEY1)
写成如下即可：
SELECT * FROM T3 WHERE KEY1 IN (SELECT KEY1 FROM T1)
v1ctory1216 2010-06-01
  • 打赏
  • 举报
 回复
in 后面要跟一个集合，exists在where后直接接子查询。可以再简单理解
where exists后的子查询会返回一个集合或一个简易临时表，如果这个集合或临时表里有数据，再在这个表中按父查询显示相应的查询结果集，如果子查询返回为空则返回个布尔值false，则父查询的结果为空
永生天地 2010-06-01
  • 打赏
  • 举报
 回复
如果是搞学术研究，那就要等高人来解释了

如果是为了实际应用，那in和exists的用途是不同的

in是要匹配相同的关键字。exists是对一个返回集合是否为空的判断
dawugui 2010-06-01
  • 打赏
  • 举报
 回复
你何必整那么复杂,如下的例即可说明问题.
create table tb(id int)

insert into tb values(1)

insert into tb values(2)

insert into tb values(3)

insert into tb values(4)

insert into tb values(5)

insert into tb values(6)

go



select * from tb where id in (1,2,3)

/*

id          

----------- 

1

2

3



（所影响的行数为 3 行）

*/



select * from tb where exists(select 1 from tb where id = 1)

/*

id          

----------- 

1

2

3

4

5

6



（所影响的行数为 6 行）

*/



select * from tb where exists(select 1 from tb where id = 2)

/*

id          

----------- 

1

2

3

4

5

6



（所影响的行数为 6 行）

*/



select * from tb where exists(select 1 from tb where id = 3)

/*

id          

----------- 

1

2

3

4

5

6



（所影响的行数为 6 行）

*/



drop table tb
dawugui 2010-06-01
  • 打赏
  • 举报
 回复
最简单的解释就是:
in表示必须在里面.
exists表示只要存在即可.
in和exists的区别
in和exists的区别 　表展示 　首先，查询中涉及到的两个表，一个user和一个order表，具体表的内容如下： 　user表： 　 　order表： 　 　in 　确定给定的值是否与子查询或列表中的值相匹配。in在查询的...
EXISTS和in的区别？EXISTS和普通where条件查询有啥区别
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上，而是exists用于对外表记录做筛选。exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当 exists里的条件语句能够返回记录行时，条件就为真...
exists和in的区别
当查询两个表的大小相当时，用In 和 exists差别不大。 如果两个表中一个表较小，一个表较大，那么子查询表大的用exists,子查询表小的用In，效率会高的。 也就是说IN适合于外表大而内表小的情况；EXISTS适合于外表...
in 和 exists 区别
in 和 exists 区别
MySQL中exists关键字和in的区别有那些？ SQL语句优化有那些方法? 查询速度很慢应该怎么办？
一、exists和in有何区别如下？ exists使用循环的方式，由outer表的记录数决定循环的次数，对于exists的影响最大，所以，外表的记录越小，适用于exists; in 先执行子查询，子查询的结果返回去重之后，再执行主查询...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章