這裏有條SQL語句：select * from A where id in(select id from B)
以上查詢使用了in語句,in()只執行壹次,它查出B表中的所有id字段並緩存起來.之後,檢查A表的id是否與B表中的id相等,如果相等則將A表的記錄加入結果集中,直到遍歷完A表的所有記錄;
它的查詢過程類似於以下過程
List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<A.length;i++) {
for(int j=0;j<B.length;j++) {
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break;
}
}
}
return resultSet;
可以看出,當B表數據較大時不適合使用in(),因為它會B表數據全部遍歷壹次.
如:A表有10000條記錄,B表有1000000條記錄,那麽最多有可能遍歷10000*1000000次,效率很差.
再如:A表有10000條記錄,B表有100條記錄,那麽最多有可能遍歷10000*100次,遍歷次數大大減少,效率大大提升.
結論1:in()適合B表比A表數據小的情況
這裏還有壹條SQL語句： select a.* from A a where exists(select 1 from B b where a.id=b.id)
以上查詢使用了exists語句,exists()會執行A.length次,它並不緩存exists()結果集,因為exists()結果集的內容並不重要,重要的是結果集中是否有記錄,如果有則返回true,沒有則返回false.
它的查詢過程類似於以下過程
List resultSet=[];
Array A=(select * from A)
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) { //執行select 1 from B b where b.id=a.id是否有記錄返回
resultSet.add(A[i]);
}
}
return resultSet;
結論2:exists()適合B表比A表數據大的情況
當B表比A表數據大時適合使用exists(),因為它沒有那麽遍歷操作,只需要再執行壹次查詢就行.
如:A表有10000條記錄,B表有1000000條記錄,那麽exists()會執行10000次去判斷A表中的id是否與B表中的id相等.
如:A表有10000條記錄,B表有100000000條記錄,那麽exists()還是執行10000次,因為它只執行A.length次,可見B表數據越多,越適合exists()發揮效果.
再如:A表有10000條記錄,B表有100條記錄,那麽exists()還是執行10000次,還不如使用in()遍歷10000*100次,因為in()是在內存裏遍歷比較,而exists()需要查詢數據庫,我們都知道查詢數據庫所消耗的性能更高,而內存比較很快.
當A表數據與B表數據壹樣大時,in與exists效率差不多,可任選壹個使用.
In適合內外表都很大的情況，exists適合外表結果集很小的情況。
In和exists對比：
若子查詢結果集比較小，優先使用in，若外層查詢比子查詢小，優先使
用exists。因為若用in，則Oracle會優先查詢子查詢，然後匹配外層查詢，
若使用exists，則oracle會優先查詢外層表，然後再與內層表匹配。最優化
匹配原則，拿最小記錄匹配大記錄
2、關於在 Oracle8i 之後 時代中in和exists的區別
in 是把外表和內表作hash join，而exists是對外表作loop，每次loop再對內表進行查詢。
壹直以來認為exists比in效率高的說法是不準確的。
如果查詢的兩個表大小相當，那麽用in和exists差別不大。
如果兩個表中壹個較小，壹個是大表，則子查詢表大的用exists，子查詢表小的用in：
例如：表A（小表），表B（大表）
1：
select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。
相反的
2：
select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。
帶in的關聯子查詢是多余的，因為in子句和子查詢中相關的操作的功能是壹樣的。如：
select staff_name from staff_member where staff_id in
(select staff_id from staff_func where staff_member.staff_id=staff_func.staff_id);
為非關聯子查詢指定exists子句是不適當的，因為這樣會產生笛卡乘積。如：
select staff_name from staff_member where staff_id
exists (select staff_id from staff_func);
not in 和not exists
如果查詢語句使用了not in 那麽內外表都進行全表掃描，沒有用到索引；
而not extsts 的子查詢依然能用到表上的索引。
所以無論哪個表大，用not exists都比not in要快。
盡量不要使用not in子句。使用minus 子句都比not in 子句快，雖然使用minus子句要進行兩次查詢：
select staff_name from staff_member where staff_id in (select staff_id from staff_member minus select staff_id from staff_func where func_id like '81%');
in 與 "=" 的區別
select name from student where name in ('zhang','wang','li','zhao');
與
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的結果是相同的。
3、關於在 Oracle8i 之後 時代中in和exists的區別
在ORACLE 11G大行其道的今天，還有很多人受早期版本的影響，記住壹些既定的規則，
1.子查詢結果集小，用IN
2.外表小，子查詢表大，用EXISTS
這是完全錯誤的觀點。在8i時代，這經常是正確的，但是現在已經11G了，馬上12C就要面世了。其實在ORACLE 9i CBO就已經優化了IN,EXISTS的區別，ORACLE優化器有個查詢轉換器，很多SQL雖然寫法不同，但是ORACLE優化器會根據既定規則進行查詢重寫，重寫為優化器覺得效率最高的SQL，所以可能SQL寫法不同，但是執行計劃卻是完全壹樣的。
IN與EXISTS有壹點要記住：IN壹般是用於非相關子查詢，而EXISTS壹般用於相關子查詢。當然IN也可以用於相關子查詢，EXISTS也可以用於非相關子查詢。但是這區別很重要，雖然優化器很強大，但是查詢轉換是有壹定的限制的，在EXISTS性能低下，無法進行相關查詢轉換，比如不能UNNEST SUBQUERY,那麽可能我們需要改寫SQL，通常可以用IN/JOIN等改寫相關子查詢，或對於含有OR的SEMI JOIN改為UNION ALL/UNION的形式。
下面就用例子說明壹下：
DROP TABLE a;
DROP TABLE b;
CREATE TABLE a AS SELECT * FROM hr.employees;
CREATE TABLE b AS SELECT * FROM hr.employees;
--反復插入，構造20萬行+
INSERT INTO a SELECT * FROM a;
INSERT INTO b SELECT * FROM a;
COMMIT；
BEGIN
dbms_stats.gather_table_stats(ownname => USER,tabname => 'a',estimate_percent => 100,cascade => TRUE);
dbms_stats.gather_table_stats(ownname => USER,tabname => 'b',estimate_percent => 100,cascade => TRUE);
END;
/
1.測試IN,EXISTS在簡單查詢中，是等價的
SQL> set autotrace traceonly exp
SQL> SELECT * FROM a
2 WHERE EXISTS(
3 SELECT 1 FROM b WHERE a.employee_id=b.employee_id);
執行計劃
----------------------------------------------------------
Plan hash value: 2317816356
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 217K| 15M| 1375 (2)| 00:00:17 |
|* 1 | HASH JOIN | | 217K| 15M| 1375 (2)| 00:00:17 |
| 2 | SORT UNIQUE | | 217K| 848K| 126 (1)| 00:00:02 |
| 3 | INDEX FAST FULL SCAN | IDX2_B | 217K| 848K| 126 (1)| 00:00:02 |
| 4 | TABLE ACCESS FULL | A | 217K| 14M| 620 (1)| 00:00:08 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID")
SQL> SELECT * FROM a
2 WHERE a.employee_id IN (SELECT b.employee_id FROM b);
執行計劃
----------------------------------------------------------
Plan hash value: 2317816356
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 217K | 15M| 1375 (2) | 00:00:17 |
|* 1 | HASH JOIN | | 217K | 15M| 1375 (2) | 00:00:17 |
| 2 | SORT UNIQUE | | 217K | 848K| 126 (1) | 00:00:02 |
| 3 | INDEX FAST FULL SCAN | IDX2_B | 217K | 848K| 126 (1) | 00:00:02 |
| 4 | TABLE ACCESS FULL | A | 217K | 14M| 620 (1) | 00:00:08 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPLOYEE_ID"="B"."EMPLOYEE_ID")
可以看出，兩個計劃完全沒有區別。類似於ORACLE查詢重寫為：
SELECT a.* FROM a,(SELECT DISTINCT b.employee_id FROM b) b1 WHERE a.employee_id=b1.employee_id;
結語：討論SQL語句：select * from A where id in (1,2,3,4,5,6,7,8,9) 中 在Oracle8i 版本之後的Oracle數據庫中性能是壹樣的。