前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >专栏 >left/right join中on和where的区别

left/right join中on和where的区别

作者头像
bisal
发布于 2020-03-18 03:31:40
发布于 2020-03-18 03:31:40
76400
代码可运行
举报
运行总次数: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 删除。

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

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
从ORA-01752的错误,透过现象看本质
这几天开发同学反映了一个问题,有一个Java写的夜维程序,用于每天定时删除历史过期数据,3月10日之前经过了内测,但这两天再次执行的时候,有一条SQL语句一直报ORA-01752的错误,由于近期做过一次开发库的迁移,从一个机房搬迁至另一个机房,而且开发同学确认这期间未变代码逻辑,所以怀疑是否和数据迁移有关,这个错误被测试同学提为了bug,卡在版本测试中,有可能造成进度延误,所以属于比较紧急的问题。
bisal
2019/01/29
1.1K0
【DB笔试面试517】在Oracle中,什么是临时表?它有哪些分类?有关临时表需要注意什么?
目前所有使用Oracle作为数据库支撑平台的应用,大部分是数据量比较庞大的系统,即表的数据量级一般情况下都是在百万级以上。当然,在Oracle中创建分区是一种不错的选择,但是当发现应用有多张表关联的时候,并且这些表大部分都比较庞大,而关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小,并且查询得到这个结果集的速度非常快,那么这个时候考虑在Oracle中创建“临时表”。
AiDBA宝典
2019/09/29
1.2K0
left join on and 与 left join on where的区别
数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。
wuweixiang
2018/08/14
1.2K0
面试官:left join 后用 on 和 where 有什么区别?
哈喽,我是狗哥。前天写 SQL 时本想通过 A left B join on and 后面的条件来使查出的两条记录变成一条,奈何发现还是有两条。
JavaFish
2022/01/17
6110
面试官:left join 后用 on 和 where 有什么区别?
SQL语句中 left join 后用 on 还是 where,区别大了!
前天写SQL时本想通过 A left B join on and 后面的条件来使查出的两条记录变成一条,奈何发现还是有两条。
捡田螺的小男孩
2021/06/15
1.2K0
SQL语句中 left join 后用 on 还是 where,区别大了!
多表关联查询过滤条件写在on与where后的区别
SQL优化过程中,发现开发人员在写多表关联查询的时候,对于谓词过滤条件的写法很随意,写在on后面与where后面的情况均有,这可能会导致没有理解清楚其真正的含义而无法得到期望的结果。
数据和云
2021/09/23
4.8K0
多表关联查询过滤条件写在on与where后的区别
Oracle存储过程(while循环、for循环、if判断、sql拼接、游标)
用户10358987
2024/04/23
7350
Oracle存储过程(while循环、for循环、if判断、sql拼接、游标)
Join,left join,right join(1)--连接原理(三十九)
前面说了mysql优化器访问数据库的方法有const,ref,ref_or_null,range,index,all。然后又分为条件全部是索引回表查询,和条件有非索引查询,则需要回表之后,在过滤。又有intersection合并索引和union并集索引,当两个单独二级索引查询,不是联合索引查询,可能会触发这两个索引查询,用and是intersection,用or是union查询,触发有两个注重点:
用户9919783
2022/07/26
4610
Oracle的批量插入操作
MySQL中支持一条SQL语句执行批量插入,Oracle中支持的形式有些不同,但是殊途同归,用的就是insert all into语法。
bisal
2020/11/12
2.6K0
Oracle表连接
  内连接:查询满足条件的结果集,是相对于外连接来说的(条件的话由很多种可以等于、不等于、大于、小于等等)。
翎野君
2023/05/12
7240
SQL中JOIN时条件放在Where和On的区别
SQL中JOIN子句是用于把来自两个或多个表的数据连接起来,在这个过程中可能会添加一些过滤条件。昨天有小伙伴问,如下图的这两种SQL写法查询结果是否会一样?(好像这是某一年阿里的面试题)
Python数据科学
2020/11/11
3.5K0
SQL中JOIN时条件放在Where和On的区别
谈一谈in/exists , not in/not exists
这个话题应该很多人写过了,多数是在分析in/exists哪个写法效率高;not in/not exists是否等价等问题。老虎刘这里再补充点内容。
老虎刘
2022/06/22
5970
left join-on-and 与 left join-on-where 和 inner join on 加条件和where加条件的区别
在多表查询时,on 比 where 更早起作用。系统首先根据各个表之间的联接条件,把多个表合成一个临时表后,再由 where 进行过滤,然后再计算,计算完后再由 having 进行过滤。由此可见,要想过滤条件起到正确的作用,首先要明白这个条件应该在什么时候起作用,然后再决定放在那里。
botkenni
2023/05/23
2.6K0
left join-on-and 与 left join-on-where 和 inner join on 加条件和where加条件的区别
SQL中 LEFT JOIN 左表合并去重实用技巧
前两天刚遇到这个问题,当时是用group by去重的。昨天遇到了大佬发的去重技巧,特此记录一下。
cultureSun
2023/05/18
1K0
SQL中 LEFT JOIN 左表合并去重实用技巧
SQL优化 21 连击 + 思维导图
比如,存储字符串“101”,对于char(10),表示你存储的字符将占10个字节(包括7个空字符),在数据库中它是以空格占位的,而同样的varchar2(10)则只占用3个字节的长度,10只是最大值,当你存储的字符小于10时,按实际长度存储。
Java小咖秀
2022/09/23
8100
新增字段在数据块中的体现
前几天同事提了一个问题,比较有意思,如果一张表新增字段,在数据块上是怎么存储的?是直接“加”到数据块中,还是通过其他的形式,表示新的字段?让我们从Oracle数据块内容,看下他到底是怎么存储的。
bisal
2021/09/06
1K0
京东面试官问:LEFT JOIN 关联表中用 ON 还是 WHERE 跟条件有什么区别?
之前有码友去京东面试,被问到 LEFT JOIN 关联表中用 ON 还是 WHERE 跟条件有什么区别,很快就答出来了,可是追问什么原因造成这一情况的,一时没回答上来。
JAVA葵花宝典
2021/05/11
4280
京东面试官问:LEFT JOIN 关联表中用 ON 还是 WHERE 跟条件有什么区别?
【转】MySQL 多表Join条件在ON AND 和 Where的写法差异
在MySQL中,多表Join是一种常见的操作,它允许从多个表中根据相关联的列,来组合提取数据。MySQL中多表关联也是支持,多种方式.。比如内连接,左链接,右链接,笛卡尔积等方式。特别是在左右链接下,不同的写法颠覆了对SQL语句处理的理解。
保持热爱奔赴山海
2024/11/26
2840
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.1K0
Oracle的常见问题汇总(1)——​Oracle中的JOIN的整理和结构分析
一道SQL考题的思考
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
bisal
2019/12/10
3860
一道SQL考题的思考
推荐阅读
相关推荐
从ORA-01752的错误,透过现象看本质
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
查看详情【社区公告】 技术创作特训营有奖征文
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验