首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >left/right join中on和where的区别

left/right join中on和where的区别

作者头像
bisal
发布于 2020-03-18 03:31:40
发布于 2020-03-18 03:31:40
90000
代码可运行
举报
运行总次数:0
代码可运行

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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';

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

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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条记录,

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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为空,

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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条记录,

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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条记录,

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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得到相同的结果集,

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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条记录,

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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条记录,

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
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,还是其他的技术,又或是任何其他的领域,都是如此。

本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2020/03/16 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
什么?inner join比left join更快?
在数据密集型应用中,优化数据库查询性能是提高系统响应速度的关键因素之一。INNER JOIN 和 LEFT JOIN 是常用的SQL连接操作,但它们在性能上的表现常常引发争议。很多开发者在面对复杂的查询需求时,不确定该选择哪种连接方式来获得最佳的性能。本文将通过性能分析、实际案例和优化技巧,帮助你解答这个问题,确保你的数据库查询既高效又可靠。
一只牛博
2025/05/31
3160
什么?inner join比left join更快?
Oracle的常见问题汇总(1)——​Oracle中的JOIN的整理和结构分析
oracle中的join的整理和结构分析 在Oracle中的join主要分为: 外连接(outter join),内连接(inner join),自身连接(self-join) 外连接(outter join)又分为左外连接(left outer join)、右外连接(right outer join)、全外连接(full outer join)。在sql语句(structured Query Language)是:table_1 left/right/full outer join table_2,通常我
用户1257215
2018/01/30
1.2K0
Oracle的常见问题汇总(1)——​Oracle中的JOIN的整理和结构分析
一次有意思的错选执行计划问题定位(涉及SYS_OP_C2)
1. 11g的库,话说有一个应用程序新上线,应用中使用了绑定变量的方式执行一条简单的SQL,例如select a from b where c = :x,c列是该表复合主键的前导列,表定义是varchar2类型,从spotlight监控看这条SQL的执行计划是全表扫描,一次执行要1个小时,这张表是运行很久的引用分区表,数据量是亿级,测试的时候正常,但很显然测试的数据量可能和生产非常不一致,导致没察觉。
bisal
2019/01/29
6820
Oracle存储过程(while循环、for循环、if判断、sql拼接、游标)
用户10358987
2024/04/23
1.2K0
Oracle存储过程(while循环、for循环、if判断、sql拼接、游标)
从ORA-01752的错误,透过现象看本质
这几天开发同学反映了一个问题,有一个Java写的夜维程序,用于每天定时删除历史过期数据,3月10日之前经过了内测,但这两天再次执行的时候,有一条SQL语句一直报ORA-01752的错误,由于近期做过一次开发库的迁移,从一个机房搬迁至另一个机房,而且开发同学确认这期间未变代码逻辑,所以怀疑是否和数据迁移有关,这个错误被测试同学提为了bug,卡在版本测试中,有可能造成进度延误,所以属于比较紧急的问题。
bisal
2019/01/29
1.2K0
SQL中 LEFT JOIN 左表合并去重实用技巧
前两天刚遇到这个问题,当时是用group by去重的。昨天遇到了大佬发的去重技巧,特此记录一下。
cultureSun
2023/05/18
1.3K0
SQL中 LEFT JOIN 左表合并去重实用技巧
复盘eygle在甲骨文大会上演讲中的示例,看看什么是大师的由点及面
盖总(eygle)在刚结束的甲骨文大会的演讲中,通过一个简单的UPDATE语句,为我们展示了什么叫由点及面的优化,什么叫由点及面的知识覆盖度,不在于这个案具体如何操作,更应关注或更值得我们借鉴的是这种学习态度和方法思路,大师是如何炼成的?我想这个案例可以带给我们一些启迪。
bisal
2019/01/29
5690
京东面试官问:LEFT JOIN 关联表中用 ON 还是 WHERE 跟条件有什么区别?
之前有码友去京东面试,被问到 LEFT JOIN 关联表中用 ON 还是 WHERE 跟条件有什么区别,很快就答出来了,可是追问什么原因造成这一情况的,一时没回答上来。
JAVA葵花宝典
2021/05/11
4680
京东面试官问:LEFT JOIN 关联表中用 ON 还是 WHERE 跟条件有什么区别?
SQL优化 21 连击 + 思维导图
比如,存储字符串“101”,对于char(10),表示你存储的字符将占10个字节(包括7个空字符),在数据库中它是以空格占位的,而同样的varchar2(10)则只占用3个字节的长度,10只是最大值,当你存储的字符小于10时,按实际长度存储。
Java小咖秀
2022/09/23
8910
一道SQL考题的思考
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
bisal
2019/12/10
4090
一道SQL考题的思考
SQL中 LEFT JOIN 左表合并去重实用技巧
MySQL left join 语句格式为:A LEFT JOIN B ON 条件表达式
Spark学习技巧
2022/01/13
1.9K0
SQL中 LEFT JOIN 左表合并去重实用技巧
Join,left join,right join(1)--连接原理(三十九)
前面说了mysql优化器访问数据库的方法有const,ref,ref_or_null,range,index,all。然后又分为条件全部是索引回表查询,和条件有非索引查询,则需要回表之后,在过滤。又有intersection合并索引和union并集索引,当两个单独二级索引查询,不是联合索引查询,可能会触发这两个索引查询,用and是intersection,用or是union查询,触发有两个注重点:
keying
2022/07/26
5190
SQL优化 21 连击
比如,存储字符串“101”,对于char(10),表示你存储的字符将占10个字节(包括7个空字符),在数据库中它是以空格占位的,而同样的varchar2(10)则只占用3个字节的长度,10只是最大值,当你存储的字符小于10时,按实际长度存储。
公众号: 云原生生态圈
2022/11/02
7680
SQL语句中 left join 后用 on 还是 where,区别大了!
前天写SQL时本想通过 A left B join on and 后面的条件来使查出的两条记录变成一条,奈何发现还是有两条。
捡田螺的小男孩
2021/06/15
1.3K0
SQL语句中 left join 后用 on 还是 where,区别大了!
谈一谈in/exists , not in/not exists
这个话题应该很多人写过了,多数是在分析in/exists哪个写法效率高;not in/not exists是否等价等问题。老虎刘这里再补充点内容。
老虎刘
2022/06/22
6930
多表关联查询过滤条件写在on与where后的区别
SQL优化过程中,发现开发人员在写多表关联查询的时候,对于谓词过滤条件的写法很随意,写在on后面与where后面的情况均有,这可能会导致没有理解清楚其真正的含义而无法得到期望的结果。
数据和云
2021/09/23
5.4K0
多表关联查询过滤条件写在on与where后的区别
面试官:left join 后用 on 和 where 有什么区别?
哈喽,我是狗哥。前天写 SQL 时本想通过 A left B join on and 后面的条件来使查出的两条记录变成一条,奈何发现还是有两条。
JavaFish
2022/01/17
6900
面试官:left join 后用 on 和 where 有什么区别?
left join-on-and 与 left join-on-where 和 inner join on 加条件和where加条件的区别
在多表查询时,on 比 where 更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由 where 进行过滤,然后再计算,计算完后再由 having 进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里。
botkenni
2023/05/23
3.4K0
left join-on-and 与 left join-on-where 和 inner join on 加条件和where加条件的区别
Oracle的批量插入操作
MySQL中支持一条SQL语句执行批量插入,Oracle中支持的形式有些不同,但是殊途同归,用的就是insert all into语法。
bisal
2020/11/12
2.7K0
oracle 笔记
如果你想查看一下数据库的时间该怎么办呢?你需要执行一个 SQL 语句,但是 SQL 语句语法规定需要指定一个表,为此 Oracle 设计了一个只有一行一列的表 DUAL,我们可以使用这个表来执行一些不需要表的 SQL 语句。
Remember_Ray
2020/08/05
4.4K0
推荐阅读
相关推荐
什么?inner join比left join更快?
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验