1. Toy模板网首页
  2. >Toy博客

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

1年前作者：赵延东的一亩三分地分类：Toy博客阅读(5)违法举报

这篇具有很好参考价值的文章主要介绍了【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

系列文章目录

【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;

如何通过关联查询把这些信息查出来？
同样有三种写法: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效率极其低下，在本章案例中通过执行计划能够直观的看到，三者效率竟然一致了！！所以，读万卷书不如行万里路，网上别人做的总结再好，也不如自己实践一把来的真实。还有就是，在使用关联查询时候，关联条件和过滤条件一定要想好放哪里，不然你会想当然的错了！文章来源地址https://www.toymoban.com/news/detail-810600.html

到了这里，关于【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包赞助服务器费用

相关文章

  • Python实验，用pygame做飞机大战游戏设计

    摘 要：根据课程要求，以及面向对象程序设计的编程思想，在Windows操作系统环境下，运用PyCharm编译程序，以Python语言为开发语言，最终实现飞机大战游戏相应的游戏操作功能。Python是一种跨平台的、开源的、免费的、解释型的高级编程语言。它具有丰富的、强大的库, 能够

    2024年02月07日
    浏览(13)
  • 当程序员纠结中午应该吃什么，那就用pygame来解决吧

    当程序员纠结中午应该吃什么，那就用pygame来解决吧

    写多了kotlin和android，最近想搞点小东西，于是拿出了长期没有宠爱的python，打算搞个小项目 想想应该写什么，对了，该吃饭了，诶，刚好，写一个能随机选择吃什么的小程序吧，只需要点击按钮，就会随机出现菜谱，然后再点一下，就会得出今天吃什么的结论 思路是这样的

    2024年01月16日
    浏览(45)

  • 斐讯无线路由器的正确连接方法

        一、要是无线路由器的上网参数设置不正确的话，那么无线路由器肯定也不会很顺利地连接上无线网络，所以要保证无线路由器能够非常顺利地连接本地无线网络，我们就需要仔细检查无线上网设置因素。 1、以管理员身份登录进无线路由器系统中，并在桌面中依次单击

    2024年02月05日
    浏览(47)
  • 易模为真人3D手办制作带来了创新

    易模为真人3D手办制作带来了创新

    3d打印技术是一项近年来迅速发展的先进制造技术，逐渐在各个领域展现出无限的潜力。其中，3d打印真人手办成为了一个备受关注的领域。在市面上，我们常常可以看到一些热门动漫角色或明星的真人3d手办，逼真的细节和完美的再现度让人们为之赞叹不已。 真人3d手办是指

    2024年02月16日
    浏览(9)
  • React实现关键字高亮

    React实现关键字高亮

    先看效果： 实现很简单通过以下这个函数： 展示某段文本时调用该函数处理后，在展示就能实现高亮效果 原理是： 这个函数的作用是在给定的文本中，将指定的进行高亮标记。它接受两个参数：text（要处理的文本）和 keyword（要高亮标记的）。 函数首先使用

    2024年02月13日
    浏览(40)
  • 在Linux下做性能分析1：基本模型

    在Linux下做性能分析1：基本模型

    ==介绍== 本Blog开始介绍一下在Linux分析性能瓶颈的基本方法。主要围绕一个基本的分析模型，介绍perf和ftrace的使用技巧，然后东一扒子，西一扒子，逮到什么说什么，也不一定会严谨。主要是把这个领域的一些思路和技巧串起来。如果读者来讨论得多，我们就讨论深入一点，

    2024年02月16日
    浏览(6)
  • python#django数据库一对一/一对多/多对多

    python#django数据库一对一/一对多/多对多

    搭建 # 一对一 class   TestUser(models.Model):     username=models.CharField(max_length=32)     password = models.CharField(max_length=32) class TestInfo(models.Model):     mick_name=models.CharField(max_length=32)     user=models.OneToOneField(to=TestUser,on_delete=models.CASCADE()#on_delete 删除的模式 CASCADE 级联删除 让后执行数

    2024年02月14日
    浏览(16)

  • MyBatis-Plus不写任何resultMap和SQL执行一对一、一对多、多对多关联查询

    MyBatis-Plus不写任何resultMap和SQL执行一对一、一对多、多对多关联查询 MyBatis-Plus不写任何resultMap和SQL执行一对一、一对多、多对多关联查询 com.github.dreamyoung mprelation 0.0.3.2-RELEASE 注解工具使用优缺点： 优点： 使用简单，通过在实体类上添加@OneToOne / @OneToMany / @ManyToOne / @ManyToM

    2024年01月20日
    浏览(9)

  • SpringBoot 开启事务的常用方式

    前言，有时候统一异常处理不太好，因为范围太广了，很多没必要回滚的地方，因为抛了个异常都没办法继续执行别的代码，所以，可以在需要开事务的地方添加 @Transation 注解开启事务就行了。 springboot 项目中，一般两种方式可以开启事务： 1. 只需要在类或者方法上添加注

    2024年02月16日
    浏览(8)
  • swap分区详解(创建swap分区，启用swap交换空间，关闭swap交换空间)

    swap分区详解(创建swap分区，启用swap交换空间，关闭swap交换空间)

    swap分区 是指在Linux操作系统中为了提高系统运行效率而设置的一块特殊的硬盘空间，也称为虚拟内存。当系统内存不足时，会将一部分不常用的内存数据存储到swap分区中，以释放内存空间，从而保证系统的稳定运行。 swap分区的大小一般设置为物理内存大小的两倍，但也可以

    2024年02月10日
    浏览(23)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包