left/right join中on和where的区别
- 2020 年 3 月 18 日
- 筆記
开发同学提了个问题，如下两种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，还是其他的技术，又或是任何其他的领域，都是如此。
