HiveQL SELECT JOIN
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 与 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。
RIGHT JOIN 返回右表中的所有值,加上左表中匹配的值,如果没有匹配的连接谓词,则返回 NULL。
以下查询演示了 CUSTOMER 和 ORDER 表之间的 RIGHT OUTER JOIN。
notranslate"> 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 |
+------+----------+--------+---------------------+