介绍
许多数据库设计基于某些数据点之间的关系将信息分离到不同的表中。即使在这种情况下，有时也可能有人希望一次从多个表中检索信息。
在单个结构化查询语言(SQL) 操作中访问来自多个表的数据的常用方法是将表与JOIN子句组合在一起。基于关系代数中的连接操作，JOIN子句通过匹配每个表中彼此相关的行来组合单独的表。通常，这种关系基于一对列（每个表中的一个），它们共享公共值，例如一个表的外键和外键引用的另一个表的主键。
本指南概述了如何构造包含JOIN子句的各种 SQL 查询。它还强调了不同类型的JOIN子句，它们如何组合来自多个表的数据，以及如何为列名设置别名以减少写入JOIN操作的乏味。
先决条件
为了遵循本指南，您需要一台运行某种类型的使用 SQL 的关系数据库管理系统 (RDBMS) 的计算机。本指南中的说明和示例已使用以下环境进行验证：
- 运行 Ubuntu 20.04 的服务器，具有管理权限的非 root 用户和使用 UFW 配置的防火墙，如我们的 Ubuntu 20.04 初始服务器设置指南中所述。
- 在服务器上安装并保护 MySQL，如如何在 Ubuntu 20.04 上安装 MySQL 中所述。本指南已使用新创建的用户进行验证，如步骤 3 中所述。
注意：请注意，许多 RDBMS 使用自己独特的 SQL 实现。尽管本教程中概述的命令适用于大多数 RDBMS，但如果您在 MySQL 以外的系统上测试它们，则确切的语法或输出可能会有所不同。
- 您还需要一个数据库，其中包含一些加载了示例数据的表，您可以使用这些表来练习使用
JOIN操作。我们鼓励您阅读以下连接到 MySQL 和设置示例数据库部分，详细了解如何连接到 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 数据库中跟踪有关您的产品线、销售团队员工和公司销售的信息。您计划从三个表开始，其中第一个将存储有关您的产品的信息。您决定第一个表需要三列：
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约束要求添加到表中productSpecialty列的任何值team必须已经存在于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：进行销售的员工的身份证号码
与表中的productSpecialty列一样team，您决定对和列应用FOREIGN KEY约束。这将确保这些列分别只包含表列和表列中已经存在的值。productIDsalespersonproductsproductIDteamempID
创建一个以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)
- );
之后，products通过运行以下INSERT INTO操作加载包含一些示例数据的表：
- 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子句的工作方式。
以下示例显示了SELECT包含JOIN子句的语句的一般语法：
- 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，甚至BETWEEN在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子句有助于使结果集更加连贯和可读。
加入两个以上的表
有时您可能需要合并来自两个以上表的数据。您可以通过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. 请注意，第三个表可以连接到第一个或第二个表中的列。
为了说明这一点，假设您想知道您的员工的销售带来了多少收入，但您只关心涉及销售他们专攻产品的员工的销售记录。
要获取此信息，您可以运行以下查询。此查询首先通过匹配它们各自的列将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;
请注意，在此查询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子句：INNERjoins 和OUTERjoins。这两种类型的连接之间的区别与它们返回的数据有关。INNER连接操作只返回每个连接表中匹配的行，而OUTER连接返回匹配和不匹配的行。
前面部分的示例语法和查询都使用了INNER JOIN子句，即使它们都不包含INNER关键字。大多数 SQL 实现将任何JOIN子句视为INNER连接，除非另有明确说明。
指定OUTER JOIN组合多个表并返回任何匹配行以及不匹配行的查询。这对于查找缺少值的行或在可接受部分匹配的情况下很有用。
OUTERLEFT OUTER联接操作可以进一步分为三种类型：联接、RIGHT OUTER联接和FULL OUTER联接。LEFT OUTER连接或只是LEFT连接，返回两个连接表中的每个匹配行，以及“左”表中的每个不匹配行。在JOIN操作上下文中，“左”表始终是紧跟在FROM关键字之后且位于关键字左侧的第一个表JOIN。同样，“右”表是第二个表，或一个紧随其后的JOIN，并RIGHT OUTER加入从连接表从“右”表中的每个非匹配行一起返回每个匹配的行。AFULL OUTER JOIN返回两个表中的每一行，包括任一表中没有匹配项的任何行。
为了说明这些不同类型的JOIN子句如何返回数据，请对在前面的连接和设置示例数据库小节中创建的表运行以下示例查询。这些查询是相同的，只是每个查询指定了不同类型的JOIN子句。
第一个示例使用和通过匹配各自的和列INNER JOIN将sales和team表组合在一起。同样，即使没有明确包含该关键字，它也是隐含的：salespersonempIDINNER
- 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子句的方式类似，在OUTER您编写LEFT JOINor时隐含RIGHT JOIN。
此查询的结果与前一个相反，因为它返回两个表中的每一行，但仅返回“右”表中不匹配的行：
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);
请注意，这个例子还创建第三别名revenue，在该值的产品sales表的quantity从列，它们的匹配值products的表的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的其他教程。
