本文主要分析了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值匹配, 找到匹配結果,即返回,不再往下處理記錄. 主查詢下移.
