MySQL 连接表

最后修改于 2023 年 1 月 10 日

在本 MySQL 教程中，我们将连接 MySQL 中的表。

从关系数据库获得真正力量和好处来自于连接表。 SQL JOIN 子句组合数据库中两个或多个表的记录。基本上有两种类型的连接：INNER 和 OUTER。

在本教程中，我们将使用 Customers 和 Reservations 表。

mysql> SELECT * FROM Customers;
+------------+-------------+
| CustomerId | Name        |
+------------+-------------+
|          1 | Paul Novak  |
|          2 | Terry Neils |
|          3 | Jack Fonda  |
|          4 | Tom Willis  |
+------------+-------------+

这些是 Customers 表中的值。

mysql> SELECT * FROM Reservations;
+----+------------+------------+
| Id | CustomerId | Day        |
+----+------------+------------+
|  1 |          1 | 2009-11-22 |
|  2 |          2 | 2009-11-28 |
|  3 |          2 | 2009-11-29 |
|  4 |          1 | 2009-11-29 |
|  5 |          3 | 2009-12-02 |
+----+------------+------------+

这些是 Reservations 表中的值。

Customers 和 Reservations 都有一个 CustomerId 列。它是关系列。如果它在两个表中具有相同的名称，我们可以使用 USING (CustomerId) 语法。如果名称不同，例如我们有 CustomerId 和 CId，我们将使用 ON Customers.CustomerId = Reservations.CId 语法。

MySQL 内连接

内连接是最常见的连接类型。它也是默认的连接。内连接仅选择数据库表中具有匹配值的那些记录。我们有三种类型的 INNER JOINS：INNER JOIN、NATURAL INNER JOIN 和 CROSS INNER JOIN。 INNER 关键字可以省略。

INNER JOIN

mysql> SELECT Name, Day FROM Customers AS C JOIN Reservations 
    -> AS R ON C.CustomerId=R.CustomerId;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+
5 rows in set (0.00 sec)

在此 SELECT 语句中，我们选择了所有已进行预订的客户。 Paul Novak 和 Terry Neils 进行了两次预订。 Jack Fonda 进行了一次。 Tom Willis 缺席，他还没有进行任何预订。请注意，我们省略了 INNER 关键字。

该语句等效于以下语句

mysql> SELECT Name, Day FROM Customers, Reservations
    -> WHERE Customers.CustomerId=Reservations.CustomerId;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

我们得到相同的数据。

CROSS INNER JOIN

CROSS INNER JOIN 将一个表中的所有记录与另一个表中的所有记录组合起来。这种类型的连接几乎没有实际价值。它也被称为记录的笛卡尔积。

mysql> SELECT Name, Day FROM Customers CROSS JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-28 |
| Paul Novak  | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Paul Novak  | 2009-12-02 |
| Terry Neils | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Terry Neils | 2009-11-29 |
| Terry Neils | 2009-12-02 |
| Jack Fonda  | 2009-11-22 |
...

可以使用以下 SQL 语句实现相同的结果

SELECT Name, Day FROM Customers, Reservations;

MySQL 外连接

一个 外连接 并不要求两个连接表中的每条记录都有匹配的记录。外连接有三种类型。左外连接、右外连接和全外连接。在本教程创建时，MySQL 不支持全外连接。

正如我们上面已经提到的，内连接是最常见的。外连接可能有助于找出孤立记录。如果一个人没有进行任何预订，他还是客户吗？如果我们无法将其与客户匹配，那么预订有效吗？

LEFT OUTER JOIN

LEFT OUTER JOIN 返回左表中的所有值，即使与右表不匹配。在此类行中，将有 NULL 值。换句话说，左外连接返回左表中的所有值，加上右表中的匹配值。请注意，可以省略 OUTER 关键字。

mysql> SELECT Name, Day FROM Customers LEFT JOIN Reservations
    -> ON Customers.CustomerId=Reservations.CustomerId;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-29 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
| Tom Willis  | NULL       |
+-------------+------------+

在这里，我们有所有客户及其预订，以及一个没有预订的客户。他的行中有一个 NULL 值。

我们可以使用 USING 关键字来实现相同的结果。这是因为关系列在两个表中具有相同的名称。 SQL 语句将不太冗长。

mysql> SELECT Name, Day FROM Customers LEFT JOIN Reservations
    -> USING (CustomerId);
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-29 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
| Tom Willis  | NULL       |
+-------------+------------+

相同的结果，使用更短的 SQL 语句。

RIGHT OUTER JOIN

RIGHT OUTER JOIN 和 RIGHT JOIN 是一样的。它给出了两个表中匹配的所有记录以及右表的所有可能性。孤立的右记录在左侧显示 NULL。

mysql> SELECT Name, Day FROM Customers RIGHT JOIN
    -> Reservations USING (CustomerId);
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

这是两个表右连接的输出。右侧表 (Reservations) 的所有记录在左侧 (Customers) 都有匹配的记录。

MySQL 自然连接

自然连接链接两个表中具有相同名称的所有列。在我们的 ustomers 和 Reservations 表中，我们有一个名为 CustomerId 的列。

NATURAL INNER JOIN

NATURAL INNER JOIN 自动使用所有匹配的列名进行连接。在我们的表中，我们在两个表中都有一个名为 CustomerId 的列。

mysql> SELECT Name, Day FROM Customers NATURAL JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

我们得到相同的数据。 SQL 语句不太冗长。

NATURAL LEFT OUTER JOIN

NATURAL LEFT OUTER JOIN 给出了表中所有匹配的记录和左表中的所有其他记录。它会自动使用所有匹配的列名进行连接。

mysql> SELECT Name, Day FROM Customers 
    -> NATURAL LEFT JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Paul Novak  | 2009-11-29 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
| Tom Willis  | NULL       |
+-------------+------------+

相同的结果，但使用更少的击键。

NATURAL RIGHT OUTER JOIN

NATURAL RIGHT OUTER JOIN 给出了表中所有匹配的记录和右表中的所有其他记录。它会自动使用匹配的列名进行连接。

mysql> SELECT Name, Day FROM Customers
    -> NATURAL RIGHT JOIN Reservations;
+-------------+------------+
| Name        | Day        |
+-------------+------------+
| Paul Novak  | 2009-11-22 |
| Terry Neils | 2009-11-28 |
| Terry Neils | 2009-11-29 |
| Paul Novak  | 2009-11-29 |
| Jack Fonda  | 2009-12-02 |
+-------------+------------+

快速回顾

接下来，我们将创建两个小表来回顾我们在这里学到的内容。

mysql> CREATE TABLE AA(A INTEGER);
mysql> CREATE TABLE BB(B INTEGER);
mysql> INSERT INTO AA VALUES(1);
mysql> INSERT INTO AA VALUES(2);
mysql> INSERT INTO AA VALUES(3);
mysql> INSERT INTO AA VALUES(4);
mysql> INSERT INTO BB VALUES(3);
mysql> INSERT INTO BB VALUES(4);
mysql> INSERT INTO BB VALUES(5);
mysql> INSERT INTO BB VALUES(6);

mysql> SELECT * FROM AA;
+------+
| A    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

mysql> SELECT * FROM BB;
+------+
| B    |
+------+
|    3 |
|    4 |
|    5 |
|    6 |
+------+

我们已经创建并填充了两个包含数字数据的表。表 AA 有两个唯一数字 (1, 2)，表 BB 也有两个唯一数字 (5, 6)。它们共享两个数字 (3, 4)。

INNER JOIN

mysql> SELECT * FROM AA JOIN BB ON A = B;
+------+------+
| A    | B    |
+------+------+
|    3 |    3 |
|    4 |    4 |
+------+------+

这是两个表的 INNER JOIN。我们仅从两个表中获取匹配的值。

LEFT OUTER JOIN

mysql> SELECT * FROM AA LEFT JOIN BB ON A = B;
+------+------+
| A    | B    |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 |    3 |
|    4 |    4 |
+------+------+

这是两个表的 LEFT OUTER JOIN。我们获得匹配值加上左表中没有匹配记录的值。

RIGHT OUTER JOIN

mysql> SELECT * FROM AA RIGHT JOIN BB ON A = B;
+------+------+
| A    | B    |
+------+------+
|    3 |    3 |
|    4 |    4 |
| NULL |    5 |
| NULL |    6 |
+------+------+

这是两个表的 RIGHT OUTER JOIN。我们获得匹配值加上右表中没有匹配记录的值。

在本 MySQL 教程中，我们正在连接表。

电子书

MySQL 连接表

MySQL 内连接

INNER JOIN

CROSS INNER JOIN

MySQL 外连接

LEFT OUTER JOIN

RIGHT OUTER JOIN

MySQL 自然连接

NATURAL INNER JOIN

NATURAL LEFT OUTER JOIN

NATURAL RIGHT OUTER JOIN

快速回顾

INNER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN