Hsqldb 简明教程

HSQLDB - Joins

每当需要使用单个查询从多个表中检索数据时，您可以使用RDBMS中的JOINS。可以在单个SQL查询中使用多个表。在HSQLDB中连接操作是指将两个或更多个表合并成一个表。

Whenever there is a requirement to retrieve data from multiple tables using a single query, you can use JOINS from RDBMS. You can use multiple tables in your single SQL query. The act of joining in HSQLDB refers to smashing two or more tables into a single table.

考虑以下Customers和Orders表。

Consider the following Customers and Orders tables.

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

现在，让我们尝试检索客户数据和相应客户下的订单金额。这意味着我们正在从customers表和orders表中检索记录数据。我们可以通过使用HSQLDB中的JOINS概念来实现此目的。以下是相同的JOIN查询。

Now, let us try to retrieve the data of the customers and the order amount that the respective customer placed. This means we are retrieving the record data from both customers and orders table. We can achieve this by using the JOINS concept in HSQLDB. Following is the JOIN query for the same.

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

在执行上述查询后，你将收到以下输出。

After execution of the above query, you will receive the following output.

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

JOIN Types

HSQLDB中提供不同类型的连接。

There are different types of joins available in HSQLDB.

INNER JOIN − Returns the rows when there is a match in both tables.
LEFT JOIN − Returns all rows from the left table, even if there are no matches in the right table.
RIGHT JOIN − Returns all rows from the right table, even if there are no matches in the left table.
FULL JOIN − Returns the rows when there is a match in one of the tables.
SELF JOIN − Used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Inner Join

最常用且最重要的连接是 INNER JOIN。它也被称为 EQUIJOIN（等值连接）。

The most frequently used and important of the joins is the INNER JOIN. It is also referred to as an EQUIJOIN.

INNER JOIN 基于连接谓词通过组合两张表（table1 和 table2）中的列值创建新的结果表。查询会比较 table1 中的每行与 table2 中的每行，以找到满足连接谓词的所有行对。当满足连接谓词时，将每个匹配行对 A 和 B 的列值组合成一行结果。

The INNER JOIN creates a new result table by combining the column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-predicate. When the join-predicate is satisfied, the column values for each matched pair of rows A and B are combined into a result row.

Syntax

INNER JOIN 的基本语法如下。

The basic syntax of INNER JOIN is as follows.

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

Example

考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：

Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −

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

现在，让我们使用 INNER JOIN 查询连接这两张表，如下所示：

Now, let us join these two tables using INNER JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

在执行上述查询后，你将收到以下输出。

After execution of the above query, you will receive the following output.

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

Left Join

HSQLDB LEFT JOIN 会返回左表中的所有行，即使右表中没有匹配项。这意味着如果 ON 子句与右表中的记录匹配 0（零）条，连接仍会在结果中返回一行，但右表中的每列都是 NULL。

The HSQLDB LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in the right table, the join will still return a row in the result, but with NULL in each column from the right table.

这意味着左连接会返回左表中的所有值，加上右表中的匹配值或在没有匹配连接谓词的情况下返回 NULL。

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

Syntax

LEFT JOIN 的基本语法如下：

The basic syntax of LEFT JOIN is as follows −

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

这里给定的条件可以是根据您的要求给出的任何表达式。

Here the given condition could be any given expression based on your requirement.

Example

考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：

Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −

+----+----------+-----+-----------+----------+
| 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 查询连接这两张表，如下所示：

Now, let us join these two tables using the LEFT JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

执行以上查询后，您将收到以下输出 −

After execution of the above query, you will receive the following output −

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

HSQLDB RIGHT JOIN 会返回右表中的所有行，即使左表中没有匹配项。这意味着如果 ON 子句与左表中的记录匹配 0（零）条，连接仍会在结果中返回一行，但左表中的每列都是 NULL。

The HSQLDB RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table, the join will still return a row in the result, but with NULL in each column from the left table.

这意味着右连接会返回右表中的所有值，加上左表中的匹配值或在没有匹配连接谓词的情况下返回 NULL。

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

Syntax

RIGHT JOIN 的基本语法如下：

The basic syntax of RIGHT JOIN is as follows −

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

Example

考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：

Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −

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

现在，让我们使用 RIGHT JOIN 查询连接这两张表，如下所示：

Now, let us join these two tables using the RIGHT JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

执行以上查询后，您将收到以下结果。

After execution of the above query, you will receive the following result.

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

HSQLDB FULL JOIN 将左外连接和右外连接的结果组合在一起。

The HSQLDB FULL JOIN combines the results of both left and right outer joins.

连接的表将包含两张表中的所有记录，并在两边填入缺少匹配项的 NULL。

The joined table will contain all records from both tables, and fill in NULLs for the missing matches on either side.

Syntax

FULL JOIN 的基本语法如下：

The basic syntax of FULL JOIN is as follows −

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

这里给定的条件可以是根据您的要求给出的任何表达式。

Here the given condition could be any given expression based on your requirement.

Example

考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：

Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −

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

现在，让我们使用 FULL JOIN 查询连接这两张表，如下所示：

Now, let us join these two tables using the FULL JOIN query as follows −

SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

执行以上查询后，您将收到以下结果。

After execution of the above query, you will receive the following result.

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

Self Join

SQL 自联接用于将一个表连接到它自身，就像该表是两个表一样，在 SQL 语句中临时重命名至少一个表。

The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Syntax

SELF JOIN 的基本语法如下 −

The basic syntax of SELF JOIN is as follows −

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

这里，WHERE 子句可以是基于您要求的任何给定表达式。

Here, the WHERE clause could be any given expression based on your requirement.

Example

考虑以下两张表，一张称为 CUSTOMERS 表，另一张称为 ORDERS 表，如下所示：

Consider the following two tables, one titled as CUSTOMERS table and another titled as ORDERS table as follows −

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

现在，我们使用 SELF JOIN 查询如下连接此表 −

Now, let us join this table using the SELF JOIN query as follows −

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

执行以上查询后，您将收到以下输出 −

After execution of the above query, you will receive the following output −

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