Hive中的in、exists和left semi join

在hive sql开发的过程中，对于当前数据在另一个数据集合中，是否存在的判断有三种方式，一种是in ，一种是exists，另一种可以是left semi join，但是由于hive不支持in|not in子查询，所以如果是单纯判断一个值是否在一个集合里面存在的时候，可以用in，但是判断一个集合在另一个集合存在的时候，还是推荐使用exists和left semi join。

一、数据准备

1，建表

CREATE TABLE test.in_test1
(
id      varchar(10)
,name   varchar(10)
,sex    varchar(10)
,age    varchar(10)
);

CREATE TABLE test.in_test2
(
id      varchar(10)
,name   varchar(10)
,class    varchar(10)
,school    varchar(10)
);

2，插入数据

INSERT INTO test.in_test1 VALUES
('1','xiaoming','1','17'),
('2','xiaohua','0','23'),
('3','jack','1','12'),
('4','rose','0','28'),
('5','jenny','0','45'),
('6','judy','0','10'),
('7','wangwu','1','35')
;
INSERT INTO test.in_test2 VALUES
('1','xiaoming','3','花花高中'),
('2','xiaohua','5','北京大学'),
('3','jack','2','新民中学'),
('4','rose','1','清华大学')
;

3，查询数据

SELECT * FROM test.in_test1;

SELECT * FROM test.in_test2;

二、join

1，查询语句

SELECT * 
FROM test.in_test1 test1 
JOIN test.in_test2 test2
ON test1.name = test2.name
；

2， 查询结果

三、exists

1，查询语句

SELECT * FROM test.in_test1 test1 
WHERE 1=1
AND EXISTS (SELECT * FROM test.in_test2 test2 WHERE test1.name = test2.name)
；

2， 查询结果

四、left semi join

left semi join 只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表；遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，在关联的时候会更加的高效。

1，查询语句

SELECT * 
FROM test.in_test1 test1 
left semi JOIN test.in_test2 test2
ON test1.name = test2.name；

2， 查询结果

总结

所以在判断是否存在的时候，由于Hive 不支持 where 子句中的子查询，所以最好的办法是采用exists和left semi join，这样在运行效率上面也会提高很多。