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，还是其他的技术，又或是任何其他的领域，都是如此。