oracle中的exists和not exists和in用法詳解

有兩個簡單例子，以說明 “exists”和“in”的效率問題

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

　　T1資料量小而T2資料量非常大時，T1<

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

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

　　exists 用法：

　　請注意 1）句中的有顏色字型的部分 ，理解其含義；

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

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

　　但是，如果你噹噹執行 1） 句括號裡的語句，是會報語法錯誤的，這也是使用exists需要注意的地方。

　　“exists（xxx）”就表示括號裡的語句能不能查出記錄，它要查的記錄是否存在。

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

　　in 的用法：

　　繼續引用上面的例子

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

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

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

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

　　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通常查詢速度快的原因

==================================

假設如下應用：
兩張表——使用者表TDefUser（userid，address,phone）和消費表 TAccConsume(userid,time,amount)，需要查消費超過5000的使用者記錄。
用exists:
select * from TDefUser
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)
用in:
select * from TDefUser
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)

通常情況下采用exists要比in效率高。

exists()後面的子查詢被稱做相關子查詢 他是不返回列表的值的.只是返回一個ture或false的結果(這也是為什麼子查詢裡是"select 1"的原因，換成"select 6"完全一樣，當然也可以select欄位，但是明顯效率低些)
其執行方式是先執行主查詢一次 再去子查詢裡查詢與其對應的結果 如果是ture則輸出,反之則不輸出.再根據主查詢中的每一行去子查詢裡去查詢.

in()後面的子查詢 是返回結果集的,換句話說執行次序和exists()不一樣.子查詢先產生結果集,然後主查詢再去結果集裡去找符合要求的欄位列表去.符合要求的輸出,反之則不輸出.

比如使用者表TDefUser（userid，address,phone），消費表 TAccConsume(userid,time,amount)資料如下：

消費表聚集索引是userid,time
資料（注意因為有聚集索引,實際儲存也是按以下次序的）
1   2006-1-1  200
1   2006-1-2  300
1   2006-1-2  500
1   2006-1-3  2000
1   2006-1-3  2000
1   2006-1-4  400
1   2006-1-5  500
2   2006-1-1  200
2   2006-1-2  300
2   2006-1-2  500
2   2006-1-3  2000
2   2006-1-3  6000
2   2006-1-4  400
2   2006-1-5  8000
3   2006-1-1  7000
3   2006-1-2  30000
3   2006-1-2  50000
3   2006-1-3  20000

語句：
select * from TDefUser
where exists (select 1 from TAccConsume where TDefUser.userid=TAccConsume.userid and TAccConsume.amount>5000)

對於userid=1，需要找所有記錄，才返回false，與第二個語句的效率差不多
對於userid=2，找到2006-1-3的記錄，就返回true，比第而個語句的效率高
對於userid=3，第一條記錄就返回true，比第二個語句的效率高

語句
select * from TDefUser
where userid in (select userid from TAccConsume where TAccConsume.amount>5000)

返回空記錄集
2
2
3
3
3
3

再判斷

語句
select * from TDefUser
where userid in (select userid from TAccConsume where userid=TDefUser.userid and amount>5000)

對於userid=1，需要找所有記錄，返回空記錄集，比較判斷
對於userid=2，需要找所有記錄，返回記錄集
2
2
，比較判斷
對於userid=3需要找所有記錄，返回記錄集
3
3
3
3
，比較判斷

表中如果沒有聚集索引，對exists每個userid查詢的條數都不同，但都是<=第三個語句需要掃描的條數，極端的（比如> 5000的都是在最後）與第三個語句效率相似，一般的比第二個語句快，所以說,“一般”exists比in效率高

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11320622/viewspace-675332/，如需轉載，請註明出處，否則將追究法律責任。