【SQL開發(fā)實戰(zhàn)技巧】系列（六）：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關聯(lián)條件不要亂放

這篇具有很好參考價值的文章主要介紹了【SQL開發(fā)實戰(zhàn)技巧】系列（六）：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關聯(lián)條件不要亂放。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

系列文章目錄

【SQL開發(fā)實戰(zhàn)技巧】系列（一）:關于SQL不得不說的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（二）：簡單單表查詢
【SQL開發(fā)實戰(zhàn)技巧】系列（三）：SQL排序的那些事
【SQL開發(fā)實戰(zhàn)技巧】系列（四）：從執(zhí)行計劃討論UNION ALL與空字符串&UNION與OR的使用注意事項
【SQL開發(fā)實戰(zhàn)技巧】系列（五）：從執(zhí)行計劃看IN、EXISTS 和 INNER JOIN效率，我們要分場景不要死記網(wǎng)上結論
【SQL開發(fā)實戰(zhàn)技巧】系列（六）：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關聯(lián)條件不要亂放
【SQL開發(fā)實戰(zhàn)技巧】系列（七）：從有重復數(shù)據(jù)前提下如何比較出兩個表中的差異數(shù)據(jù)及對應條數(shù)聊起
【SQL開發(fā)實戰(zhàn)技巧】系列（八）：聊聊如何插入數(shù)據(jù)時比約束更靈活的限制數(shù)據(jù)插入以及怎么一個insert語句同時插入多張表
【SQL開發(fā)實戰(zhàn)技巧】系列（九）：一個update誤把其他列數(shù)據(jù)更新成空了？Merge改寫update！給你五種刪除重復數(shù)據(jù)的寫法！
【SQL開發(fā)實戰(zhàn)技巧】系列（十）：從拆分字符串、替換字符串以及統(tǒng)計字符串出現(xiàn)次數(shù)說起
【SQL開發(fā)實戰(zhàn)技巧】系列（十一）：拿幾個案例講講translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十二）：三問(如何對字符串字母去重后按字母順序排列字符串？如何識別哪些字符串中包含數(shù)字？如何將分隔數(shù)據(jù)轉換為多值IN列表?)
【SQL開發(fā)實戰(zhàn)技巧】系列（十三）：討論一下常用聚集函數(shù)&通過執(zhí)行計劃看sum()over()對員工工資進行累加
【SQL開發(fā)實戰(zhàn)技巧】系列（十四）：計算消費后的余額&計算銀行流水累計和&計算各部門工資排名前三位的員工
【SQL開發(fā)實戰(zhàn)技巧】系列（十五）：查找最值所在行數(shù)據(jù)信息及快速計算總和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL開發(fā)實戰(zhàn)技巧】系列（十六）：數(shù)據(jù)倉庫中時間類型操作（初級）日、月、年、時、分、秒之差及時間間隔計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十七）：數(shù)據(jù)倉庫中時間類型操作（初級）確定兩個日期之間的工作天數(shù)、計算—年中周內(nèi)各日期出現(xiàn)次數(shù)、確定當前記錄和下一條記錄之間相差的天數(shù)
【SQL開發(fā)實戰(zhàn)技巧】系列（十八）：數(shù)據(jù)倉庫中時間類型操作（進階）INTERVAL、EXTRACT以及如何確定一年是否為閏年及周的計算
【SQL開發(fā)實戰(zhàn)技巧】系列（十九）：數(shù)據(jù)倉庫中時間類型操作（進階）如何一個SQL打印當月或一年的日歷？如何確定某月內(nèi)第一個和最后—個周內(nèi)某天的日期？
【SQL開發(fā)實戰(zhàn)技巧】系列（二十）：數(shù)據(jù)倉庫中時間類型操作（進階）獲取季度開始結束時間以及如何統(tǒng)計非連續(xù)性時間的數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十一）：數(shù)據(jù)倉庫中時間類型操作（進階）識別重疊的日期范圍，按指定10分鐘時間間隔匯總數(shù)據(jù)
【SQL開發(fā)實戰(zhàn)技巧】系列（二十二）：數(shù)倉報表場景? 從分析函數(shù)效率一定快嗎聊一聊結果集分頁和隔行抽樣實現(xiàn)方式
【SQL開發(fā)實戰(zhàn)技巧】系列（二十三）：數(shù)倉報表場景? 如何對數(shù)據(jù)排列組合去重以及通過如何找到包含最大值和最小值的記錄這個問題再次用執(zhí)行計劃給你證明分析函數(shù)性能不一定高
【SQL開發(fā)實戰(zhàn)技巧】系列（二十四）：數(shù)倉報表場景?通過案例執(zhí)行計劃詳解”行轉列”,”列轉行”是如何實現(xiàn)的
【SQL開發(fā)實戰(zhàn)技巧】系列（二十五）：數(shù)倉報表場景?結果集中的重復數(shù)據(jù)只顯示一次以及計算部門薪資差異高效的寫法以及如何對數(shù)據(jù)進行快速分組
【SQL開發(fā)實戰(zhàn)技巧】系列（二十六）：數(shù)倉報表場景?聊聊ROLLUP、UNION ALL是如何分別做分組合計的以及如何識別哪些行是做匯總的結果行

前言

本篇文章講解的主要內(nèi)容是：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，還是那就話，別死記網(wǎng)上結論、在使用內(nèi)外關聯(lián)時，特別是簡寫方式時記住關聯(lián)條件不要亂放！
【SQL開發(fā)實戰(zhàn)技巧】這一系列博主當作復習舊知識來進行寫作，畢竟SQL開發(fā)在數(shù)據(jù)分析場景非常重要且基礎，面試也會經(jīng)常問SQL開發(fā)和調優(yōu)經(jīng)驗，相信當我寫完這一系列文章，也能再有所收獲，未來面對SQL面試也能游刃有余~。

一、從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率

有些單位的部門（如40)中一個員工也沒有，只是設了一個部門名字，如下列語句：

select count(*) from dept where deptno=40;

如何通過關聯(lián)查詢把這些信息查出來？
同樣有三種寫法:NOT IN、NOT EXISTS 和LEFT JOIN。
語句及PLAN如下（版本為11.2.0.4.0 )。
環(huán)境：

alter table dept add constraints pk_dept primary key (deptno); --如果你有就不用建了

NOT IN用法

EXPLAIN PLAN FOR select *
FROM dept
WHERE deptno NOT IN (SELECT emp.deptno FROM emp WHERE emp.deptno IS NOT NULL);
SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("DEPTNO"="EMP"."DEPTNO")
       filter("DEPTNO"="EMP"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected

NOT EXISTS 用法

EXPLAIN PLAN FOR SELECT*
FROM dept
WHERE NOT EXISTS ( SELECT NULL FROM emp WHERE emp.deptno  =  dept.deptno) ; 
SELECT * FROM TABLE(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected

LEFT JOIN 用法

根據(jù)前面介紹過的左聯(lián)知識,LEFT JOIN 取出的是左表中所有的數(shù)據(jù),其中與右表不匹配的就表示左表NOT IN右表。
所以這里LEFT JOIN加上條件TS NULL,就是LEFT JOIN的寫法：

EXPLAIN PLAN FOR
SELECT dept.*
FROM dept
LEFT JOIN emp ON emp.deptno = dept.deptno WHERE emp.deptno IS NULL;

SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1353548327
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    23 |     6  (17)| 00
|   1 |  MERGE JOIN ANTI             |         |     1 |    23 |     6  (17)| 00
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00
|*  4 |   SORT UNIQUE                |         |    14 |    42 |     4  (25)| 00
|*  5 |    TABLE ACCESS FULL         | EMP     |    14 |    42 |     3   (0)| 00
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)

19 rows selected

通過看上面的執(zhí)行計劃，三個SQL用的都是 MERGE JOIN ANTI, 說明這三種方法的效率一樣。
如果想改寫，就要對比改寫前后的PLAN,根據(jù)PLAN來判斷并測試哪種方法的效率高，一定要記住不能憑借某些結論來碰運氣。

二、外連接中的條件不要亂放，建議大家使用join而非(+)

對于系列三博客介紹的左聯(lián)語句,見下面的數(shù)據(jù)。

SELECT l.str AS left_str, r.str AS right_str,r.status FROM l
LEFT JOIN r	ON l.v = r.v
ORDER BY 1 , 2 ;
LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4   right_4            0

那現(xiàn)在有這么一個需求：對于其中的L表，四條數(shù)據(jù)都返回。而對于R表，我們需要只顯示其中的status=1的數(shù)據(jù)，也就是下面這樣的結果：

LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4

對于這個需求，可能有些人會加一個where條件！然后結果就變成了下面這樣了:
left join寫法：

SELECT l.str AS left_str, r.str AS right_str,r.status 
 FROM l
LEFT JOIN r  ON (l.v = r.v)
where  r.status=1
ORDER BY 1 , 2;
LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_3   right_3            1

（+）寫法：

SELECT l.str AS left_str, r.str AS right_str, r.status
  FROM l, r
 where l.v = r.v(+)
   and r.status = 1
 ORDER BY 1, 2;
LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_3   right_3            1

而此時的執(zhí)行計劃：

SQL> EXPLAIN PLAN FOR
  2  SELECT l.str AS left_str, r.str AS right_str,r.status
  3   FROM l
  4  LEFT JOIN r  ON (l.v = r.v)
  5  where  r.status=1
  6  ORDER BY 1 , 2;

Explained

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 688663707
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     2 |    42 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     2 |    42 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     2 |    42 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| R    |     2 |    24 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| L    |     4 |    36 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("L"."V"="R"."V")
   3 - filter("R"."STATUS"=1)

17 rows selected

很明顯，結果以及執(zhí)行計劃(HASH JOIN)與我們期望得到的結果都不一致?。?！這是很多人在寫查詢或更改查詢時常遇到的一種錯誤。問題就在于所加條件的位置及寫法，正確的寫法分別如下：

SQL> SELECT l.str AS left_str, r.str AS right_str, r.status
  2    FROM l
  3    LEFT JOIN r
  4      ON (l.v = r.v and r.status = 1)
  5   ORDER BY 1, 2;

LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4             



SQL> SELECT l.str AS left_str, r.str AS right_str, r.status
  2    FROM l, r
  3   where l.v = r.v(+)
  4     and r.status(+) = 1
  5   ORDER BY 1, 2;

LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4

看一下這時候的執(zhí)行計劃：

SQL> EXPLAIN PLAN FOR
  2  SELECT l.str AS left_str, r.str AS right_str, r.status
  3    FROM l
  4    LEFT JOIN r
  5      ON (l.v = r.v and r.status = 1)
  6   ORDER BY 1, 2;

Explained


SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2310059642
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     4 |    84 |     7  (15)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |     4 |    84 |     7  (15)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |      |     4 |    84 |     6   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| L    |     4 |    36 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| R    |     2 |    24 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("L"."V"="R"."V"(+))
   4 - filter("R"."STATUS"(+)=1)

17 rows selected

以上兩種寫法結果均正確，且根據(jù)執(zhí)行計劃HASH JOIN OUTER明確走的是外連接。而且根據(jù)上面查詢我們能夠看出來JOIN的方式明顯更容易辨別，這也是我反復建議使用JOIN的原因。
對于上面SQL我們還可以使用先過濾再關聯(lián)的方式，即R表先過濾：

(select * from r where status=1) r

總結

同上一篇博客所說，在使用in exists或則NOT IN、NOT EXISTS 和 LEFT JOIN時候，不要想當然的認為in和not in效率極其低下，在本章案例中通過執(zhí)行計劃能夠直觀的看到，三者效率竟然一致了??！所以，讀萬卷書不如行萬里路，網(wǎng)上別人做的總結再好，也不如自己實踐一把來的真實。還有就是，在使用關聯(lián)查詢時候，關聯(lián)條件和過濾條件一定要想好放哪里，不然你會想當然的錯了！文章來源地址http://www.zghlxwxcb.cn/news/detail-810600.html

到了這里，關于【SQL開發(fā)實戰(zhàn)技巧】系列（六）：從執(zhí)行計劃看NOT IN、NOT EXISTS 和 LEFT JOIN效率，記住內(nèi)外關聯(lián)條件不要亂放的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！