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

系列文章目录

【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行

---

---

前言

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

---

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

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

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

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

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

• 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

• 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

• 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

通过看上面的执行计划，三个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
1
2
3
4
5
6
7
8
9

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

LEFT_STR RIGHT_STR     STATUS
-------- --------- ----------
left_1             
left_2             
left_3   right_3            1
left_4             
1
2
3
4
5
6

对于这个需求，可能有些人会加一个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
1
2
3
4
5
6
7
8

（+）写法：

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
1
2
3
4
5
6
7
8

而此时的执行计划：

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

很明显，结果以及执行计划(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             
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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

(select * from r where status=1) r
1

---

总结

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