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，还是其他的技术，又或是任何其他的领域，都是如此。
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020/03/16 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
