in和exists的區別與SQL執行效率分析
原文：http://www.cnblogs.com/diction/archive/2008/01/18/1043844.html
本文對in和exists的區別與SQL執行效率進行了全面整理分析……
最近很多論壇又開始討論in和exists的區別與SQL執行效率的問題，
本文特整理一些
in和exists的區別與SQL執行效率分析
SQL中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 語句的SQL，一般編程習慣應該是用exists而不用in，而很少去考慮in和exists的執行效率.
in和exists的SQL執行效率分析
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)
的執行效率要高三倍以上。具體的執行時間忘記了。但是結果我記得很清楚。在此之前我一直推崇第二種寫法，后來就改第一種了。
in和exists的sql執行效率分析,再簡單舉一個例子：
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語句in的sql寫法
select * from @t where v in (select v from @t group by v having count(*)>1)
--b語句exists的sql寫法
select * from @t a where exists(select 1 from @t where id!=a.id and v=a.v)
兩條語句功能都是找到表變量@t中,v含有重復值的記錄.
第一條sql語句使用in,但子查詢中與外部沒有連系.
第二條sql語句使用exists,但子查詢中與外部有連系.
大家看SQL查詢計劃,很清楚了.
selec v from @t group by v having count(*)> 1
這條Sql語句,它的執行不依賴于主查詢主句(我也不知道怎么來描述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值匹配, 找到匹配結果,即返回,不再往下處理記錄. 主查詢下移.
處理第三行時，以此類推...
sql優化中，使用in和exist？ 主要是看你的篩選條件是在主查詢上還是在子查詢上。
通過分析，相信大家已經對in和exists的區別、in和exists的SQL執行效率有較清晰的了解。