hive学习笔记之JOIN

最新推荐文章于 2022-09-02 11:46:58 发布

原创最新推荐文章于 2022-09-02 11:46:58 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#hive

本文详细介绍了Hive SQL中各种Join操作的区别，包括常见的inner join、left outer join、right outer join、full outer join以及left semi join等，并通过具体示例说明了每种Join的特点及应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

hive没有left join和right join，只有left outer join 和right outer join尽管这两个在平常是一样的。

再说几个join的区别：

举例子：

hive> select * from zz0;
111111
222222
888888
hive> select * from zz1;
111111
333333
444444
888888

hive> select * from zz0 join zz1 on zz0.uid = zz1.uid;
111111 111111
888888 888888
hive> select * from zz0 left outer join zz1 on zz0.uid = zz1.uid;
111111 111111
222222 NULL
888888 888888
hive> select * from zz0 right outer join zz1 on zz0.uid = zz1.uid;
111111 111111
NULL    333333
NULL    444444
888888 888888
hive> select * from zz0 full outer join zz1 on zz0.uid = zz1.uid;
111111 111111
222222 NULL
NULL    333333
NULL    444444
888888 888888
hive> select * from zz0 left semi join zz1 on zz0.uid = zz1.uid;
111111 111111
888888 888888

再说下要注意的地方：

还是举例子：

使用以下sql查询

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'

假设我们想要的查询结果为：

a.val b.val

111 null

但是这样是查不出来的，因为这样写，如果有第三列b.ds,那么其值也为null。这样在where条件中就会过滤调这样的数据。那要如何得到我们想要的结果呢？

需要这样写：

SELECT a.val, b.val FROM a LEFT OUTER JOIN b
ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07')

大家可能对hive的LEFT SEMI JOIN不是很理解，那我就从我理解的角度去解释一下

Hive不支持where子句中的子查询，SQL常用的exist in子句需要改写。这一改写相对简单。考虑以下SQL查询语句：

SELECT a.key, a.value FROM a WHERE a.key in (SELECT b.key FROM B);

可以改写为

SELECT a.key, a.value FROM a LEFT OUTER JOIN b ON (a.key = b.key) WHERE b.key IS NOT NULL;

一个更高效的实现是利用left semi join改写为：

SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key);

left semi join是0.5.0以上版本的特性。

总有人问,left semi join和直接join 有什么区别.他们总是说返回的结果不是一样的么?

在某些情景下它们返回的结果是一样的.但是会有两点主要区别:

查询效率不一样.left semi join效率会优于join.原理在于left semi join 会将左边的表每一行数据与右边的表进行比较.如果关联上则直接返回左边表的数据,并且不再对右边的表进行扫描.而join则会继续扫描,如果再次关联上,那么左边表的这条数据将会在和右边表关联上的数据一起返回,也就是说,如果存在一对多的情况,那么将返回多条记录.left semi join 仅仅是用来对左边表进行过滤
明白了上边所说的原理那就知道了第二个区别在于left semi join 的右边就是一个过滤条件结果结果集,在你上层查询并不能像join那样查出join右边表的字段.