如何在 SQL 中使用连接

介绍

许多数据库根据某些数据点之间的关系将单独的信息设计成不同的表,即使在这种情况下,也可能有时会有人想要从一次多个表中获取信息。

在单一的 https://en.wikipedia.org/wiki/SQL (SQL) 操作中访问多个表中的数据的一种常见方法是将这些表与一个JOIN条款相结合。基于关系算法中的合并操作,一个JOIN条款通过在每个表中匹配相互关联的行来组合单独的表。通常,这种关系是基于一对列 - 来自每个表的一个 - 共享共同值,例如一个表的外部密钥和另一个表的主密钥外部密钥参考。

本指南概述了如何构建包含JOIN条款的各种 SQL 查询,还突出了不同类型的JOIN条款,它们如何将来自多个表的数据组合在一起,以及如何代名列名称,以使写JOIN操作更轻松。

前提条件

要遵循本指南,您需要运行某种类型的关系数据库管理系统(RDBMS)的计算机,该系统使用SQL。

运行 Ubuntu 20.04 的服务器,具有非根用户的管理权限和与 UFW 配置的防火墙,如我们在 [Ubuntu 20.04 的初始服务器设置指南] 中所描述的。

<$>[注] 注:请注意,许多RDBMS使用自己的独特的SQL实现程序,虽然本教程中描述的命令将在大多数RDBMS上工作,但如果您在MySQL以外的系统上测试它们,精确的语法或输出可能会有所不同。

您还需要一个数据库,其中有一些表载有样本数据,您可以使用这些数据来练习使用JOIN操作. 我们鼓励您通过以下 连接到MySQL和设置样本数据库部分,了解如何连接到MySQL服务器并创建本指南中使用的测试数据库。

连接到MySQL并设置样本数据库

如果您的 SQL 数据库系统在远程服务器上运行,则从本地计算机输入 SSH 到服务器:

1[environment local]
2ssh sammy@your_server_ip

然后打开MySQL服务器提示,用您的MySQL用户帐户的名称代替sammy:

1mysql -u sammy -p

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

1CREATE DATABASE joinsDB;

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

1[secondary_label Output]
2Query OK, 1 row affected (0.01 sec)

要选择joinsDB数据库,请运行以下USE语句:

1USE joinsDB;

1[secondary_label Output]
2Database changed

在选择joinsDB后,在其内创建几个表格. 在本指南中使用的示例中,想象一下,您正在运营一个工厂,并已决定开始跟踪有关您的产品线,您的销售团队的员工和您的公司的销售信息在SQL数据库中。

productID:每個產品的識別號碼,用「int」資料類型表示。本列將作為表的 primary key,這意味著每個值將作為其相應行的獨特識別符功能。因為一個主要鍵中的每個值都必須是獨一無二的,這個列也將有一個「UNIQUE」限制應用於它
productName:每個產品的名稱,用最多 20 個字符的 varchar 資料類型表示
price:每個產品的價格,用 decimal 資料類型表示。

创建一个名为产品的表,其中包含以下三个列:

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

第二个表将存储有关公司销售团队的员工的信息. 您决定该表还需要三个列:

empID:类似于 productID 列,这个列将为销售团队的每个员工提供一个独特的识别号码,用 int 数据类型表示。同样,这个列将适用于 UNIQUE 限制,并作为 team 表的主要密钥
empName:每个销售人员的名称,用 varchar 数据类型表示,最多 20 个字符
productSpecialty:您的销售团队的每个成员都被分配了一个产品作为他们的专业;他们可以出售您的公司制造的任何产品,但他们的整体重点将是他们专注于任何产品。

要确保产品特征列只包含代表有效产品 ID 号码的值,您决定将 foreign key 限制应用到指向产品表的产品ID列的列中。

创建一个名为团队的表,使用这三个列:

1CREATE TABLE team (
2empID int UNIQUE,
3empName varchar(20),
4productSpecialty int,
5PRIMARY KEY (empID),
6FOREIGN KEY (productSpecialty) REFERENCES products (productID)
7);

您创建的最后一张表将存储公司的销售记录. 该表将有四个列:

saleID:类似于 productID 和 empID 列,此列将包含每个销售的唯一标识号,用 int 数据类型表示。这个列还将具有 UNIQUE 限制,因此它可以作为 sales 表的主要密钥
数量:每个销售产品的单位数,用 int 数据类型表示
productID:销售产品的标识号,以 int 表示
salesperson:销售的员工的标识号

与团队表中的产品特征列一样,您决定将外部钥匙限制应用于产品ID和销售人员列,从而确保这些列仅包含产品表的产品ID列和团队表的empID列中已经存在的值。

使用这四个列创建一个名为销售的表:

1CREATE TABLE sales (
2saleID int UNIQUE,
3quantity int,
4productID int,
5salesperson int,
6PRIMARY KEY (saleID),
7FOREIGN KEY (productID) REFERENCES products (productID),
8FOREIGN KEY (salesperson) REFERENCES team (empID)
9);

接下来,通过运行以下INSERT INTO操作来加载产品表中的一些样本数据:

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

然后加载团队表,其中包含一些样本数据:

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

加载销售表,并提供一些样本数据:

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

最后,想象一下,您的公司在销售团队中没有任何人参与的情况下进行一些销售。若要记录这些销售,请执行以下操作,将三个行添加到销售表中,这些行不包含销售人员列的值:

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

有了它,您已经准备好跟随本指南的其余部分,并开始学习如何在 SQL 中将表合并。

了解`JOIN`操作的语法

「JOIN」条款可用于各种 SQL 陈述,包括 UPDATE 和 DELETE 操作。

下面的示例显示包含JOIN条款的SELECT陈述的通用语法:

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

请注意,由于JOIN条款比较多个表的内容,此示例语法指定哪个表选择每个列,以前列的名称与表的名称和期限。

您可以在任何操作中使用完全合格的列引用,但在技术上只需要在来自不同表的两个列共享相同名称的操作中使用这些列引用。

在任何查询中,从条款是您定义应该搜索的数据集以返回所需的数据的地方。唯一的区别在于从条款包含两个由加入关键字分开的表格。写查询的有用方法是记住,您选择哪个列返回从哪个表,您想查询。

搜索条件是由一个或多个 predicates 或表达式组成的,可以评估一个特定条件是否为真,假或未知。

在一个ON条款中,通常有意义的包含一个搜索条件,测试两个相关的列 - 例如一个表的外部密钥和外部密钥引用的另一个表的主要密钥 - 是否具有等值。

作为 equi 如何将来自多个表的匹配数据合并的示例,请使用您之前添加的样本数据运行下列查询。此陈述将与产品和团队表结合,使用搜索条件测试它们的产品ID和产品特征列中的值匹配。

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

以下是这个查询的结果:

 1[secondary_label Output]
 2+----------+-------------+-------+
 3| empName  | productName | price |
 4+----------+-------------+-------+
 5| Florence | widget      | 18.99 |
 6| Mary     | doodad      | 11.50 |
 7| Diana    | thingamajig | 39.99 |
 8| Betty    | gizmo       | 14.49 |
 9+----------+-------------+-------+
104 rows in set (0.00 sec)

要说明 SQL 如何将这些表组合成这个结果集,让我们仔细看看这个过程. 要明确的是,下面的不是当数据库管理系统将两个表合并时会发生什么,但可以用来考虑JOIN操作是遵循这样的程序。

首先,查询会打印从条款中的第一个表中的每个行和列,即产品:

 1[secondary_label JOIN Process Example]
 2+-----------+-------------+-------+
 3| productID | productName | price |
 4+-----------+-------------+-------+
 5|         1 | widget      | 18.99 |
 6|         2 | gizmo       | 14.49 |
 7|         3 | thingamajig | 39.99 |
 8|         4 | doodad      | 11.50 |
 9|         5 | whatzit     | 29.99 |
10+-----------+-------------+-------+

然后,它会查看这些行中的每个行,并匹配团队表中的任何行,其产品特征等于该行中的产品ID值:

 1[secondary_label JOIN Process Example]
 2+-----------+-------------+-------+-------+----------+------------------+
 3| productID | productName | price | empID | empName  | productSpecialty |
 4+-----------+-------------+-------+-------+----------+------------------+
 5|         1 | widget      | 18.99 |     1 | Florence |                1 |
 6|         2 | gizmo       | 14.49 |     4 | Betty    |                2 |
 7|         3 | thingamajig | 39.99 |     3 | Diana    |                3 |
 8|         4 | doodad      | 11.50 |     2 | Mary     |                4 |
 9|         5 | whatzit     | 29.99 |       |          |                  |
10+-----------+-------------+-------+-------+----------+------------------+

然后,它切断没有匹配的行,并根据其在选择条款中的顺序重新排列列,丢弃未指定的列,重置行,并返回最终结果集:

 1[secondary_label JOIN Process Example]
 2+----------+-------------+-------+
 3| empName  | productName | price |
 4+----------+-------------+-------+
 5| Florence | widget      | 18.99 |
 6| Mary     | doodad      | 11.50 |
 7| Diana    | thingamajig | 39.99 |
 8| Betty    | gizmo       | 14.49 |
 9+----------+-------------+-------+
104 rows in set (0.00 sec)

使用 equi joins 是加入表的最常见方法,但您可以使用其他 SQL 操作符,如<,>,LIKE,NOT LIKE,甚至在ON条款搜索条件中使用BETWEEN。

在大多数实现中,您可以连接任何具有 SQL 标准称为‘JOIN’合格数据类型的列的表,这意味着,一般来说,可以将包含数字数据的列连接到包含数字数据的任何其他列,而不论其各自的数据类型。同样,通常可以将包含字符值的任何列连接到任何其他包含字符的列数据。

许多 SQL 实现也允许您将具有相同名称的列加入到使用代替ON的关键字中。

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

在此示例语法中,使用条款等同于ON table1.related_column = table2.related_column;。

由于销售和产品都有一个名为productID的列,您可以通过与使用关键字匹配这些列来加入它们。下列命令执行此操作,并返回每个销售的销售ID,出售的单位数量,出售的每个产品的名称和价格。

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

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

加入表时,数据库系统有时会以不容易预测的方式重新排列行,包括一个类似于此的订单按条款可以帮助使结果集更加一致和可读。

加入超过两张桌子

有时您可能需要将来自两个以上表的数据合并在一起. 您可以将任何数量的表合并在一起,将JOIN条款嵌入到其他JOIN条款中。

1SELECT table1.column1, table2.column2, table3.column3
2FROM table1 JOIN table2
3ON table1.related_column = table2.related_column
4JOIN table3
5ON table3.related_column = table1_or_2.related_column;

此示例语法中的FROM条款由将table1与table2合并开始.此合并的ON条款之后,它启动了第二个JOIN,将最初的合并表集与table3相结合。

为了说明,想象一下,你想知道你的员工的销售带来了多少收入,但你只关心涉及员工销售他们专门从事的产品的销售记录。

要获取此信息,您可以运行以下查询: 此查询始于将产品和销售表连接在一起,并匹配各自的产品ID列,然后将团队表连接到前两行,并将最初的JOIN列中的每个行匹配到其产品Specialty列。

 1SELECT sales.saleID,
 2team.empName,
 3products.productName,
 4(sales.quantity * products.price)
 5FROM products JOIN sales
 6USING (productID)
 7JOIN team
 8ON team.productSpecialty = sales.productID
 9WHERE team.empID = sales.salesperson
10ORDER BY sales.saleID;

请注意,在此查询中的SELECT条款中列出的列中,有一个表达式,将销售表的数量列中的值乘以产品表的价格值。

1[secondary_label Output]
2+--------+----------+-------------+-----------------------------------+
3| saleID | empName  | productName | (sales.quantity * products.price) |
4+--------+----------+-------------+-----------------------------------+
5|      1 | Florence | widget      |                            132.93 |
6|      3 | Betty    | gizmo       |                            115.92 |
7|      4 | Diana    | thingamajig |                             39.99 |
8+--------+----------+-------------+-----------------------------------+
93 rows in set (0.00 sec)

到目前为止,所有示例都具有相同类型的JOIN条款:INNER JOIN。

内部与外部`合并`操作

有两种主要类型的JOIN条款:INTER条款和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条款如何返回数据,请在上一节连接到并设置示例数据库中创建的表上运行以下示例查询。

此第一個例子使用「INTER JOIN」來結合「銷售」和「團隊」表,以匹配各自的「銷售人」和「empID」列。

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

由于此查询使用一个INTER JOIN条款,它只会从两个表中返回匹配的行:

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

此版本的查询使用一个LEFT OUTER JOIN条款,而不是:

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

与之前的查询一样,此查询还会返回来自两个表的每个匹配值,但是,它还会返回来自左表的任何值(在这种情况下,销售)在右表(团队)中没有匹配,因为左表的这些行没有匹配,所以未匹配的值将返回为NULL:

 1[secondary_label Output]
 2+--------+----------+-------------+----------+
 3| saleID | quantity | salesperson | empName  |
 4+--------+----------+-------------+----------+
 5|      1 |        7 |           1 | Florence |
 6|      2 |       10 |           4 | Betty    |
 7|      3 |        8 |           4 | Betty    |
 8|      4 |        1 |           3 | Diana    |
 9|      5 |        5 |           3 | Diana    |
10|      6 |        1 |        NULL | NULL     |
11|      7 |        3 |        NULL | NULL     |
12|      8 |        4 |        NULL | NULL     |
13+--------+----------+-------------+----------+
148 rows in set (0.00 sec)

这个下一个版本的查询使用一个RIGHT JOIN条款:

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

请注意,此查询的JOIN条款读取RIGHT JOIN而不是RIGHT OUTER JOIN。

此查询的结果与前者相反,因为它返回了两个表的每个行,但只有正确表的未匹配的行:

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

<$>[注] 注:请注意,MySQL不支持FULL OUTER JOIN条款. 为了说明如果该查询使用了FULL OUTER JOIN条款,该查询会返回哪些数据,以下是PostgreSQL数据库中结果的样子:

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

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

正如此输出所示,FULL JOIN 返回了两个表中的每个行,包括未匹配的行。

在`JOIN`条款中分类表和列名称

当添加具有长或高度描述性的名称的表时,需要编写多个完全合格的列引用可能会变得无聊。

在 SQL 中,您可以按照FROM条款中的任何表定义执行AS关键字,然后按照您选择的代码执行此操作:

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

此示例语法使用SELECT条款中的代名词,即使它们在FROM条款之前没有定义,但这是可能的,因为在 SQL 查询中,执行顺序从FROM条款开始。

举个例子,运行以下查询,将销售和产品表连接在一起,并分别为它们提供S和P副名称:

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

请注意,此示例还会创建第三个代名词收入,用于销售表的数量列中的值的产物,并从产品表的价格列中创建它们的匹配值。

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

请注意,在定义异名时,AS关键字在技术上是可选的。

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

虽然不需要AS关键字来定义一个字符串,但包括它被认为是一个很好的做法,这样做可以帮助保持查询的目的清晰,并提高其可读性。

结论

通过阅读本指南,您了解如何使用JOIN操作来将单独的表组合成一个单一的查询结果集. 虽然这里显示的命令应该在大多数关系数据库上工作,但请注意每一个SQL数据库都使用其独特的语言实现。

如果您想了解有关使用 SQL 的更多信息,我们鼓励您查看本系列中的其他教程在如何使用 SQL。