介绍
许多数据库设计会根据特定数据点之间的关系，将信息分离到不同的表中。即使在这种情况下，也可能出现需要同时从多个表中检索信息的情况。.
在结构化查询语言 (SQL) 操作中，访问多个表中的数据通常使用 JOIN 子句将表连接起来。JOIN 子句基于关系代数中的连接操作，通过匹配每个表中彼此相关的行来连接不同的表。通常，这种关系基于一对列（每个表中各一列）共享值，例如一个表的外键和该外键引用的另一个表的主键。.
本指南解释了如何构建包含 JOIN 子句的各种 SQL 查询。它还展示了不同类型的 JOIN 子句、如何合并来自多个表的数据，以及如何为列创建别名以简化 JOIN 操作的编写。.
先决条件
要遵循本指南，您需要一台安装了某种使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已在以下环境下验证：
- 服务器运行Ubuntu 20.04，有一个具有管理员权限的非root用户，并且配置了UFW防火墙。
- 服务器上已安装并配置了 MySQL 安全措施。
- 您还需要一个数据库，其中包含一些加载了示例数据的表，用于练习使用 JOIN 操作。我们建议您参考下文“连接到 MySQL 和设置示例数据库”部分，了解如何连接到 MySQL 服务器并创建本指南示例中使用的测试数据库。.
连接到 MySQL 并设置示例数据库
如果您的 SQL 数据库系统运行在远程服务器上，请从本地计算机通过 SSH 连接到您的服务器：
ssh sammy@your_server_ip
然后打开 MySQL 服务器提示符，并将 Sami 替换为您的 MySQL 用户名：
mysql -u sammy -p
创建一个名为 joinsDB 的数据库：
CREATE DATABASE joinsDB;
如果数据库创建成功，您将收到以下输出：
Output
Query OK, 1 row affected (0.01 sec)要选择 joinsDB 数据库，请运行以下 USE 语句：
USE joinsDB;Output
Database changed选择 joinsDB 后，在其中创建几个表。在本指南的示例示例中，假设您经营一家工厂，并决定在 SQL 数据库中跟踪有关生产线、销售团队员工和公司销售情况的信息。您将从三个表开始，第一个表将存储有关产品的信息。您决定第一个表需要三列：
- 产品 ID：每个产品的识别编号，数据类型为 int。此列作为表的主键，这意味着每个值都作为对应行的唯一标识符。由于主键中的每个值都必须唯一，因此该列还应用了 UNIQUE 约束。
- productName：每个产品的名称使用 varchar 数据类型表示，最多 20 个字符。.
- 价格：每件产品的价格，以十进制数据类型表示。这意味着此列中的每个值最多只能包含四位数字，其中两位数字位于小数点右侧。因此，此列的取值范围为 -99.99 至 99.99。
创建一个名为 products 的表，该表包含以下三列：
CREATE TABLE products (
productID int UNIQUE,
productName varchar(20),
price decimal (4,2),
PRIMARY KEY (productID)
);第二张表存储了公司销售团队员工的信息。您决定这张表也需要三列：
- empID：与 productID 列类似，此列包含销售团队中每位员工的唯一标识号，数据类型为 int。因此，此列将具有 UNIQUE 约束，并将作为团队表的主键。.
- empName：每个销售人员的姓名，使用 varchar 数据类型表示，最多 20 个字符。.
- 产品专长：您的销售团队中的每位成员都被分配了一个专长产品。他们可以销售公司生产的任何产品，但他们的主要工作重心是他们专长的产品。为了在表格中体现这一点，您需要创建此列，其中包含每位员工专长产品的 productID 值。.
为了确保 productSpecialty 列仅包含代表有效产品 ID 号的值，您决定对引用 Products 表中 productID 列的列应用外键约束。外键约束是一种表达两个表之间关系的方法，它要求被约束列中的值必须存在于被约束列中。在下面的 CREATE TABLE 语句中，外键约束要求添加到 team 表中 productSpecialty 列的任何值都必须存在于 Products 表中 productID 列中。.
创建一个名为“团队”的表格，包含以下三列：
CREATE TABLE team (
empID int UNIQUE,
empName varchar(20),
productSpecialty int,
PRIMARY KEY (empID),
FOREIGN KEY (productSpecialty) REFERENCES products (productID)
);
您创建的最后一个表将保存公司的销售记录。该表将包含四列：
- saleID：与 productID 和 empID 列类似，此列包含每笔销售的唯一标识号，数据类型为 int。此列还具有 UNIQUE 约束，因此可以用作销售表的主键。
- 数量：每种产品的销售单位数量用 int 数据类型表示。
- 产品 ID：所售产品的识别号码，以整数表示。
- 销售人员：完成销售的员工的身份证号码。
与团队表中的 productSpecialty 列一样，您决定对 productID 和 salesperson 列应用外键约束。这确保这些列仅包含产品表中 productID 列和团队表中 empID 列中分别存在的值。.
创建一个名为“销售”的表格，包含以下四列：
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);然后向团队表中加载示例数据：
INSERT INTO team
VALUES
(1, 'Florence', 1),
(2, 'Mary', 4),
(3, 'Diana', 3),
(4, 'Betty', 2);同时，将销售表加载示例数据：
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 表中添加三行“销售人员”列为空的记录：
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 关键字连接的表。编写查询的一个有效方法是记住选择要查询的表中的列。.
然后，还有一个 ON 子句，它通过定义搜索条件来解释查询如何连接两个表。搜索条件是一组一个或多个语句或表达式，对于特定条件，这些语句或表达式的计算结果可能为«真»、«假»或«未知»。可以将 JOIN 操作理解为将两个表中的每一行合并，然后返回 ON 子句中搜索条件计算结果为“真”的每一行。.
在 ON 子句中，通常有必要包含一个搜索条件，用于测试两个相关列（例如，一个表的外键和该外键引用的另一个表的主键）的值是否相等。这有时被称为等值连接。.
为了演示 equi 如何连接来自多个表的匹配数据，请使用您之前添加的示例数据运行以下查询。此查询将 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 子句中第一个表中每一行和每一列的产品：
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 |
+-----------+-------------+-------+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 | | | |
+-----------+-------------+-------+-------+----------+------------------+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)使用等值连接是连接表最常用的方法，但其他 SQL 运算符，例如 <、>、LIKE、NOT LIKE 甚至 BETWEEN，也可以用于 ON 子句的搜索条件。然而，请注意，使用更复杂的搜索条件可能会使预测结果集中会出现哪些数据变得困难。.
在大多数实现中，您可以根据任何具有 SQL 标准所称的«限定连接»（qualified JOIN）数据类型的列集来连接表。这意味着，通常情况下，您可以将包含数值数据的列连接到任何其他包含数值数据的列，而无需考虑对应的数据类型。类似地，您通常可以将包含字符值的列连接到任何其他包含字符数据的列。如前所述，用于连接两个表的列通常是表示表之间关系的列，例如外键和它所引用的另一个表的主键。.
许多 SQL 实现允许使用 USING 关键字而不是 ON 来连接同名列。此类操作的语法可能如下：
SELECT table1.column1, table2.column2
FROM table1 JOIN table2
USING (related_column);在这个示例语法中，USING 语句等价于 ON table1.related_column = table2.related_column;.
由于销售和产品表各有一个名为 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 子句可以帮助使结果集更清晰易读。.
合并两个以上的表
有时您可能需要合并来自两个以上表的数据。您可以通过在其他 JOIN 子句中嵌套 JOIN 子句来连接任意数量的表。以下语法示例展示了连接三个表的情况：
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 子句首先将表 1 连接到表 2。在此 ON 子句之后，它开始第二个 JOIN，将最初连接的表集与表 3 合并。请注意，第三个表可以连接到第一个表或第二个表中的列。.
例如，假设你想知道你的员工创造了多少销售额，但你只关心包含该员工擅长的产品的销售记录。.
要获取此信息，您可以运行以下查询。此查询首先将 Products 表和 Sales 表通过匹配各自的 productID 列连接起来。然后，它将 Team 表与前两个表连接起来，方法是将初始 JOIN 连接中的每一行与其 productSpecialty 列匹配。接下来，查询使用 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;
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 表达式：内连接。请继续阅读下一节，了解内连接、外连接及其区别的概述。.
内连接与外连接操作
JOIN 语句主要有两种类型：内连接和外连接。这两种连接的区别在于它们返回的数据。内连接只返回来自每个连接表的匹配行，而外连接则返回匹配行和不匹配行。.
前面几节的语法和示例查询都使用了 INNER JOIN 语句，即使它们都没有包含 INNER 关键字。大多数 SQL 实现会将所有 JOIN 语句都视为 INNER JOIN，除非另有明确说明。.
指定外部 JOIN 的查询会将多个表连接起来，并返回所有匹配的行以及所有不匹配的行。这对于查找包含缺失值的行或可以接受部分匹配的情况非常有用。.
外连接操作可以分为三种类型：左外连接、右外连接和全外连接。左外连接（简称左连接）返回两个连接表中所有匹配的行，以及“左”表中所有不匹配的行。在 JOIN 操作的上下文中，“左”表始终是紧跟在 FROM 关键字之后、JOIN 关键字左侧的第一个表。类似地，«右»表是第二个表，即紧跟在 JOIN 之后的表。右外连接返回两个连接表中所有匹配的行，以及«右»表中所有不匹配的行。全外连接返回两个表中的所有行，包括任一表中不匹配的行。.
为了演示这些不同类型的 JOIN 子句如何返回数据，请在上一节“连接和设置示例数据库”中创建的表上运行以下示例查询。这些查询完全相同，只是每个查询指定了不同类型的 JOIN 子句。.
第一个例子使用内部连接 (INNER JOIN) 将销售表和团队表通过匹配相应的销售人员 (salesperson) 和员工 ID (empID) 列来合并。同样，即使没有显式包含 INNER 关键字，它也隐含在内：
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales JOIN team
ON sales.salesperson = team.empID;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)
SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales LEFT 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 | NULL | NULL |
| 7 | 3 | NULL | NULL |
| 8 | 4 | NULL | NULL |
+--------+----------+-------------+----------+
8 rows in set (0.00 sec)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 JOIN 子句不需要指定 INNER 关键字一样，当您编写 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)JOIN 子句中的表和列别名
当连接名称过长或描述性很强的表时，编写多个完整的列引用会变得非常繁琐。为了避免这种情况，用户有时会发现提供较短的表名或列名别名很有用。.
在 SQL 中，您可以通过在 FROM 子句中的每个表定义后跟 AS 关键字，然后在 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);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)SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue
FROM sales S JOIN products P
USING (productID);虽然定义别名时并非必须使用 AS 关键字，但建议最好还是使用它。这样做有助于清晰地表达查询意图，并提高查询的可读性。.
结果
通过阅读本指南，您学习了如何使用 JOIN 操作将多个表合并到一个查询结果集中。虽然这里展示的命令适用于大多数关系型数据库，但请注意，每个 SQL 数据库都使用其独特的语言实现。您应该参考数据库管理系统 (DBMS) 文档，以获取每个命令及其所有选项的更完整说明。.
