LEFT SEMI JOIN(左半连接)介绍
SEMI JOIN
(即等价于LEFT SEMI JOIN
)最主要的使用场景就是解决EXISTS IN
。LEFT SEMI JOIN
(左半连接)是IN/EXISTS
子查询的一种更高效的实现。LEFT SEMI JOIN
虽然含有LEFT
,但其实现效果等价于INNER JOIN
,但是JOIN
结果只取原左表中的列。
优化实例
实例表准备:
CREATE TABLE test.user1(`id` bigint)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS textfileLOCATION '/big-data/test/user1';CREATE TABLE test.user2(`id` bigint,`role` string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS textfileLOCATION '/big-data/test/user2';
实例数据准备:
insert into test.user1 values(-1);insert into test.user1 values(1);insert into test.user2 values(-1,'C1');insert into test.user2 values(1,'C1');insert into test.user2 values(1,'C2');
数据测试:
JOIN
--join的select的结果中可以有t1(左表),t2(右表)两张表的字段SELECT t1.id,t2.roleFROM test.user1 t1JOIN test.user2 t2ON t1.id=t2.id;
结果:
left semi join
--left semi join的select的结果中只允许出现t1(左表)表的字段SELECT t1.idFROM test.user1 t1LEFT SEMI JOIN test.user2 t2ON t1.id=t2.id;--等价于SELECT t1.idFROM test.user1 t1WHERE id IN (SELECTid FROM test.user2);--等价于SELECT t1.idFROM test.user1 t1WHERE EXISTS (SELECT 1 FROM test.user2 t2WHERE t1.id=t2.id);
结果:
异常:
这样如果结果涉及查询右表中的字段就会报错写会报错:
SELECT t1.id,t2.roleFROM test.user1 t1LEFT SEMI JOIN test.user2 t2ON t1.id=t2.id;
结果:
join on与left semi join的联系
他们都是
hive join
方式的一种,join on
属于common join(shuffle join/reduce join)
,而left semi join
则属于map join(broadcast join)
的一种变体,从名字可以看出他们的实现原理有差异。
join on与left semi join的区别
(1)
Left Semi Join
,也叫半连接
,是从分布式数据库中借鉴过来的方法。它的产生动机是:对于reduce side join,跨机器的数据传输量非常大,这成了join操作的一个瓶颈,如果能够在map端过滤掉不会参加join操作的数据,则可以大大节省网络IO,提升执行效率
。实现方法:选取一个小表,假设是
File1
,将其参与join
的key
抽取出来,保存到文件File3
中,File3
文件一般很小,可以放到内存中。在map
阶段,使用DistributedCache
将File3
复制到各个TaskTracker
上,然后将File2
中不在File3
中的key
对应的记录过滤掉,剩下的reduce
阶段的工作与reduce side join
相同。
left semi join
是只传递表的join key
给map
阶段 , 如果key
足够小还是执行map join
, 如果不是则还是common join
。关于common join(shuffle join/reduce join)
的原理请参考文末refer
。(2)
left semi join
子句中右边的表只能在ON
子句中设置过滤条件,在WHERE
子句、SELECT
子句或其他地方
过滤都不行。(3)对待右表中重复
key
的处理方式差异:因为 left semi join 是 in(keySet) 的关系,遇到右表重复记录,左表会跳过,而 join on 则会一直遍历
。left semi join
中最后的结果是这会造成性能,以及join
结果上的差异。(4)
left semi join中最后 select 的结果只许出现左表,因为右表只有join key参与关联计算了,而 join on 默认是整个关系模型都参与计算了
。
这里借鉴网上一个实例图在解释下:
比如以下A
表和B
表进行join
或left semi join
,然后select
出所有字段,结果区别如下:(蓝色叉的那一列实际是不存在left semi join
中的,因为最后select
的结果只许出现左表。)
写在最后
对于大多数情况下JOIN ON
和left semi on
是对等的,在右表存在重复数据记录时,导致结果差异,所以大家在使用的时候最好能了解这两种方式的原理,是否和我们想要的数据效果一致。