如何在SQL中使用连接
介绍
许多数据库设计基于某些数据点之间的关系将信息分成不同的表。 即使在这种情况下，也可能会有人希望一次从多个表中检索信息。
在单个 Structured Query Language (SQL) 操作中访问多个表中的数据的常用方法是将表与 JOIN 子句组合。 基于关系代数中的连接操作，JOIN 子句通过匹配每个表中相互关联的行来组合单独的表。 通常，这种关系基于一对列——每个表中的一个列——它们共享共同的值，例如一个表的 外键 和另一个表的 主键 关键参考。
本指南概述了如何构建包含 JOIN 子句的各种 SQL 查询。 它还强调了不同类型的 JOIN 子句，它们如何组合来自多个表的数据，以及如何为列名命名以减少编写 JOIN 操作的繁琐。
先决条件
为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。 本指南中的说明和示例已使用以下环境进行验证：
- 运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和配置了 UFW 的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
- 如 如何在 Ubuntu 20.04 上安装 MySQL 中所述，在服务器上安装并保护了 MySQL。 如 Step 3 中所述，本指南已通过新创建的用户验证。
注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。 尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，确切的语法或输出可能会有所不同。
- 您还需要一个数据库，其中包含一些加载了示例数据的表，您可以使用这些表来练习使用
JOIN操作。 我们鼓励您阅读以下 连接到 MySQL 并设置示例数据库 部分，详细了解如何连接到 MySQL 服务器并创建本指南示例中使用的测试数据库。
您还可以使用嵌入在此页面上的交互式终端来试验本教程中的示例查询。 单击下面的Launch an Interactive Terminal!按钮开始使用。
启动交互式终端！
连接到 MySQL 并设置示例数据库
如果您的 SQL 数据库系统在远程服务器上运行，请从本地计算机通过 SSH 连接到您的服务器：
ssh sammy@your_server_ip
然后打开 MySQL 服务器提示符，将 sammy 替换为您的 MySQL 用户帐户的名称。 如果您在此页面上使用嵌入式交互式终端，请注意提示时使用的密码是单词 secret：
mysql -u sammy -p
创建一个名为 joinsDB 的数据库：
CREATE DATABASE joinsDB;
如果数据库创建成功，您将收到如下输出：
OutputQuery OK, 1 row affected (0.01 sec)
要选择 joinsDB 数据库，请运行以下 USE 语句：
USE joinsDB;
OutputDatabase changed
选择 joinsDB 后，在其中创建几个表。 对于本指南中使用的示例，假设您经营一家工厂，并决定开始在 SQL 数据库中跟踪有关您的产品线、销售团队的员工以及您公司的销售额的信息。 您计划从三个表开始，其中第一个将存储有关您的产品的信息。 您决定第一个表需要三列：
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) );
第二个表将存储有关贵公司销售团队员工的信息。 您决定此表还需要三列：
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) );
您创建的最后一个表将保存公司的销售记录。 该表将有四列：
saleID：类似于productID和empID列，此列将保存以int数据类型表示的每个销售的唯一标识号。 此列还将具有UNIQUE约束，因此它可以用作sales表的主键quantity：每个产品销售的单位数，用int数据类型表示productID：所售产品的标识号，表示为intsalesperson：进行销售的员工的识别号
与 team 表中的 productSpecialty 列类似，您决定将 FOREIGN KEY 约束应用于 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 要返回哪些列 FROM 要查询哪个表。
其后是 ON 子句，它描述了查询应如何通过定义 搜索条件 将两个表连接在一起。 搜索条件是一组一个或多个 谓词 或可以评估某个条件是“真”、“假”还是“未知”的表达式。 将 JOIN 操作视为组合两个表中的每一行，然后返回 ON 子句中的搜索条件评估为“真”的任何行会很有帮助。
在 ON 子句中，包含一个搜索条件通常是有意义的，该条件测试两个相关列（例如一个表的外键和外键引用的另一个表的主键）是否具有相等的值。 这有时被称为 equi join。
作为 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 子句 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 连接是连接表的最常用方法，但也可以使用其他 SQL 运算符，例如 <、>、LIKE、NOT LIKE、甚至 ON 子句搜索条件中的 BETWEEN。 但请注意，使用更复杂的搜索条件可能会导致难以预测结果集中将出现哪些数据。
在大多数实现中，您可以将表与具有 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 子句有助于使结果集更加连贯和可读。
连接两个以上的表
有时您可能需要组合来自两个以上表的数据。 您可以通过在其他 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 子句首先将 table1 与 table2 连接起来。 在此连接的 ON 子句之后，它启动第二个 JOIN，它将初始连接表集与 table3 组合在一起。 请注意，第三个表可以连接到第一个或第二个表中的列。
举例来说，假设您想知道员工的销售带来了多少收入，但您只关心涉及员工销售他们擅长的产品的销售记录。
要获取此信息，您可以运行以下查询。 此查询首先通过匹配它们各自的 productID 列将 products 和 sales 表连接在一起。 然后，它通过将初始 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 表的 [X179X ] 值。 它在匹配的行中返回这些值的乘积：
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连接、RIGHT OUTER连接和FULL OUTER连接。 LEFT OUTER 连接，或者只是 LEFT 连接，返回两个连接表中的每个匹配行，以及“左”表中的每个不匹配行。 在 JOIN 操作的上下文中，“左”表始终是紧接在 FROM 关键字之后和 JOIN 关键字左侧指定的第一个表。 同样，“右”表是第二个表，或者紧跟在 JOIN 之后的表，并且 RIGHT OUTER 联接返回联接表中的每个匹配行以及“右”表。 FULL OUTER JOIN 返回两个表中的每一行，包括任一表中没有匹配项的任何行。
为了说明这些不同类型的 JOIN 子句如何返回数据，请对在前面的 连接并设置示例数据库 小节中创建的表运行以下示例查询。 这些查询是相同的，只是每个查询都指定了不同类型的 JOIN 子句。
第一个示例使用 INNER JOIN 将 sales 和 team 表通过匹配它们各自的 salesperson 和 empID 列组合在一起。 同样， 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;
与前一个查询一样，这个查询也返回两个表中的每个匹配值。 但是，它还会返回“左”表（在本例中为 sales）中没有与“右”表 (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 或 [X143X ]。
这个查询的结果与前一个相反，它返回两个表中的每一行，但只返回“右”表中不匹配的行：
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 列中的值与来自 [X175X 的匹配值的乘积创建了第三个别名 revenue ] 表的 price 列。 这仅在结果集中的列名中很明显，但提供这样的别名有助于传达查询结果背后的含义或目的：
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 的官方文档，以获得对每个命令及其全套选项的更完整描述。
如果您想了解有关使用 SQL 的更多信息，我们鼓励您查看本系列中关于 如何使用 SQL 的其他教程。
