今天在網上查資料看sql中exist與in 的區別,發現眾說紛紜,有幾種說法:
1. exist效率比in高,凡是in都應該用exist替換
2. 外大內小用in,外小內大用exist
3. 外大內小用exist,外小內大用in
一時分辨不了哪個正確,于是動手檢驗.
數據庫: ORACLE 10G
客戶端: PlSqlDev 7.1
1. 外大內小的情況:
history.tb_stk_cap_chg 記錄數 > 100,000,000
history.tb_stk_cap_chg_test 記錄數 = 20
--使用in
select count(*)
from history.tb_stk_cap_chg a
where a.tradedate =
'20060712'
and a.br_serial_no in
(select b.br_serial_no from
history.tb_stk_cap_chg_test b)
1 row selected in 21.188 seconds
--使用exists
select count(*)
from history.tb_stk_cap_chg a
where a.tradedate =
'20060712'
and exists (select null
from
history.tb_stk_cap_chg_test b
where a.br_serial_no =
b.br_serial_no)
1 row selected in 0.5 seconds
可以看出外大內小用in效率極低,用exists效率很高
2. 外小內大的情況:
history.tb_stk_cap_chg 記錄數 > 100,000,000
history.tb_stk_cap_chg_test 記錄數 = 1,000,000
--使用in
select count(*)
from history.tb_stk_cap_chg_test a
where
a.br_serial_no in
(select b.br_serial_no from history.tb_stk_cap_chg
b)
1 row selected in 9.187 seconds
--使用exists
select count(*)
from history.tb_stk_cap_chg_test a
where exists
(select null
from history.tb_stk_cap_chg b
where
a.br_serial_no = b.br_serial_no)
1 row selected in 10.359 seconds
注: 當history.tb_stk_cap_chg_test 記錄數 < 1,000,000時得出的時間差別很小,不容易判斷
可以看出外小內大時使用in比exists效率更高
----------------------------------------------------------------------------------
在改變history.tb_stk_cap_chg_test記錄數(10-1,000,000)進行測試(>5次)得出的結論與上述結論符合,在此不一一列出
由此得出結論: exits適合內小外大的查詢，in適合內大外小的查詢
----------------------------------------------------------------------------------
附上一篇覺得正確的文章,歡迎探討:
in 和 exists也是很好區別的.
in 是一個集合運算符.
a
in {a,c,d,s,d....}
這個運算中,前面是一個元素,后面是一個集合,集合中的元素類型是和前面的元素一樣的.
而exists是一個存在判斷,如果后面的查詢中有結果,則exists為真,否則為假.
in 運算用在語句中,它后面帶的select
一定是選一個字段,而不是select *.
比如說你要判斷某班是否存在一個名為"小明"的學生,你可以用in 運算:
"小明" in
(select sname from student)
這樣(select sname from student)
返回的是一個全班姓名的集合,in用于判斷"小明"是否為此集合中的一個數據;
同時,你也可以用exists語句:
exists
(select * from student where sname="小明")
這兩個涵數是差不多的, 但是由于優化方案的不同, 通常NOT
EXISTS要比NOT IN 要快, 因為NOT EXISTS可以使用結合算法而NOT IN 就不行了，而EXISTS則不如IN快,
因為這時候IN可能更多的使用結合算法.
select * from 表A where exists(select * from 表B
where 表B.id=表A.id)
這句相當于
select * from 表A where id in (select id
from 表B)
對于表A的每一條數據,都執行select * from 表B where
表B.id=表A.id的存在性判斷,如果表B中存在表A當前行相同的id,則exists為真,該行顯示,否則不顯示
exits適合內小外大的查詢，in適合內大外小的查詢
IN
確定給定的值是否與子查詢或列表中的值相匹配。
EXISTS
指定一個子查詢，檢測行的存在。
比較使用 EXISTS 和 IN 的查詢
這個例子比較了兩個語義類似的查詢。第一個查詢使用 EXISTS 而第二個查詢使用 IN。注意兩個查詢返回相同的信息。
USE
pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
(SELECT *
FROM titles
WHERE pub_id = publishers.pub_id
AND type
= 'business')
GO
-- Or, using the IN clause:
USE pubs
GO
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
(SELECT
pub_id
FROM titles
WHERE type = 'business')
GO
下面是任一查詢的結果集：
pub_name
----------------------------------------
Algodata
Infosystems
New Moon Books
(2 row(s) affected)
exits 相當于存在量詞：表示集合存在,也就是集合不為空只作用一個集合.例如 exist P
表示P不空時為真; not exist P表示p為空時 為真 in表示一個標量和一元關系的關系。例如：s in P表示當s與P中的某個值相等時 為真; s
not in P 表示s與P中的每一個值都不相等時 為真