關於 in與exist , not in與not exist 的區別

吃奶的牛發表於2016-09-27
  1. in和exists  
  2. in 是把外表和內表作hash 連線，而exists是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。一直以來認為exists比in效率高的說法是不準確的。  
  3. 如果查詢的兩個表大小相當，那麼用in和exists差別不大。  
  4. 如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in：  
  5. 例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B)  
  6. 效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)  
  7. 效率高，用到了B表上cc列的索引。  
  8. 相反的2：select * from B where cc in (select cc from A)  
  9. 效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc)  
  10. 效率低，用到了A表上cc列的索引。  
  11.   
  12.   
  13. not in 和not exists  
  14.   
  15. 如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not in要快。  
  16.   
  17.    
  18.   
  19. not in 邏輯上不完全等同於not exists，如果你誤用了not in，小心你的程式存在致命的BUG：  
  20.   
  21. 請看下面的例子：  
  22. create table t1 (c1 number,c2 number);  
  23. create table t2 (c1 number,c2 number);  
  24.   
  25. insert into t1 values (1,2);  
  26. insert into t1 values (1,3);  
  27. insert into t2 values (1,2);  
  28. insert into t2 values (1,null);  
  29.   
  30. select * from t1 where c2 not in (select c2 from t2);  
  31. no rows found  
  32. select * from t1 where not exists (select 1 from t2 where t1.c2=t2.c2);  
  33. c1 c2  
  34. 1 3  
  35.   
  36. 正如所看到的，not in 出現了不期望的結果集，存在邏輯錯誤。如果看一下上述兩個select語句的執行計劃，也會不同。後者使用了hash_aj。  
  37. 因此，請儘量不要使用not in(它會呼叫子查詢)，而儘量使用not exists(它會呼叫關聯子查詢)。如果子查詢中返回的任意一條記錄含有空值，則查詢將不返回任何記錄，正如上面例子所示。  
  38. 除非子查詢欄位有非空限制，這時可以使用not in ,並且也可以通過提示讓它使用hasg_aj或merge_aj連線 

相關文章