left semi join 和 left join 区别

た入场券 2022-09-11 14:14 288阅读 0赞

### left semi join 和 left join 区别 ###

*  对比
 *  执行计划
 *  小结

# 对比 #

`a表`和`b表`通过`user_id`关联

*  `a表`数据

select * from wedw_dw.t_user;

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

*  `b表`数据

select * from wedw_dw.t_order;

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

*  left join

select
    *
    from wedw_dw.t_user as t1
    left join wedw_dw.t_order as t2
    on t1.user_id = t2.user_id;

如图所示：`a表`和`b表`匹配的字段都会展示出来  
![在这里插入图片描述][7d4bfed34df94a66b711c973e65f0843.png]

*  left semi join

select
    *
    from wedw_dw.t_user as t1
    left semi join wedw_dw.t_order as t2
    on t1.user_id = t2.user_id;

如图所示：只能展示`a表`的字段，因为`left semi join` 只传递表的 `join key` 给 map 阶段  
![在这里插入图片描述][514b905e63864a77af773fc24dcbfdf6.png]

*  in

select
    *
    from wedw_dw.t_user as t1
    where t1.user_id in (select user_id from wedw_dw.t_order);

如图所示：发现效果和`left semi join`是一样的  
![在这里插入图片描述][8cd83f7e50d942ae9d2ca5b9d926301e.png]

*  inner join

select
    t1.*
    from wedw_dw.t_user as t1
    inner join wedw_dw.t_order as t2
    on t1.user_id = t2.user_id;

如图所示：不会对`b表`有去重操作，会一直遍历  
![在这里插入图片描述][949484dc16944811be8883e454b20c87.png]

# 执行计划 #

explain select
    *
    from wedw_dw.t_user as t1
    left semi join wedw_dw.t_order as t2
    on t1.user_id = t2.user_id;

+---------------------------------------------------------------------------------------------------+--+
    |                                              Explain                                              |
    +---------------------------------------------------------------------------------------------------+--+
    | STAGE DEPENDENCIES:                                                                               |
    |   Stage-4 is a root stage                                                                         |
    |   Stage-3 depends on stages: Stage-4                                                              |
    |   Stage-0 depends on stages: Stage-3                                                              |
    |                                                                                                   |
    | STAGE PLANS:                                                                                      |
    |   Stage: Stage-4                                                                                  |
    |     Map Reduce Local Work                                                                         |
    |       Alias -> Map Local Tables:                                                                  |
    |         t2                                                                                        |
    |           Fetch Operator                                                                          |
    |             limit: -1                                                                             |
    |       Alias -> Map Local Operator Tree:                                                           |
    |         t2                                                                                        |
    |           TableScan                                                                               |
    |             alias: t2                                                                             |
    |             Statistics: Num rows: 6 Data size: 30 Basic stats: COMPLETE Column stats: NONE        |
    |             Filter Operator                                                                       |
    |               predicate: user_id is not null (type: boolean)                                      |
    |               Statistics: Num rows: 3 Data size: 15 Basic stats: COMPLETE Column stats: NONE      |
    |               Select Operator                                                                     |
    |                 expressions: user_id (type: string)                                               |
    |                 outputColumnNames: user_id                                                        |
    |                 Statistics: Num rows: 3 Data size: 15 Basic stats: COMPLETE Column stats: NONE    |
    |                 Group By Operator                                                                 |
    |                   keys: user_id (type: string)                                                    |
    |                   mode: hash                                                                      |
    |                   outputColumnNames: _col0                                                        |
    |                   Statistics: Num rows: 3 Data size: 15 Basic stats: COMPLETE Column stats: NONE  |
    |                   HashTable Sink Operator                                                         |
    |                     keys:                                                                         |
    |                       0 user_id (type: string)                                                    |
    |                       1 _col0 (type: string)                                                      |
    |                                                                                                   |
    |   Stage: Stage-3                                                                                  |
    |     Map Reduce                                                                                    |
    |       Map Operator Tree:                                                                          |
    |           TableScan                                                                               |
    |             alias: t1                                                                             |
    |             Statistics: Num rows: 4 Data size: 16 Basic stats: COMPLETE Column stats: NONE        |
    |             Filter Operator                                                                       |
    |               predicate: user_id is not null (type: boolean)                                      |
    |               Statistics: Num rows: 2 Data size: 8 Basic stats: COMPLETE Column stats: NONE       |
    |               Map Join Operator                                                                   |
    |                 condition map:                                                                    |
    |                      Left Semi Join 0 to 1                                                        |
    |                 keys:                                                                             |
    |                   0 user_id (type: string)                                                        |
    |                   1 _col0 (type: string)                                                          |
    |                 outputColumnNames: _col0, _col1                                                   |
    |                 Statistics: Num rows: 3 Data size: 16 Basic stats: COMPLETE Column stats: NONE    |
    |                 File Output Operator                                                              |
    |                   compressed: false                                                               |
    |                   Statistics: Num rows: 3 Data size: 16 Basic stats: COMPLETE Column stats: NONE  |
    |                   table:                                                                          |
    |                       input format: org.apache.hadoop.mapred.TextInputFormat                      |
    |                       output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat   |
    |                       serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe                   |
    |       Local Work:                                                                                 |
    |         Map Reduce Local Work                                                                     |
    |                                                                                                   |
    |   Stage: Stage-0                                                                                  |
    |     Fetch Operator                                                                                |
    |       limit: -1                                                                                   |
    |       Processor Tree:                                                                             |
    |         ListSink                                                                                  |
    |                                                                                                   |
    +---------------------------------------------------------------------------------------------------+--+

# 小结 #

1、`LEFT SEMI JOIN` 是 `IN/EXISTS 子查询`的一种更高效的实现。

2、`LEFT SEMI JOIN` 的限制是， JOIN 子句中右边的表只能在 `ON` 子句中设置过滤条件，在 `WHERE` 子句、`SELECT` 子句或其他地方都不行。

3、因为 `left semi join` 是 `in(keySet)` 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 `left semi join` 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。

4、`left semi join` 是只传递表的 `join key` 给 map 阶段，因此`left semi join` 中最后 `select 的结果`只许出现左表。因为右表只有 join key 参与关联计算了，而left join on 默认是整个关系模型都参与计算了

[978b1e91b9c346c5ba46bb3f3b26b7c2.png]: /images/20220828/bafbf3b1480a4750a141513d2d24fa41.png
[b19f9aeaaab94d5bafee157afa260f22.png]: /images/20220828/d56ff6ba74a84a67a3394fc2b950a69f.png
[7d4bfed34df94a66b711c973e65f0843.png]: /images/20220828/cfea7a428ab940c8b371ecab22e3c58f.png
[514b905e63864a77af773fc24dcbfdf6.png]: /images/20220828/e49e2f5b4df7407f93d6bf5c14449207.png
[8cd83f7e50d942ae9d2ca5b9d926301e.png]: /images/20220828/b9cbc9273e854575b1cf70bdabbd74ff.png
[949484dc16944811be8883e454b20c87.png]: /images/20220828/8c1ff55600e74d44971f94dd738576ed.png

发表评论取消回复

表情：

评论列表（有 0 条评论，288人围观）

还没有评论，来说两句吧...

相关阅读

相关 Hive：left join VS left semi join VS in 的联系与区别

Hive中，左关联有left join和left semi join两种方式，两种方式存在很大的差别。简单总结来说： left join就是我们平时所用的left j

今天药忘吃喽~/ 2023年06月23日 14:22/ 0 赞/ 9 阅读

相关 left join right join inner join区别

sql的left join 、right join 、inner join之间的区别 left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录　

╰半夏微凉°/ 2022年12月01日 05:24/ 0 赞/ 214 阅读

相关 left semi join 和 left join 区别

left semi join 和 left join 区别对比执行计划小结对比 `a表`和`b表`通过`user_id`关联 `

た入场券/ 2022年09月11日 14:14/ 0 赞/ 289 阅读

相关 Hive的left join、left outer join和left semi join三者的区别

Hive的left join、left outer join和left semi join三者的区别 Hive的Join的文档说明地址： https://cwiki.a

爱被打了一巴掌/ 2022年08月07日 10:55/ 0 赞/ 215 阅读

相关 join 、left join 的区别

Join 等同于 inner join ：表示两个表都必须存在的 left join 表示以左边的表为基准，不管你右边的表有没有都显示 right join

比眉伴天荒/ 2022年07月25日 15:13/ 0 赞/ 354 阅读

相关 left join和inner join之间的区别

关于inner join 与 left join 之间的区别，以前以为自己搞懂了，今天从前端取参数的时候发现不是预想中的结果，才知道问题出在inner join

电玩女神/ 2022年05月18日 01:20/ 0 赞/ 242 阅读

相关 left join on and 和 left join on where的区别

left join on and 和 left join on where的区别一、left join on on条件是在生成临时表时使用的条件，它不管on中

太过爱你忘了你带给我的痛/ 2022年04月16日 00:28/ 0 赞/ 229 阅读

相关 Hive中HSQL中left semi join和INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN区别

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。 s

今天药忘吃喽~/ 2022年03月17日 15:00/ 0 赞/ 733 阅读

相关 LEFT SEMI JOIN 左半开连接

左半开连接（LEFT SEMI JOIN）会返回左边表的记录，前提是其记录对于右边表满足 ON 语句中的判定条件。对于常见的内连接（INNER JOIN）来说，这是一个特殊

今天药忘吃喽~/ 2022年01月29日 05:47/ 0 赞/ 230 阅读

相关 join、inner join、left join、right join、full join区别

join等价于inner join内连接抄，是返回两个表中都有的符合条件的行。 left join左连接，是返回左袭表知中所有的行及右表中符合条件的行。 r

喜欢ヅ旅行/ 2021年09月24日 11:04/ 0 赞/ 490 阅读