HiveQL Select-Joins子句

JOIN是一个使用每个表中共同值组合两个表的特定字段的子句。它用于在数据库中组合两个或多个表中的记录。

阅读更多：Hive 教程

语法

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
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

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   | 
+----+----------+-----+--------+

LEFT OUTER JOIN

HiveQL LEFT OUTER JOIN返回左侧表中的所有行，即使右侧表中没有匹配项也是如此。 这意味着，如果ON子句在右侧表中匹配0（零）条记录，则JOIN仍然在结果中返回一个行，但每个来自右侧表的列都为NULL。

LEFT 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                | 
+----+----------+--------+---------------------+

RIGHT OUTER JOIN（右外连接）

HiveQL中的RIGHT OUTER JOIN（右外连接）返回所有来自右表的行，即使左表没有匹配项。 如果ON子句在左表中匹配了0（零）条记录，则JOIN仍将在结果中返回一行，但左表中每列都是NULL。

RIGHT JOIN（右连接）返回右表中的全部值，以及左表中的符合连接条件的匹配值，无匹配时返回NULL。

下面的查询演示了CUSTOMER和ORDER表之间的RIGHT OUTER JOIN（右外连接）：

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

+------+----------+--------+---------------------+ 
| 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 | 
+------+----------+--------+---------------------+

FULL OUTER JOIN（完全外连接）

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 | 
+------+----------+--------+---------------------+