left join on,where中的过滤条件放置问题

左手的ㄟ右手 2023-10-03 13:23 29阅读 0赞

## left join on中的过滤条件对左表无效 ##

比如有以下sql联表查询命令：

select *
        from `scenario_holder` SH left join `condition_holder` CH on SH.id=CH.scenario_holder_id 
        and CH.policy_holder_id = 0 and SH.`deleted` = 1

查看查询结果，`CH.policy_holder_id = 0`这句起了作用，而`SH.`deleted`= 1`这句则没起作用。

所以left join on中的条件不会过滤左表？而我对inner join做了同样的实验，其on中的过滤条件对左表是有作用的

以此类推，我认为[这篇文章][Link 1]应该是对的：

**left join:以左表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对左表无效  
right join:以右表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对右表无效  
full join:以左表为基准，根据on条件过滤连接生成临时表，on后面的过滤条件对左右表无效  
inner join:等值连接，根据过滤条件生成临时表。on后面的条件 可以用 where实现**

## 应谨慎把对left join右表的过滤条件放在where后 ##

比如有以下sql联表查询命令：

select
        *
        from `policy_holder` PH
        left join `condition_holder` CH on PH.id=CH.policy_holder_id
        and PH.scenario_holder_id = CH.scenario_holder_id  
        and PH.store_id=CH.store_id                        
        where
          PH.store_id=131610 and PH.scenario_holder_id in (129591724)
          and PH.`status` in (0,1) and PH.`deleted` in (0)  #1
          and (CH.`status` is NULL or CH.`status` in (0,1)) #2
          and (CH.`deleted` is NULL or CH.`deleted` in (0)) #3

这条命令的本意是左表PH只要能在本表中找到满足过滤条件1的记录，就应该有返回结果，而不管右表CH中是否有匹配的记录，这也是用left join的初衷。结果因为第2,3句的过滤作用，导致某些情况下左表的记录即使满足本表的过滤条件1,也不会有返回了。把第2,3句从where中提到on中可以修复此问题：

select
        *
        from `policy_holder` PH
        left join `condition_holder` CH on PH.id=CH.policy_holder_id
        and PH.scenario_holder_id = CH.scenario_holder_id  
        and PH.store_id=CH.store_id 
        and (CH.`status` in (0,1)) #2
        and (CH.`deleted` in (0)) #3                       
        where
          PH.store_id=131610 and PH.scenario_holder_id in (129591724)
          and PH.`status` in (0,1) and PH.`deleted` in (0)  #1

## 总结 ##

对left join而言，对左表的过滤条件不应放在on中而是where中。而右表的过滤条件，当你希望它不要过滤掉一些能左表中存在但匹配不到右表的记录，请把它放置在on中而不是where中。

[Link 1]: https://blog.csdn.net/zengcong2013/article/details/78131569?utm_medium=distribute.pc_relevant.none-task-blog-2~default~OPENSEARCH~default-1.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~OPENSEARCH~default-1.control