转载：left semi join和left join区别

转载于：https://my.oschina.net/u/4631230/blog/4533371

 

(a表和b表通过user_id关联)

 

1.1 a表数据

select * from wedw_dw.t_user;

 

 

1.2 b表数据

select * from wedw_dw.t_order;

 

 

1.3 left join

select*from wedw_dw.t_user t1left join wedw_dw.t_order t2on t1.user_id = t2.user_id;

 

如图所示：a表和b表所有的字段都会展示出来

 

1.4 left semi join

select*from wedw_dw.t_user t1left semi join wedw_dw.t_order t2on t1.user_id = t2.user_id;

 

如图所示：只能展示a表的字段，因为left semi join 只传递表的 join key 给 map 阶段

 

1.5 in

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

 

如图所示：发现效果和left semi join是一样的

 

1.6 inner join

selectt1.*from wedw_dw.t_user t1inner join wedw_dw.t_order t2on t1.user_id = t2.user_id;

 

如图所示：不会对b表有去重操作，会一直遍历

 

1.7 执行计划

explain select*from wedw_dw.t_user t1left semi join wedw_dw.t_order t2on t1.user_id = t2.user_id;

二 总结

 1. 联系

他们都是 hive join 方式的一种，join on 属于 common join（shuffle join/reduce join），而 left semi join 则属于 map join（broadcast join）的一种变体，从名字可以看出他们的实现原理有差异。

 

2. 区别

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

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

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

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