in和exists的区别与执行效率问题解析

　　只简单作个例子吧,上次打的太长了,我都记不清我打了些什么,还说了什么集合运算之类的.

　　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

　　这条语句,它的执行不依赖于主查询主句(我也不知道怎么来描述in外面的和里面的,暂且这么叫吧,大家明白就行)

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

　　处理第三行时...

　　好了,说到这,我想我也说的差不多了.