（转）Hive中JOIN的用法以及一些注意事项总结。

原文：https://www.dandelioncloud.cn/article/details/1529381803362369537

Hive表连接的语法支持如下：

1. join_table:
2. table_reference JOIN table_factor [join_condition]
3. | table_reference { LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
4. | table_reference LEFT SEMI JOIN table_reference join_condition
5. | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
7. table_reference:
8. table_factor
9. | join_table
11. table_factor:
12. tbl_name [alias]
13. | table_subquery alias
14. | ( table_references )
16. join_condition:
17. ON equality_expression ( AND equality_expression )*
19. equality_expression:

20. expression = expression

    hive只支持等连接，外连接，左半连接。hive不支持非相等的join条件（可以通过其他方式实现），因为它很难在map/reduce job实现这样的条件。而且，hive可以join两个以上的表。

例子

写join查询时，有几个典型的点要考虑，如下：

等链接

只有等链接是允许的：

1. SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

这个非等值链接是不合法的：

1. SELECT a.* FROM a JOIN b ON (a.id < b.id)

但是可以用如下方式实现：

1. SELECT a.* FROM a JOIN b ON (true) WHERE a.id < b.id

多表连接

同个查询，可以join两个以上的表：

1. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

需要注意的是，多个表join时，最好按照表由小到大的顺序join。虽然实际原因要考虑数据倾斜等问题，此处不展开叙述。

join的缓存和任务转换

hive转换多表join时，如果每个表在join字句中，使用的都是同一个列，只会转换为一个单独的map/reduce。

例如：

这个会转换为单独的map/reduce任务，只有b表的key1列在join被调用。

1. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

而这个链接：

1. SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

被转换为两个map/reduce任务，因为b的key1列在第一个join条件使用，而b表的key2列在第二个join条件使用。第一个map/reduce任务join a和b。第二个任务是第一个任务的结果join c

join的结果
LEFT，RIGHT，FULL OUTER连接存在是为了提供ON语句在没有匹配时的更多控制。例如，这个查询：
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
由于链接的字段是key，不存在一对多，所以将会返回a的每一行。如果b.key等于a.key,输出将是a.val,b.val,如果a没有和b.key匹配，输出的行将是 a.val,NULL。如果b的行没有和a.key匹配上，将被抛弃。语法”FROM a LEFT OUTER JOIN b”必须写在一行，为了理解它如何工作——这个查询，a是b的左边，a的所有行会被保持；RIGHT OUTER JOIN将保持b的所有行， FULL OUTER JOIN将会保存a和b的所有行。OUTER JOIN语义应该符合标准的SQL规范。

join的过滤

Joins发生在where字句前，所以，如果要限制join的输出，需要写在where字句，否则写在JOIN字句。现在讨论的一个混乱的大点，就是分区表

Sql代码 

1. SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key)
2. WHERE a.ds=’2000-01-01’ AND b.ds=’2000-01-01’

将会连接a和b，产生a.val和b.val的列表。WHERE字句，也可以引用join的输出列，然后过滤他们。

但是，无论何时JOIN的行找到a的key，但是找不到b的key时，b的所有列会置成NULL，包括ds列。这就是说，将过滤join输出的所有行，包括没有合法的b.key的行。然后你会在LEFT OUTER的要求扑空。

也就是说，如果你在WHERE字句引用b的任何列，LEFT OUTER的部分join结果是不相关的。所以，当外连接时，使用这个语句

Sql代码 

1. SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key AND b.ds=’2000-01-01’ AND a.ds=’2000-01-01’

join的输出会预先过滤，然后你不用对有a.key而没有b.key的行做过滤。RIGHT和FULL join也是一样的逻辑