不一样的.下面的一条相当于内连接.因为b中不符合条件的记录在结果中b.b1 is null, 不会复合b.b1=3这一条. 所以会被过滤掉.
select * from a left join b on a.a1 =b.b1 and b.b1 = 3 与 select * from a left join b on a.a1 =b.b1 where b.b1 = 3 不一样的机制,可以试着多个字段组成的主键就可以更加清楚
两个SQL的结果是不一样的 select * from a left join b on a.a1 =b.b1 and b.b1 = 3 等价于 select * from a,b where a.a1=b.b1(+) and b.b1(+)=3select * from a left join b on a.a1 =b.b1 where b.b1 = 3 等价于 select * from a,b where a.a1=b.b1(+) and b.b1 = 3
所以会被过滤掉.
select * from a left join b on a.a1 =b.b1 and b.b1 = 3
与
select * from a left join b on a.a1 =b.b1 where b.b1 = 3 不一样的机制,可以试着多个字段组成的主键就可以更加清楚
select * from a left join b on a.a1 =b.b1 and b.b1 = 3
等价于
select * from a,b where a.a1=b.b1(+) and b.b1(+)=3select * from a left join b on a.a1 =b.b1 where b.b1 = 3
等价于
select * from a,b where a.a1=b.b1(+) and b.b1 = 3