专栏首页bisal的个人杂货铺left/right join中on和where的区别

left/right join中on和where的区别

开发同学提了个问题,如下两种left join中on和where条件的写法是否等价?

select * from j_a left join j_b on j_a.id=j_b.id where j_a.name='b';
select * from j_a left join j_b on j_a.id=j_b.id and j_a.name='b';

我们先看测试,创建两张测试表,导入一些数据,

SQL> create table j_a(id number, name varchar2(1));
Table created.


SQL> create table j_b(id number, name varchar2(1));
Table created.


SQL> select * from j_a;
        ID N
---------- -
         1 a
         2 b
         3 c


SQL> select * from j_b;
        ID N
---------- -
         2 d
         3 e
         5 o

为了比较,先看下join全连接,共有2条记录,

SQL> select * from j_a join j_b on j_a.id=j_b.id;
        ID N         ID N
---------- - ---------- -
         2 b          2 d
         3 c          3 e

使用left join,会显示j_a表的3条记录,其中j_a.id=1的记录,对应j_b为空,

SQL> select * from j_a left join j_b on j_a.id=j_b.id;
        ID N         ID N
---------- - ---------- -
         2 b          2 d
         3 c          3 e
         1 a

使用on,得到3条记录,

SQL> select * from j_a left join j_b on j_a.id=j_b.id and j_a.name='b';


        ID N         ID N
---------- - ---------- -
         2 b          2 d
         3 c
         1 a

使用where,得到1条记录,

SQL> select * from j_a left join j_b on j_a.id=j_b.id where j_a.name='b';
        ID N         ID N   
---------- - ---------- -
         2 b          2 d

从测试结论看,left join使用on和where得到的结果集是不相同的。

究其原因,是两种关键字执行的时间点有所区别。

(1) on条件是在left join生成临时表时执行的,因此无论on中的条件是否为真,都会返回左边表中的所有记录,所以上述测试中,得到3条记录。

(2) where条件是在left join临时表生成后,再对临时表进行过滤,此时是没有left join的含义了,条件不为真的就会被过滤,所以上述测试中,得到1条记录。

因此,之所以on和where的测试结果不同,这和left join、right join的特性是有关的,因为on的条件无论是否为真,都会返回left或right表中的记录。

当然,非得用这种写法,使用is not null,还是能让on和where得到相同的结果集,

select * from j_a left join j_b on j_a.id=j_b.id and j_a.name='b' and j_b.id is not null;

如果是join/full join,他是left join和right join的并集,所以使用on和where是相同的结果。

使用join和on,得到1条记录,

SQL> select * from j_a join j_b on j_a.id=j_b.id and j_a.name='b';
        ID N         ID N
---------- - ---------- -
         2 b          2 d

这是使用join和where,得到1条记录,

SQL> select * from j_a join j_b on j_a.id=j_b.id where j_a.name='b';
        ID N         ID N
---------- - ---------- -
         2 b          2 d

对待问题,从原理的理解,加上实际的测试,才可能让你抓到问题的本质,才可能让他成为你真正掌握的知识。不仅是Oracle,还是其他的技术,又或是任何其他的领域,都是如此。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • select from update row的实现

    DTCC大会上,阿里江疑的演讲中提到一个:select from update hot row;

    bisal
  • EDB分区表的又一个“坑”

    下周有一个应用上线,其中涉及一个夜维删除逻辑的应用,大体功能是按照时间删除一张表的历史数据,这张表的主键是另外一张时间分区表的外键,使用的是EDB(9.2)数据...

    bisal
  • connect by超乎你想象

    SQL中的connect by主要用在层级关系的查询,乍看确实可能有些绕,但在某些场景下,确实方便,语法格式如下,

    bisal
  • 分享大厂分布式唯一ID设计方案,为何搞的这么复杂?

    很多人看了分布式唯一 ID 相关的文章,觉得都设计的非常复杂,大厂的分布式唯一ID生成方案为什么要设计的这么复杂?看完本篇文章,希望能够给你解惑!

    业余草
  • 备忘:base 标签和ShowModalDialog 、showModelessDialog

    在是用ShowModalDialog 弹出子窗体中在标签后,加入<base target="_self" />后,对于子窗体: 1、POSTBACK后不会打开新...

    Jianbo
  • mybatis教程之原理剖析

      MyBatis是目前非常流行的ORM框架,功能很强大,然而其实现却比较简单、优雅。本文通过代理的方式来看下其实现

    用户4919348
  • 如何利用周六休息日狂赚两万欧元?

    近期,众测平台Intigriti组织了一场漏洞测试比赛,作者和朋友共同努力在周六休息日,发现了目标系统的两个IDOR防护机制绕过漏洞,收获了€20,000的漏洞...

    FB客服
  • Angular4记账webApp练手项目之二(在angular4项目中使用Angular WeUI)

    安装后还需要引用weui 样式以及我们的font-awesome图标苦,在我们项目index.html中引用

    易兒善
  • JDBC访问MySQL数据库踩坑

    作为一个 Android 开发者,闲来无事,想着使用 JSP + Servlet 写一些简单的接口,然后通过前端调用接口,后端的数据库使用的是 MySQL。

    Vance大飞
  • svm 之 线性可分支持向量机

    Echo_fy

扫码关注云+社区

领取腾讯云代金券