【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放

本文链接：https://blog.csdn.net/qq_28356739/article/details/128944956

系列文章目录

文章目录

系列文章目录
前言
一、从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率
二、外连接中的条件不要乱放，建议大家使用join而非(+)
总结

前言

本篇文章讲解的主要内容是：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，还是那就话，别死记网上结论、在使用内外关联时，特别是简写方式时记住关联条件不要乱放！
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。

一、从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率

有些单位的部门（如40)中一个员工也没有，只是设了一个部门名字，如下列语句：

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

如何通过关联查询把这些信息查出来？
同样有三种写法:NOT IN、NOT EXISTS 和LEFT JOIN。
语句及PLAN如下（版本为11.2.0.4.0 )。
环境：

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 用法

根据前面介绍过的左联知识,LEFT JOIN 取出的是左表中所有的数据,其中与右表不匹配的就表示左表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

通过看上面的执行计划，三个SQL用的都是 MERGE JOIN ANTI, 说明这三种方法的效率一样。
如果想改写，就要对比改写前后的PLAN,根据PLAN来判断并测试哪种方法的效率高，一定要记住不能凭借某些结论来碰运气。

二、外连接中的条件不要乱放，建议大家使用join而非(+)

对于系列三博客介绍的左联语句,见下面的数据。

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

那现在有这么一个需求：对于其中的L表，四条数据都返回。而对于R表，我们需要只显示其中的status=1的数据，也就是下面这样的结果：

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

而此时的执行计划：

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

很明显，结果以及执行计划(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

看一下这时候的执行计划：

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

以上两种写法结果均正确，且根据执行计划HASH JOIN OUTER明确走的是外连接。而且根据上面查询我们能够看出来JOIN的方式明显更容易辨别，这也是我反复建议使用JOIN的原因。
对于上面SQL我们还可以使用先过滤再关联的方式，即R表先过滤：

(select * from r where status=1) r

总结

同上一篇博客所说，在使用in exists或则NOT IN、NOT EXISTS 和 LEFT JOIN时候，不要想当然的认为in和not in效率极其低下，在本章案例中通过执行计划能够直观的看到，三者效率竟然一致了！！所以，读万卷书不如行万里路，网上别人做的总结再好，也不如自己实践一把来的真实。还有就是，在使用关联查询时候，关联条件和过滤条件一定要想好放哪里，不然你会想当然的错了！