HiveQL-選択-結合

JOINは、それぞれに共通の値を使用して2つのテーブルの特定のフィールドを結合するために使用される句です。これは、データベース内の2つ以上のテーブルのレコードを結合するために使用されます。

構文

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]

例

この章では、次の2つの表を使用します。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のOUTERJOINと同じです。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 OUTERJOINを示しています。

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 OUTERJOINを示しています。

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テーブルの間の完全外部結合を示しています。

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