oracle left join 查询on中出现and条件

结论：

使用Join和where 一致

1、在使用LEFT JOIN时，右表的限制条件，在ON和WHERE字句中出现，逻辑上的语义完全不同。

当on中对右表的非连接字段限制时(on (tab1.size1= tab2.size1 and tab2.name='AAA')) 相当于右表根据非连接字段限制获取结果，然后左表再与它关联。
select tab1.*,tab2.* from tab1 left join tab2 on (tab1.size1= tab2.size1 and tab2.name='AAA');
相当于
select tab1.*,t.* from tab1 left join (select * from tab2 where tab2.name='AAA') t on (tab1.size1= t.size1);

2、过滤条件在ON子句中出现时，不会改变原来LEFT JOIN的执行语义：以左表为基表。

3、过滤条件在WHERE字句中出现时，已经改变了原来LEFT JOIN的语义，相当于在最后LEFT JOIN的结果集里面再做了一次WHERE条件的过滤，所以已经丧失的LEFT JOIN的原始语义。

性能上，其实两者并没有本质的区别，扫描路径完全一致，只是对于后者，Oracle的内部实现，巧妙的将上面描述的语义转换为了通过INNER JOIN实现。这样就保证了在真正执行时还是首先进行内层过滤，缩小右表的数据集，然后进行外层INNER JOIN。

所以使用LEFT JOIN是，有需求对右表进行过滤时，要格外小心了。

备注：

以上测试使用Oracle 11g，更老版本的优化器的执行计划可能会不同。但最终语义上不会有差别。