exists()、not exists() 、in()、not in()用法以及效率差異

一、exists（） 用法：

select * from T1 where exists(select 1 from T2 where T1.a=T2.a) 

其中 “select 1 from T2 where T1.a=T2.a” 相當於一個關聯表查詢，

相當於“select 1 from T1,T2  where T1.a=T2.a”

但是，如果單獨執行括號中的這句話是會報語法錯誤的，這也是使用exists需要注意的地方。

“exists（xxx）”就表示括號裡的語句能不能查出記錄，它要查的記錄是否存在。因此“select 1”這裡的 “1”其實是無關緊要的，換成“*”也沒問題，它只在乎括號裡的資料能不能查詢出來，是否存在這樣的記錄，如果存在，where 條件成立。

PS：not exists()  正好相反

select name from employee where not exists (select name from student)

 

二、in（） 的用法：

select * from T1 where T1.a in (select T2.a from T2) 

 

這裡的“in”後面括號裡的語句搜尋出來的欄位的內容一定要與where後指定的欄位相對應，一般來說，T1和T2這兩個表的a欄位表達的意義應該是一樣的，否則這樣查沒什麼意義。

打個比方：T1，T2表都有一個欄位，表示工單號，但是T1表示工單號的欄位名叫“ticketid”，T2則為“id”，但是其表達的意義是一樣的，而且資料格式也是一樣的。這時，用的寫法就可以這樣：

select * from T1 where T1.ticketid in (select T2.id from T2) 

三、 “exists”和“in”的效率問題

0）select name from employee where name not in (select name from student)

      select name from employee where not exists (select name from student)

      第一句SQL語句的執行效率不如第二句。

     透過使用EXISTS，Oracle會首先檢查主查詢，然後執行子查詢直到它找到第一個匹配項，這就節省了時間。

     Oracle在執行IN子查詢時，首先執行子查詢，並將獲得的結果列表存放在一個加了索引的臨時表中。在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完畢，存放在臨時表中以後再執行主查詢。

     這也就是使用EXISTS比使用IN通常查詢速度快的原因。

1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) 

    T1資料量小而T2資料量非常大時，T1<<T2 時,  exists()的查詢效率高。

2) select * from T1 where T1.a in (select T2.a from T2) 

     T1資料量非常大而T2資料量小時，T1>>T2 時，in() 的查詢效率高。

 總結：

a in b => a 外表、b 內表
1、外表大，用 in；內表大，用 exists。
2、無論那個表大，用 not exists 都比 not in 要快。

not in、not exists

如果查詢語句使用了not in 那麼內外表都進行  全表掃描 ，沒有用到索引；

 而 not extsts 的子查詢  依然能用到表上的索引 。
所以無論那個表大，用 not exists都比not in要快。