HiveQL - 選擇連線

---

---

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
• 左外連線 (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) 返回左表中的所有值，加上右表中匹配的值，或者在沒有匹配的 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)。

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

全外連線 (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 | 
+------+----------+--------+---------------------+