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) syntax。如果名称不同，则说我们有CustomerId和CId，我们将使用ON Customers.CustomerId = Reservations.CId语法。

内连接

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

内连接

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将一个表中的所有记录与另一表中的所有记录组合在一起。这种连接几乎没有实用价值。它也称为记录的笛卡尔积。

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 不支持完全外部连接。

如前所述，内连接是最常见的连接。外部连接对于查找孤立记录可能很有用。如果一个人没有预订，是顾客吗？如果我们无法与客户匹配，预订是否有效？

左外连接

即使与右表不匹配，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 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 |
+-------------+------------+

这是两个表的正确连接的输出。右侧表的所有记录（预订）在左侧均具有匹配的记录（客户）。

自然连接

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

自然内连接

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提供表中所有匹配的记录以及左表中的所有其他记录。它会自动使用所有匹配的列名进行连接。

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提供表中的所有匹配记录以及右表中的所有其他记录。它会自动使用匹配的列名进行连接。

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）。

内连接

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

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

左外连接

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

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

右外连接

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