HiveQL Select Joins

JOIN 子句用于通过使用每个表的常见值来组合两个表中的特定字段。

JOIN 子句用于组合来自数据库中两个或多个表的记录，类似于SQL JOIN。

语法

join_table:

   table_reference JOIN table_factor [join_condition]
   | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference
   join_condition
   | table_reference LEFT SEMI JOIN table_reference join_condition
   | table_reference CROSS JOIN table_reference [join_condition]

范例

我们将在本章中使用以下两个表格。考虑一下名为CUSTOMERS的表。

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
| 1  | Ramesh   | 32  | Ahmedabad | 2000.00  |  
| 2  | Khilan   | 25  | Delhi     | 1500.00  |  
| 3  | kaushik  | 23  | Kota      | 2000.00  |
| 4  | Chaitali | 25  | Mumbai    | 6500.00  |
| 5  | Hardik   | 27  | Bhopal    | 8500.00  |
| 6  | Komal    | 22  | MP        | 4500.00  |
| 7  | Muffy    | 24  | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

考虑另一个表ORDERS如下：

+-----+---------------------+-------------+--------+
|OID  | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 | 3000   |
| 100 | 2009-10-08 00:00:00 |           3 | 1500   |
| 101 | 2009-11-20 00:00:00 |           2 | 1560   |
| 103 | 2008-05-20 00:00:00 |           4 | 2060   |
+-----+---------------------+-------------+--------+

有如下几种不同类型的连接：

• 加入
• 左外连接
• 右外部连接
• 全外连接

JOIN 子句

JOIN 子句用于组合和检索多个表中的记录。JOIN与SQL中的OUTER JOIN相同。

JOIN条件是使用表的主键和外键引发的。

以下查询在CUSTOMER和ORDER表上执行JOIN，并检索记录：

hive> SELECT c.ID, c.NAME, c.AGE, o.AMOUNT
FROM CUSTOMERS c JOIN ORDERS o
ON (c.ID = o.CUSTOMER_ID);

成功执行查询后，您将看到以下响应：

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT |
+----+----------+-----+--------+
| 3  | kaushik  | 23  | 3000   |
| 3  | kaushik  | 23  | 1500   |
| 2  | Khilan   | 25  | 1560   |
| 4  | Chaitali | 25  | 2060   |
+----+----------+-----+--------+

左外连接

HiveQL LEFT OUTER JOIN返回左表中的所有行，即使右表中没有匹配。这意味着，如果ON子句与右表中的0（零）记录相匹配，JOIN仍会在结果中返回一行，但右表中的每列都有NULL。

LEFT JOIN返回左表中的所有值，加上右表中的匹配值，或者在没有匹配JOIN谓词的情况下返回NULL。

以下查询演示了CUSTOMER和ORDER表之间的LEFT OUTER JOIN：

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
FROM CUSTOMERS c
LEFT OUTER JOIN ORDERS o
ON (c.ID = o.CUSTOMER_ID);

成功执行查询后，您将看到以下响应：

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
| 1  | Ramesh   | NULL   | NULL                |
| 2  | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 3  | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3  | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 4  | Chaitali | 2060   | 2008-05-20 00:00:00 |
| 5  | Hardik   | NULL   | NULL                |
| 6  | Komal    | NULL   | NULL                |
| 7  | Muffy    | NULL   | NULL                |
+----+----------+--------+---------------------+

右外连接

HiveQL RIGHT OUTER JOIN返回右表中的所有行，即使左表中没有匹配。如果ON子句与左表中的0（零）记录相匹配，则JOIN仍会在结果中返回一行，但左表中的每列都有NULL。

右连接返回右表中的所有值，加上左表中的匹配值，或者在没有匹配连接谓词的情况下返回NULL。

以下查询演示了CUSTOMER和ORDER表之间的RIGHT OUTER JOIN。

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE FROM CUSTOMERS c RIGHT OUTER JOIN ORDERS o ON (c.ID = o.CUSTOMER_ID);

成功执行查询后，您将看到以下响应：

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

全外连接

HiveQL FULL OUTER JOIN将满足JOIN条件的左外部表和右外部表的记录组合在一起。连接的表包含来自两个表的所有记录，或者在任何一边填充缺失匹配的NULL值。

以下查询演示了CUSTOMER和ORDER表之间的FULL OUTER JOIN：

hive> SELECT c.ID, c.NAME, o.AMOUNT, o.DATE
FROM CUSTOMERS c
FULL OUTER JOIN ORDERS o
ON (c.ID = o.CUSTOMER_ID);

成功执行查询后，您将看到以下响应：

+------+----------+--------+---------------------+
| ID   | NAME     | AMOUNT | DATE                |
+------+----------+--------+---------------------+
| 1    | Ramesh   | NULL   | NULL                |
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |
| 5    | Hardik   | NULL   | NULL                |
| 6    | Komal    | NULL   | NULL                |
| 7    | Muffy    | NULL   | NULL                |  
| 3    | kaushik  | 3000   | 2009-10-08 00:00:00 |
| 3    | kaushik  | 1500   | 2009-10-08 00:00:00 |
| 2    | Khilan   | 1560   | 2009-11-20 00:00:00 |
| 4    | Chaitali | 2060   | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+