每当需要使用单个查询从多个表中检索数据时，您可以使用RDBMS中的JOINS.您可以在单个SQL查询中使用多个表.加入HSQLDB的行为是指将两个或多个表粉碎到一个表中.
考虑以下客户和订单表.
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 : 当其中一个表中存在匹配时返回行.
SELF JOIN : 用于将表连接到自身，就像表是两个表一样，在SQL语句中暂时重命名至少一个表.
Inner加入
最常用和最重要的连接是INNER JOIN.它也被称为EQUIJOIN.
INNER JOIN通过基于连接谓词组合两个表(table1和table2)的列值来创建新的结果表.该查询将table1的每一行与table2的每一行进行比较，以查找满足join-predicate的所有行对.当满足连接谓词时，每个匹配的行A和B对的列值将合并到结果行中.
语法
INNER JOIN的基本语法如下:
SELECT table1.column1, table2.column2...FROM table1INNER JOIN table2ON table1.common_field = table2.common_field;
示例
考虑以下两个表，一个标题为CUSTOMERS表，另一个标题为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 |+-----+---------------------+-------------+--------+
现在，让我们使用INNER JOIN查询连接这两个表，如下所示 :
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERSINNER JOIN ORDERSON 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.
语法
LEFT JOIN的基本语法如下 :
SELECT table1.column1, table2.column2...FROM table1LEFT JOIN table2ON table1.common_field = table2.common_field;
这里给定的条件可以是基于您的要求的任何给定表达式.
示例
考虑以下两个表，一个标题为CUSTOMERS表，另一个标题为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查询连接这两个表，如下所示 :
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERSLEFT JOIN ORDERSON 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 table1RIGHT JOIN table2ON table1.common_field = table2.common_field;
示例
考虑以下两个表，一个标题为CUSTOMERS表，另一个标题为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 |+-----+---------------------+-------------+--------+
现在，让我们使用RIGHT JOIN查询连接这两个表，如下所示 :
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERSRIGHT JOIN ORDERSON 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.
语法
FULL JOIN的基本语法如下 :
SELECT table1.column1, table2.column2...FROM table1FULL JOIN table2ON table1.common_field = table2.common_field;
这里给定的条件可以是基于您的要求的任何给定表达式.
示例
考虑以下两个表，一个标题为CUSTOMERS表，另一个标题为
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 |+-----+---------------------+-------------+--------+
现在，让我们使用FULL JOIN查询连接这两个表，如下所示 :
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERSFULL JOIN ORDERSON 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用于将表连接到自身，就好像table是两个表，暂时在SQL语句中重命名至少一个表.
语法
SELF JOIN的基本语法如下 :
SELECT a.column_name, b.column_name...FROM table1 a, table1 bWHERE a.common_field = b.common_field;
这里，WHERE子句可以是基于您的要求的任何给定表达式.
示例
考虑以下两个表，一个标题为CUSTOMERS表，另一个标题为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 |+----+----------+-----+-----------+----------+
现在，让我们使用SELF JOIN查询加入此表，如下所示 :
SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a, CUSTOMERS bWHERE 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 |+----+----------+---------+