HSQLDB-結合

単一のクエリを使用して複数のテーブルからデータを取得する必要がある場合はいつでも、RDBMSからJOINSを使用できます。1つのSQLクエリで複数のテーブルを使用できます。HSQLDBに参加するという行為は、2つ以上のテーブルを1つのテーブルに粉砕することを意味します。

次のCustomersテーブルとOrdersテーブルについて考えてみます。

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

それでは、顧客のデータと、それぞれの顧客が出した注文金額を取得してみましょう。これは、顧客と注文テーブルの両方からレコードデータを取得していることを意味します。これは、HSQLDBのJOINSコンセプトを使用して実現できます。以下は、同じものに対するJOINクエリです。

SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS, ORDERS WHERE CUSTOMERS.ID =
ORDERS.CUSTOMER_ID;

上記のクエリを実行すると、次の出力が表示されます。

+----+----------+-----+--------+
| ID |   NAME   | AGE | AMOUNT |
+----+----------+-----+--------+
|  3 | kaushik  |  23 |  3000  |
|  3 | kaushik  |  23 |  1500  |
|  2 | Khilan   |  25 |  1560  |
|  4 | Chaitali |  25 |  2060  |
+----+----------+-----+--------+

JOINタイプ

HSQLDBで使用できる結合にはさまざまなタイプがあります。

  • INNER JOIN −両方のテーブルに一致する場合に行を返します。

  • LEFT JOIN −右側のテーブルに一致するものがない場合でも、左側のテーブルからすべての行を返します。

  • RIGHT JOIN −左側のテーブルに一致するものがない場合でも、右側のテーブルからすべての行を返します。

  • FULL JOIN −テーブルの1つに一致する場合、行を返します。

  • SELF JOIN −テーブルが2つのテーブルであるかのようにテーブルをそれ自体に結合するために使用され、SQLステートメントの少なくとも1つのテーブルの名前を一時的に変更します。

内部結合

結合の中で最も頻繁に使用され、重要なのは、内部結合です。EQUIJOINとも呼ばれます。

INNER JOINは、結合述語に基づいて2つのテーブル(table1とtable2)の列値を組み合わせることにより、新しい結果テーブルを作成します。クエリは、table1の各行をtable2の各行と比較して、結合述語を満たすすべての行のペアを見つけます。結合述語が満たされると、一致した行AとBの各ペアの列値が結合されて結果行になります。

構文

INNERJOINの基本的な構文は次のとおりです。

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

次の2つのテーブルについて考えてみます。1つはCUSTOMERSテーブル、もう1つはORDERSテーブルというタイトルです。

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

ここで、次のようにINNERJOINクエリを使用してこれら2つのテーブルを結合しましょう。

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.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 |
+----+----------+--------+---------------------+

左結合

HSQLDB LEFT JOINは、右側のテーブルに一致するものがない場合でも、左側のテーブルからすべての行を返します。これは、ON句が右側のテーブルの0(ゼロ)レコードと一致する場合でも、結合は結果に行を返しますが、右側のテーブルの各列にはNULLが含まれることを意味します。

これは、左結合が左テーブルのすべての値に加えて、右テーブルの一致した値を返すか、一致する結合述語がない場合はNULLを返すことを意味します。

構文

LEFTJOINの基本的な構文は次のとおりです。

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

ここで、指定された条件は、要件に基づいた任意の式にすることができます。

次の2つのテーブルについて考えてみます。1つはCUSTOMERSテーブル、もう1つはORDERSテーブルというタイトルです。

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

ここで、LEFT JOINクエリを使用してこれら2つのテーブルを次のように結合しましょう-

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.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         |
+----+----------+--------+---------------------+

右結合

HSQLDB RIGHT JOINは、左側のテーブルに一致するものがない場合でも、右側のテーブルからすべての行を返します。これは、ON句が左側のテーブルの0(ゼロ)レコードと一致する場合でも、結合は結果に行を返しますが、左側のテーブルの各列にはNULLが含まれることを意味します。

これは、右結合が右テーブルのすべての値に加えて、左テーブルの一致した値を返すか、一致する結合述語がない場合はNULLを返すことを意味します。

構文

の基本構文 RIGHT JOIN 次のとおりです-

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

次の2つのテーブルについて考えてみます。1つはCUSTOMERSテーブル、もう1つはORDERSテーブルというタイトルです。

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

ここで、次のようにRIGHTJOINクエリを使用してこれら2つのテーブルを結合しましょう。

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.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 |
+------+----------+--------+---------------------+

完全結合

HSQLDB FULL JOINは、左右両方の外部結合の結果を結合します。

結合されたテーブルには、両方のテーブルのすべてのレコードが含まれ、いずれかの側で欠落している一致についてNULLを入力します。

構文

FULLJOINの基本的な構文は次のとおりです。

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

ここで、指定された条件は、要件に基づいた任意の式にすることができます。

次の2つのテーブルについて考えてみます。1つはCUSTOMERSテーブル、もう1つはORDERSテーブルというタイトルです。

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

ここで、次のようにFULLJOINクエリを使用してこれら2つのテーブルを結合しましょう。

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.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 |
+------+----------+--------+---------------------+

自己結合

SQL SELF JOINは、テーブルが2つのテーブルであるかのようにテーブルをそれ自体に結合するために使用され、SQLステートメント内の少なくとも1つのテーブルの名前を一時的に変更します。

構文

SELFJOINの基本的な構文は次のとおりです。

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

ここで、WHERE句は、要件に基づいて任意の式にすることができます。

次の2つのテーブルについて考えてみます。1つはCUSTOMERSテーブル、もう1つはORDERSテーブルというタイトルです。

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

ここで、SELFJOINクエリを使用して次のようにこのテーブルを結合しましょう-

SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY > b.SALARY;

上記のクエリの実行後、次の出力が表示されます-

+----+----------+---------+
| ID |   NAME   | SALARY  |
+----+----------+---------+
| 2  |  Ramesh  | 1500.00 |
| 2  |  kaushik | 1500.00 |
| 1  | Chaitali | 2000.00 |
| 2  | Chaitali | 1500.00 |
| 3  | Chaitali | 2000.00 |
| 6  | Chaitali | 4500.00 |
| 1  |  Hardik  | 2000.00 |
| 2  |  Hardik  | 1500.00 |
| 3  |  Hardik  | 2000.00 |
| 4  |  Hardik  | 6500.00 |
| 6  |  Hardik  | 4500.00 |
| 1  |  Komal   | 2000.00 |
| 2  |  Komal   | 1500.00 |
| 3  |  Komal   | 2000.00 |
| 1  |  Muffy   | 2000.00 |
| 2  |  Muffy   | 1500.00 |
| 3  |  Muffy   | 2000.00 |
| 4  |  Muffy   | 6500.00 |
| 5  |  Muffy   | 8500.00 |
| 6  |  Muffy   | 4500.00 |
+----+----------+---------+