Hiveql-joins
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句は、複数のテーブルからレコードを結合および取得するために使用されます。 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を示しています。
クエリが正常に実行されると、次の応答が表示されます。
+------+----------+--------+---------------------+
| 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 |
+------+----------+--------+---------------------+