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

考慮另一個表命令如下：

+-----+---------------------+-------------+--------+ 
|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和SQLOUTER JOIN 類似。連接條件是使用主鍵和表的外鍵。

下麵的查詢執行JOIN的CUSTOMER和ORDER表，並檢索記錄：

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返回左表中的所有的值，加上右表，或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。

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

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