如何使用SQL系列 之 如何在SQL中使用JOIN连接

引言

许多数据库根据某些数据点之间的关系，将信息分成不同的表。即使在这种情况下，也可能需要同时从多个表中检索信息。

在一个结构化查询语言(SQL)操作中从多个表中访问数据的一种常见方法是使用JOIN子句将这些表组合起来。基于关系代数中的连接操作，JOIN子句通过匹配每个表中相互关联的行来合并单独的表。通常，这种关系是基于一对共享相同值的列(每个表一个)，例如一个表的外键和另一个表的主键。

本指南概述了如何构造各种包含JOIN子句的SQL查询。它还强调了不同类型的JOIN子句，它们如何组合来自多个表的数据，以及如何使用别名使编写JOIN操作不那么繁琐。

前期准备

为了学习本指南，你需要一台运行某种使用SQL的关系数据库管理系统(RDBMS)的计算机。

注意：请注意，许多RDBMS使用它们自己独特的SQL实现。虽然本教程中概述的命令适用于大多数RDBMS，但如果你在MySQL以外的系统上测试它们，确切的语法或输出可能会有所不同。

你还需要一个装载了一些示例数据的数据库和表，可以在其中练习使用相关命令。

连接到MySQL并设置一个示例数据库

如果SQL数据库系统运行在远程服务器上，请从本地设备SSH到服务器：

ssh sammy@your_server_ip

然后打开MySQL服务器提示符，将==sammy==替换为你的MySQL用户账户的名称：

mysql -u sammy -p

创建一个名为joinsDB的数据库：

CREATE DATABASE joinsDB;

如果数据库成功创建,您将收到这样的输出：

OutputQuery OK, 1 row affected (0.01 sec)

要选择joinsDB数据库，运行以下USE语句：

USE joinsDB;

OutputDatabase changed

选择joinsDB后，在其中创建一些表。对于本指南中使用的示例，假设您经营一家工厂，并决定开始在SQL数据库中跟踪有关您的产品线、销售团队的员工和公司销售的信息。您计划从三个表开始，第一个表将存储有关产品的信息。你决定第一个表需要3列：

• productID：每个产品的识别号码,表示“int”数据类型。这一列将作为表的主键，这意味着每个值将作为其各自行的唯一标识符。因为主键中的每个值都必须是唯一的，所以这一列也有 UNIQUE约束
• productName：每个产品的名称，使用varchar数据类型表示，不超过20个字符
• price：每个产品的价格，使用decimal数据类型表示。此语句指定此列中的任何值的长度限制为最多四位，其中两位在小数点的右侧。因此，这一列允许的取值范围从-99.99到99.99

创建一个名为products的表，它包含以下三列：

CREATE TABLE products (
productID int UNIQUE,
productName varchar(20),
price decimal (4,2),
PRIMARY KEY (productID)
);

第二个表将存储信息的员工在你的公司的销售团队。你决定这个表还需要3列：

• empID： 类似于productID一列,这一列将举行一个独特的识别号码为每一个员工在销售团队表示int数据类型。同样，这一列也有UNIQUE约束，并将作为team表的主键
• empName：每个销售人员的名字，使用varchar数据类型表示，不超过20个字符
• productSpecialty：您的销售团队的每个成员都被分配了一种产品作为他们的专长;他们可以销售你公司生产的任何产品，但他们的整体重点将放在他们擅长的任何产品上。为了在表中表明这一点，我们创建了这一列，该列包含每个员工专长于的产品的productID值

为了确保productSpecialty列只保存代表有效产品ID号的值，你决定对引用products表的productID列的列应用一个外键约束。外键约束是一种表达两个表之间关系的方式，它要求应用的列中的值必须已经存在于它引用的列中。在下面的CREATE TABLE语句中，FOREIGN KEY约束要求任何添加到team表中的productSpecialty列的值必须已经存在于products表的productID列中。

创建一个名为team的表，包含以下三列：

CREATE TABLE team (
empID int UNIQUE,
empName varchar(20),
productSpecialty int,
PRIMARY KEY (empID),
FOREIGN KEY (productSpecialty) REFERENCES products (productID)
);

创建的最后一个表将保存公司的销售记录。这个表有4列：

• saleID：与productID和empID列类似，这一列将为每一笔以int数据类型表示的销售额保存一个唯一的标识号。这一列也有UNIQUE约束，因此它可以作为sales表的主键
• quantity：售出的每种产品的数量，用int数据类型表示
• productID：销售产品的识别号，用int表示
• salesperson：进行销售的员工的身份证号

就像team表中的productSpecialty列一样，你决定对productID和salesperson列应用外键约束。这将确保这些列只包含已经分别存在于products表的productID列和team表的empID列中的值。

创建一个名为sales的表，包含以下四列：

CREATE TABLE sales (
saleID int UNIQUE,
quantity int,
productID int,
salesperson int,
PRIMARY KEY (saleID),
FOREIGN KEY (productID) REFERENCES products (productID),
FOREIGN KEY (salesperson) REFERENCES team (empID)
);

接下来，通过运行以下INSERT INTO操作来加载包含一些示例数据的products表：

INSERT INTO products
VALUES
(1, 'widget', 18.99),
(2, 'gizmo', 14.49),
(3, 'thingamajig', 39.99),
(4, 'doodad', 11.50),
(5, 'whatzit', 29.99);

然后加载包含一些示例数据的team表：

INSERT INTO team
VALUES
(1, 'Florence', 1),
(2, 'Mary', 4),
(3, 'Diana', 3),
(4, 'Betty', 2);

加载包含一些示例数据的sales表：

INSERT INTO sales
VALUES
(1, 7, 1, 1),
(2, 10, 5, 4),
(3, 8, 2, 4),
(4, 1, 3, 3),
(5, 5, 1, 3);

最后，假设你的公司在没有任何销售团队成员参与的情况下完成了一些销售。为了记录这些销售额，运行以下操作，在sales表中添加三行不包含salesperson列值的行：

INSERT INTO sales (saleID, quantity, productID)
VALUES
(6, 1, 5),
(7, 3, 1),
(8, 4, 5);

有了这些，你就可以开始学习如何在SQL中联结表了。

理解JOIN操作的语法

JOIN子句可以在各种SQL语句中使用，包括UPDATE和DELETE操作。出于说明的目的，本指南中的示例使用SELECT查询来演示JOIN子句的工作原理。

下面的例子展示了包含JOIN子句的SELECT语句的通用语法：

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
ON search_condition;

这种语法以SELECT语句开始，它将从两个不同的表中返回两列。注意，因为JOIN子句会比较多个表的内容，这个语法示例通过在列的名称前面加上表的名称和句点来指定要从哪个表中选择每一列。这被称为完全限定列引用。

你可以在任何操作中使用完全限定的列引用，但从技术上讲，只有来自不同表的两个列共享相同名称时才有必要这样做。不过，在处理多张表时，使用它们是一个好习惯，因为它们可以使JOIN操作更容易阅读和理解。

在SELECT子句之后是FROM子句。在任何查询中，FROM子句都是你定义应该被搜索以返回所需数据的数据集的地方。这里唯一的区别是，FROM子句包含两个由JOIN关键字分隔的表。编写查询的一个有用的方法是记住你要从你想要查询的表中SELECT哪些列要返回。

接下来是一个ON子句，它描述了查询如何通过定义一个搜索条件来连接两个表。一个搜索条件是一个或多个谓词的集合，或者可以评估某个条件是“true”、“false”还是“unknown”的表达式。可以将JOIN操作理解为组合两个表中的每一行，然后返回ON子句中搜索条件求值为“true”的任何行。

在ON子句中，通常包含一个搜索条件，用于测试两个相关的列——比如外键引用的一个表的外键和另一个表的主键——的值是否相等。这有时被称为Equi join。

作为Equi join如何匹配来自多个表的数据的示例，使用前面添加的样例数据运行以下查询。这条语句将使用一个搜索条件连接products和team表，该搜索条件测试是否匹配各自的productID和productSpecialty列中的值。然后，它将返回销售团队每个成员的名字、他们擅长的每种产品的名称以及这些产品的价格：

SELECT team.empName, products.productName, products.price
FROM products JOIN team
ON products.productID = team.productSpecialty;

下面是这个查询的结果集：

Output+----------+-------------+-------+
| empName  | productName | price |
+----------+-------------+-------+
| Florence | widget      | 18.99 |
| Mary     | doodad      | 11.50 |
| Diana    | thingamajig | 39.99 |
| Betty    | gizmo       | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

为了说明SQL是如何组合这些表形成结果集的，让我们仔细看看这个过程。需要明确的是，以下并不是数据库管理系统将两个表连接在一起时所发生的事情，但将JOIN操作想象为如下过程可能会有所帮助。

首先，查询打印出FROM子句中第一个表products中的每一行和每一列:

JOIN Process Example+-----------+-------------+-------+
| productID | productName | price |
+-----------+-------------+-------+
|         1 | widget      | 18.99 |
|         2 | gizmo       | 14.49 |
|         3 | thingamajig | 39.99 |
|         4 | doodad      | 11.50 |
|         5 | whatzit     | 29.99 |
+-----------+-------------+-------+

然后，它查看每一行，并匹配team表中productSpecialty等于productID值的任何行：

JOIN Process Example+-----------+-------------+-------+-------+----------+------------------+
| productID | productName | price | empID | empName  | productSpecialty |
+-----------+-------------+-------+-------+----------+------------------+
|         1 | widget      | 18.99 |     1 | Florence |                1 |
|         2 | gizmo       | 14.49 |     4 | Betty    |                2 |
|         3 | thingamajig | 39.99 |     3 | Diana    |                3 |
|         4 | doodad      | 11.50 |     2 | Mary     |                4 |
|         5 | whatzit     | 29.99 |       |          |                  |
+-----------+-------------+-------+-------+----------+------------------+

然后，它删除所有不匹配的行，并根据它们在SELECT子句中的顺序重新排列列，删除所有未指定的列，对行进行排序，并返回最终结果集：

JOIN Process Example+----------+-------------+-------+
| empName  | productName | price |
+----------+-------------+-------+
| Florence | widget      | 18.99 |
| Mary     | doodad      | 11.50 |
| Diana    | thingamajig | 39.99 |
| Betty    | gizmo       | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

使用equi join是联结表最常见的方式，但也可以使用其他SQL操作符，如<、>、LIKE、NOT LIKE，甚至BETWEEN in ON子句搜索条件。但是要注意，使用更复杂的搜索条件可能会使预测结果集中会出现什么数据变得困难。

在大多数实现中，你可以使用任意一组SQL标准中称为“符合JOIN条件”数据类型的列来联结表。这意味着，通常情况下，可以将一个存储数值型数据的列与其他存储数值型数据的列进行联结，而不管它们各自的数据类型如何。同样，通常可以将保存字符值的任何列与保存字符数据的任何其他列进行联结。不过，如前所述，用于联结两张表的列通常是已经表示了两张表之间关系的列，比如外键和它所引用的另一张表的主键。

许多SQL实现还允许你使用USING关键字来联结具有相同名称的列，而不是ON。下面是这种操作的语法:

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
USING (related_column);

在这个语法示例中，USING子句等价于ON ==table1==.==related_column== = ==table2==.==related_column==;。

因为sales和products都有一个名为productID的列，你可以通过USING关键字来匹配它们。下面的命令执行此操作，并返回每次销售的saleID、售出的数量、售出的每个产品的名称和价格。此外，它按升序排序结果集基于saleID的价值:

SELECT sales.saleID, sales.quantity, products.productName, products.price
FROM sales JOIN products
USING (productID)
ORDER BY saleID;

Output+--------+----------+-------------+-------+
| saleID | quantity | productName | price |
+--------+----------+-------------+-------+
|      1 |        7 | widget     | 18.99 |
|      2 |       10 | whatzit     | 29.99 |
|      3 |        8 | gizmo       | 14.49 |
|      4 |        1 | thingamajig | 39.99 |
|      5 |        5 | widget      | 18.99 |
|      6 |        1 | whatzit     | 29.99 |
|      7 |        3 | widget      | 18.99 |
|      8 |        4 | whatzit     | 29.99 |
+--------+----------+-------------+-------+
8 rows in set (0.00 sec)

在连接表时，数据库系统有时会以一种不容易预测的方式重新排列行。像这样包含一个ORDER BY子句有助于使结果集更加连贯和可读。

连接两个以上的表

There may be times when you need to combine data from more than just two tables. You can join any number of tables together by embedding JOIN clauses within other JOIN clauses. The following syntax is an example of how this can look when joining three tables：

SELECT table1.column1, table2.column2, table3.column3
FROM table1 JOIN table2
ON table1.related_column = table2.related_column
JOIN table3
ON table3.related_column = table1_or_2.related_column;

这个语法示例的FROM子句以连接table1和table2开始。在这个联结的ON子句之后，它开始第二个JOIN，将初始联结的表集合与table3合并。注意，第三张表可以与第一张表或第二张表中的一列进行联结。

为了说明这一点,假设你想知道员工的销售带来多少收入,但你只关心销售记录,包括员工他们专门从事销售该产品。

要获取这些信息，可以运行以下查询。这个查询首先通过匹配products和sales表各自的productID列来连接它们。然后，通过将初始JOIN中的每一行匹配到其productSpecialty列，将team表与前两个表连接起来。然后，查询用WHERE子句过滤结果，只返回匹配的员工同时也是销售人员的行。这个查询还包含一个ORDER BY子句，它根据saleID列中的值对最终结果进行升序排序：

SELECT sales.saleID,
team.empName,
products.productName,
(sales.quantity * products.price)
FROM products JOIN sales
USING (productID)
JOIN team
ON team.productSpecialty = sales.productID
WHERE team.empID = sales.salesperson
ORDER BY sales.saleID;

请注意，在这个查询的SELECT子句中列出的列是一个表达式，它将sales表的quantity列中的值乘以products表的price值。它返回匹配行的这些值的乘积:

Output+--------+----------+-------------+-----------------------------------+
| saleID | empName  | productName | (sales.quantity * products.price) |
+--------+----------+-------------+-----------------------------------+
|      1 | Florence | widget      |                            132.93 |
|      3 | Betty    | gizmo       |                            115.92 |
|      4 | Diana    | thingamajig |                             39.99 |
+--------+----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

到目前为止，所有的例子都具有相同类型的JOIN子句:INNER JOIN。关于INNER联结和OUTER联结的概述，以及它们的区别，请继续阅读下一节。

内部和外部的JOIN操作

有两种主要类型的JOIN子句:INNER联结和OUTER联结。这两种联结的区别在于它们返回的数据。INNER连接操作只返回每个连接表中匹配的行，而OUTER连接返回匹配和非匹配的行。

前一节中的示例语法和查询都使用了INNER JOIN子句，尽管它们都没有包含INNER关键字。大多数SQL实现都将任何JOIN子句视为INNER联结，除非另有明确说明。

指定了OUTER JOIN的查询会合并多个表，并返回匹配和不匹配的行。这对于查找有缺失值的行，或者可以接受部分匹配的情况很有用。

OUTER连接操作可以进一步分为三种类型:左外连接，右外连接和全外连接。LEFT OUTER联结，或简称为LEFT联结，返回两个联结表中所有匹配的行，以及" LEFT "表中所有不匹配的行。在JOIN操作的上下文中，“left”表总是FROM关键字之后指定的第一个表，并且在JOIN关键字的左侧。同样地，“right”表是第二个表，或者紧跟着JOIN的那个表，而 right OUTER联结返回连接表中所有匹配的行以及“right”表中所有不匹配的行。FULL OUTER JOIN返回两张表中的每一行，包括两张表中没有匹配的行。

为了说明这些不同类型的JOIN子句如何返回数据，在前面连接并设置示例数据库小节中创建的表上运行以下示例查询。除了指定不同类型的JOIN子句之外，这些查询是相同的。

第一个例子使用INNER JOIN通过匹配各自的salesperson和empID列来将sales和team表组合在一起。同样，INNER关键字是隐含的，即使它没有明确包含：

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName 
FROM sales JOIN team
ON sales.salesperson = team.empID;

因为这个查询使用了INNER JOIN子句，它只返回两张表中匹配的行：

Output+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName  |
+--------+----------+-------------+----------+
|      1 |        7 |           1 | Florence |
|      4 |        1 |           3 | Diana    |
|      5 |        5 |           3 | Diana    |
|      2 |       10 |           4 | Betty    |
|      3 |        8 |           4 | Betty    |
+--------+----------+-------------+----------+
5 rows in set (0.00 sec)

这个版本的查询使用了LEFT OUTER JOIN子句：

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales LEFT OUTER JOIN team
ON sales.salesperson = team.empID;

和前面的查询一样，这个查询也返回两个表中所有匹配的值。然而，它也会返回在“left”表(本例中是sales)中没有匹配的任何值，而在“right”表(team)中则没有。因为左表中的这些行在右表中没有匹配项，所以未匹配的值将作为NULL返回:

Output+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName  |
+--------+----------+-------------+----------+
|      1 |        7 |           1 | Florence |
|      2 |       10 |           4 | Betty    |
|      3 |        8 |           4 | Betty    |
|      4 |        1 |           3 | Diana    |
|      5 |        5 |           3 | Diana    |
|      6 |        1 |        NULL | NULL     |
|      7 |        3 |        NULL | NULL     |
|      8 |        4 |        NULL | NULL     |
+--------+----------+-------------+----------+
8 rows in set (0.00 sec)

下一个版本的查询使用了RIGHT JOIN子句：

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales RIGHT JOIN team
ON sales.salesperson = team.empID;

注意，这个查询的JOIN子句读作RIGHT JOIN而不是RIGHT OUTER JOIN。与INNER关键字不需要指定INNER JOIN子句类似，每当你写LEFT JOIN或RIGHT JOIN时，都需要指定OUTER。

这个查询的结果与前一个相反，它返回两个表中的所有行，但只返回“右”表中不匹配的行：

Output+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName  |
+--------+----------+-------------+----------+
|      1 |        7 |           1 | Florence |
|   NULL |     NULL |        NULL | Mary     |
|      4 |        1 |           3 | Diana    |
|      5 |        5 |           3 | Diana    |
|      2 |       10 |           4 | Betty    |
|      3 |        8 |           4 | Betty    |
+--------+----------+-------------+----------+
6 rows in set (0.00 sec)

注意：请注意，MySQL不支持FULL OUTER JOIN子句。为了说明如果使用FULL OUTER JOIN子句，该查询将返回什么数据，以下是PostgreSQL数据库上的结果集：

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales FULL OUTER JOIN team
ON sales.salesperson = team.empID;

Output saleid | quantity | salesperson | empname  
--------+----------+-------------+----------
      1 |        7 |           1 | Florence
      2 |       10 |           4 | Betty
      3 |        8 |           4 | Betty
      4 |        1 |           3 | Diana
      5 |        5 |           3 | Diana
      6 |        1 |             | 
      7 |        3 |             | 
      8 |        4 |             | 
        |          |             | Mary
(9 rows)

如上所示，FULL JOIN返回两个表中的每一行，包括不匹配的行。

JOIN子句中设置表名和列名的别名

当关联名称很长或描述性很强的表时，必须编写多个完全限定的列引用可能会变得冗长乏味。为了避免这种情况，用户有时会发现提供别名较短的表名或列名很有帮助。

在SQL中，你可以在FROM子句中的任何表定义后面加上AS关键字，然后在它后面加上你选择的别名:

SELECT t1.column1, t2.column2
FROM table1 AS t1 JOIN table2 AS t2
ON t1.related_column = t2.related_column;

这个示例语法在SELECT子句中使用了别名，即使它们是在FROM子句中才定义的。这是可能的，因为在SQL查询中，执行顺序以FROM子句开始。这可能令人困惑，但记住这一点并在开始编写查询之前考虑您的别名是有帮助的。

例如，运行以下查询，它连接sales和products表，并分别为它们提供别名S和P:

SELECT S.saleID, S.quantity,
P.productName,
(P.price * S.quantity) AS revenue 
FROM sales AS S JOIN products AS P
USING (productID);

注意，这个示例还为sales表的quantity列中的值与products表的price列中匹配的值的乘积创建了第三个别名revenue。这在结果集中的列名中很明显，但提供这样的别名有助于传达查询结果背后的含义或目的:

Output+--------+----------+-------------+---------+
| saleID | quantity | productName | revenue |
+--------+----------+-------------+---------+
|      1 |        7 | widget      |  132.93 |
|      2 |       10 | whatzit     |  299.90 |
|      3 |        8 | gizmo       |  115.92 |
|      4 |        1 | thingamajig |   39.99 |
|      5 |        5 | widget      |   94.95 |
|      6 |        1 | whatzit     |   29.99 |
|      7 |        3 | widget      |   56.97 |
|      8 |        4 | whatzit     |  119.96 |
+--------+----------+-------------+---------+
8 rows in set (0.00 sec)

请注意，在定义别名时，AS关键字在技术上是可选的。前面的例子也可以这样写：

SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue 
FROM sales S JOIN products P
USING (productID);

即使AS关键字不是定义别名的必要条件，但引入它也是一个很好的实践。这样做可以帮助查询的目的明确、提高其可读性。

总结

通过阅读本指南，你学会了如何使用JOIN操作将单独的表组合为单个查询结果集。虽然这里显示的命令在大多数关系型数据库上都可以工作，但请注意，每个SQL数据库都使用自己独特的语言实现。关于每个命令及其所有选项的更完整描述，读者可以查阅相应DBMS的官方文档。