【数据库】HIVE SQL -left join (on 和 where条件放置的区别)

在使用left join的过程中,总是遇到一个问题,就是将条件放在on中还是where条件中。
在查过一些资料和实际操作后,总结了一下:
       在多张表连接时,都会生成一张中间表,然后再将这张临时表返回给用户。
       在用left join中,on和where条件的区别如下:
       (1)on条件是在生成中间表时使用的条件,它不管on中的条件是否为真,都会返回左表中的记录。
       (2)where条件是在中间表生成好之后,再对这张表进行果过滤,这时已经和left join没有关系,不符合where条件的就会被过滤掉。

案例:

select a.*,b.no_b,b.status
  from (select no
          from t1
         where d = '2019-04-01'
           and no in (1686475705, 1743166512, 1780711122)
       ) a
  left join (select no, status
               from t2
              where d = '2019-04-01'
                and no in (1686475705, 1743166512, 1780711122)
            ) b
    on a.no = b.no
   and b.status <> 'C'

 结果:

no                       no_b                  status
1686475705      null                  null
1743166512      1743166512      s
1780711122      1780711122       s
select a.*
  from (select no
          from t1
         where d = '2019-04-01'
           and no in (1686475705, 1743166512, 1780711122)
       ) a
  left join (select no, status
               from t2
              where d = '2019-04-01'
                and no in (1686475705, 1743166512, 1780711122)
            ) b
    on a.no = b.no
 where b.status <> 'C'

结果:

no
1743166512
1780711122

   其实,关键原因就是left join,right join,full join的特殊性,不管on上的条件是否为真都会返回left或right表中的记录,full则具有left和right的`在这里插入代码片`特性的并集。 而inner jion没这个特殊性,则条件放在on中和where中,返回的结果集是相同的。
    left join的where和join有执行的先后顺序,若条件写在on中,就是先过滤这其中的条件,过滤完之后在按照left join执行,所以第一段代码,就相当于先把status <> 'C'的过滤掉,再和a表关联,实际返回的结果应该是a中的所有记录。

   当该条件在where中时,就是先执行完left join的操作,对于返回的结果再进行过滤。


 

  • 5
    点赞
  • 12
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
左半连接(left semi join)是一种SQL连接操作,它是基于in关键字的关系。左半连接与普通的连接(join)有一些区别。在左半连接中,如果右表中有重复记录,左表会跳过这些重复记录,而普通连接会遍历所有的重复记录。这导致在右表有重复值的情况下,左半连接只会产生一条结果,而普通连接会产生多条结果。因此,左半连接的性能通常比普通连接更高。\[1\]\[2\] 需要注意的是,左半连接的限制是,右表只能在连接条件(ON子句)中设置过滤条件,不能在WHERE子句、SELECT子句或其他地方进行过滤。如果在其他地方进行过滤,会导致语法错误。\[3\] #### 引用[.reference_title] - *1* [HiveSql&SparkSql —— 使用left semi join做in、exists类型子查询优化](https://blog.csdn.net/qq_41018861/article/details/117651627)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* *3* [Hive中HSQLleft semi join](https://blog.csdn.net/weixin_44441757/article/details/117950982)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^koosearch_v1,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值