分析in和exists的区别与执行效率的问题（3）

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

表

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值匹配, 找到匹配结果,即返回,不再往下处理记录. 主查询下移.