Oracle中leftjoin中右表的限制条件
⽆过滤条件的LEFT JOIN
SQL中最简单形式的LEFT JOIN，是直接根据关联字段，以左表为基准，对右表进⾏匹配。在SELECT语句中选取的字段，如果有右表的记录（⼀般都是需要右表的某些记录的），取出配对成功的右表记录中对应的这个字段的值；否则，直接置NULL。这本⾝就是LEFT JOIN的特点：保证左表记录完整，右表只是辅助匹配。
直接看例⼦，为了演⽰，准备了两张测试表test1，test2：
select*from test1
select*from test2sql left join 多表连接
select t2.*,t1.market_place_id from test2 t2
left join test1 t1
on t2.parent_asin=t1.asin
对照上⾯例⼦解释⼀下这个结果：以左表test2为基准，⽤右表test1的asin字段和test2的parent_asin字段进⾏匹配，取出test2的全部数据和test1的market_place_id字段。对于test2中的第⼀条记录，因为右表中有两条记录符合的asin='parent1'，只是market_place_id不同⽽已（分别为1、2）。于是这两条记录都会作为符合条件的记录加⼊结果集。这时，虽然是以左表为基准，但是这条记录却在结果集中产⽣了两条对应的记录。这点要稍加注意：以左表为基准并不意味着结果集的记录数量=左表的记录数量！
再回过头来看结果集的5条记录，由ID字段可以很好的区分出每条记录是由左表的哪条记录对应产⽣的。这⾥，最后两条记录可以很好的体现出LEFT JOIN的特点。
右表有滤条件的LEFT JOIN
这⾥，我们忽略左表有过滤条件场景的讨论，因为在LEFT JOIN中左表作为基准表，对他的过滤直接反应在SQL的WHERE字句中，效果上也相当于单表SELECT的WHERE字句过滤，缩⼩左表范围后，
再和右表做JOIN，没什么悬念。
但是对于右表的过滤，通常有两种主要的⽅式：在ON字句中加⼊过滤条件或者在LEFT JOIN之后的WHERE字句中加⼊过滤条件。对于这两种⽅式的对⽐，下⾯主要针对逻辑语义和实现性能上加以对⽐。
过滤条件在ON字句中
select t2.*,t1.market_place_id from test2 t2
left join test1 t1
on t2.parent_asin=t1.asin and t1.market_place_id='2'
上⾯这条SQL加上了对右表test1中market_place_id的过滤条件：只关⼼market_place_id为‘2’的右表记录。查询结果如下。
逻辑语义上，这个结果相当于右表test1⾸先进⾏了条件过滤，只剩下两条记录[(2,‘parent1’,‘2’),(3,‘parent2’,‘2’)]，然后左表test2和这个过滤之后的结果集进⾏⽆过滤条件的LEFT JOIN，于是得到了上图的结果。
性能上，来看⼀下这条语句的执⾏计划截图
可以看出，T1确实先以2为标准对market_place_id做了⼀次过滤，然后，在外层，再做原来的LEFT JOIN。由此可以证实上⾯逻辑语义结果的展⽰，同时也可以发现，就本例⽽⾔，如果能够在market_place_id上建⽴index，可以直接避免内层过滤对右表进⾏的全表扫描，从⽽提⾼整个SQL的执⾏效率。下图为在market_place_id上建⽴index之后，同样SQL语句的执⾏计划：
这⾥可以看出，原来的TABLE FULL SCAN 已经被换成了INDEX的RANGE SCAN，从⽽也直接导致了Oracle的优化器在最外层的Hash Join替换为了Nested Loops。（当然这个join的⽅式并不能说明什
么问题，因为毕竟测试⽤的数据集太⼩，完全有可能在⼤数据集的真实情况下，优化器根据统计信息还是最终使⽤Hash Join算法）
过滤条件在WHERE⼦句中
select t2.*,t1.market_place_id from test2 t2
left join test1 t1
on t2.parent_asin=t1.asin
where t1.market_place_id='2'
上⾯语句的执⾏结果如下：
逻辑语义上，所有的market_place_id1!='2'的记录（包括NULL）全部被过滤掉了。
性能上，再来看⼀下这条语句的执⾏计划：
由上⾯的执⾏计划可以看出，Oracle也是⾸先对右表test1进⾏了market_place_id的过滤，但是过滤之后JOIN操作已经不是LEFT JOIN了，⽽是变成了普通的INNER JOIN。这就解释了为什么最后的结果集只有两条记录。
同样思路，就本例⽽⾔，在右表test的market_place_id字段上建⽴INDEX，同样可以达到优化SQL的⽬的，以下是建⽴INDEX之后的SQL 执⾏计划：
结论：
在使⽤LEFT JOIN时，右表的限制条件，在ON和WHERE字句中出现，逻辑上的语义完全不同。
过滤条件在ON⼦句中出现时，不会改变原来LEFT JOIN的执⾏语义：以左表为基表。
过滤条件在WHERE字句中出现时，已经改变了原来LEFT JOIN的语义，相当于在最后LEFT JOIN的结果集⾥⾯再做了⼀次WHERE条件的过滤，所以已经丧失的LEFT JOIN的原始语义。
性能上，其实两者并没有本质的区别，扫描路径完全⼀致，只是对于后者，Oracle的内部实现，巧妙的将上⾯描述的语义转换为了通
过INNER JOIN实现。这样就保证了在真正执⾏时还是⾸先进⾏内层过滤，缩⼩右表的数据集，然后进⾏外层INNER JOIN。
所以使⽤LEFT JOIN是，有需求对右表进⾏过滤时，要格外⼩⼼了。
备注：
以上测试使⽤Oracle 11g，更⽼版本的优化器的执⾏计划可能会不同。但最终语义上不会有差别。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。