Oracle中left join中右表的限制条件

古城微笑少年丶 2021-06-26 16:06 347阅读 0赞

## 无过滤条件的`LEFT JOIN` ##

SQL中最简单形式的`LEFT JOIN`，是直接根据关联字段，以左表为基准，对右表进行匹配。在`SELECT`语句中选取的字段，如果有右表的记录（一般都是需要右表的某些记录的），取出配对成功的右表记录中对应的这个字段的值；否则，直接置`NULL`。这本身就是`LEFT JOIN`的特点：保证左表记录完整，右表只是辅助匹配。

直接看例子，为了演示，准备了两张测试表test1，test2：

select * from test1

![在这里插入图片描述][20181230152234803.png]

select * from test2

![在这里插入图片描述][20181230152300850.png]

select t2.*,t1.market_place_id from test2 t2   
    left join test1 t1  
    on t2.parent_asin=t1.asin

![在这里插入图片描述][20181230152322343.png]  
对照上面例子解释一下这个结果：以左表`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’的右表记录。查询结果如下。

![在这里插入图片描述][20181230153050888.png]

逻辑语义上，这个结果相当于右表`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语句的执行计划：  
![在这里插入图片描述][20181230153158888.png]  
这里可以看出，原来的`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'

上面语句的执行结果如下：  
![在这里插入图片描述][20181230153313758.png]

逻辑语义上，所有的`market_place_id1!='2'`的记录（包括`NULL`）全部被过滤掉了。  
性能上，再来看一下这条语句的执行计划：  
![在这里插入图片描述][20181230153339148.png]  
由上面的执行计划可以看出，Oracle也是首先对右表`test1`进行了`market_place_id`的过滤，但是过滤之后`JOIN`操作已经不是`LEFT JOIN`了，而是变成了普通的INNER JOIN。这就解释了为什么最后的结果集只有两条记录。  
同样思路，就本例而言，在右表test的`market_place_id`字段上建立`INDEX`，同样可以达到优化`SQL`的目的，以下是建立`INDEX`之后的SQL执行计划：  
![在这里插入图片描述][20181230153550715.png]

## 结论： ##

在使用`LEFT JOIN`时，右表的限制条件，在`ON`和`WHERE`字句中出现，逻辑上的语义完全不同。  
过滤条件在`ON`子句中出现时，不会改变原来`LEFT JOIN`的执行语义：以左表为基表。  
过滤条件在`WHERE`字句中出现时，已经改变了原来`LEFT JOIN`的语义，相当于在最后`LEFT JOIN`的结果集里面再做了一次`WHERE`条件的过滤，所以已经丧失的`LEFT JOIN`的原始语义。  
性能上，其实两者并没有本质的区别，扫描路径完全一致，只是对于后者，`Oracle`的内部实现，巧妙的将上面描述的语义转换为了通过`INNER JOIN`实现。这样就保证了在真正执行时还是首先进行内层过滤，缩小右表的数据集，然后进行外层`INNER JOIN`。  
所以使用`LEFT JOIN`是，有需求对右表进行过滤时，要格外小心了。

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

[20181230152234803.png]: /images/20210626/377996bde10243bf88ee291b7f5de7df.png
[20181230152300850.png]: /images/20210626/a27ab5178a1540c3b3b77adaf57c9f47.png
[20181230152322343.png]: /images/20210626/2386469aacde448288bcf3235e89a80e.png
[20181230153050888.png]: /images/20210626/2dd9438494d7406b8f36e67b6702408e.png
[20181230153158888.png]: /images/20210626/7b0e1df33cc148e2904fc77fb7ed2a2e.png
[20181230153313758.png]: /images/20210626/30c98223e90241cea2201a6c2b44e421.png
[20181230153339148.png]: /images/20210626/2975478ce24647babad1958495e815de.png
[20181230153550715.png]: /images/20210626/c2aa9e6faaab44ee80f7d8d8c72d1897.png