hive学习笔记之JOIN

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 bON (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 有什么区别.他们总是说返回的结果不是一样的么?

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

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