MySQL JOIN

本文介绍了 MySQL 中的内连接、左连接、右连接、交叉连接。

在 MySQL 中，JOIN 语句用于将数据库中的两个表或者多个表组合起来。

比如在一个学校系统中，有一个学生信息表和一个学生成绩表。这两个表通过学生 ID 字段关联起来。当我们要查询学生的成绩的时候，就需要连接两个表以查询学生信息和成绩。

MySQL 连接类型

MySQL 支持以下类型的连接：

  • 内部联接 (INNER JOIN)
  • 左连接 (LEFT JOIN)
  • 右连接 (RIGHT JOIN)
  • 交叉连接 (CROSS JOIN)

MySQL 目前不支持全连接 FULL OUTER JOIN 。

实例表和数据

本教程中关于表连接的实例都使用 student 和 student_score 两个表来完成。

首先，使用下面的 SQL 语句创建表 student 和 student_score ：

CREATE TABLE `student` (
  `student_id` int NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`student_id`)
);

CREATE TABLE `student_score` (
  `student_id` int NOT NULL,
  `subject` varchar(45) NOT NULL,
  `score` int NOT NULL
);

然后，分别在两个表中插入数据：

INSERT INTO `student` (`student_id`, `name`)
VALUES (1,'Tim'),(2,'Jim'),(3,'Lucy');

INSERT INTO `student_score` (`student_id`, `subject`, `score`)
VALUES (1,'English',90),(1,'Math',80),(2,'English',85),
       (2,'Math',88),(5,'English',92);

第三，从表中查询数据以验证数据已经成功插入：

SELECT * FROM student;

+------------+------+
| student_id | name |
+------------+------+
|          1 | Tim  |
|          2 | Jim  |
|          3 | Lucy |
+------------+------+
3 rows in set (0.01 sec)
SELECT * FROM student_score;

+------------+---------+-------+
| student_id | subject | score |
+------------+---------+-------+
|          1 | English |    90 |
|          1 | Math    |    80 |
|          2 | English |    85 |
|          2 | Math    |    88 |
|          5 | English |    92 |
+------------+---------+-------+
5 rows in set (0.00 sec)

注意： student_score 表中的最后一行的 student_id 为 5，而 student 表中不存在 student_id 为 5 的记录。

交叉连接

交叉连接返回两个集合的笛卡尔积。也就是两个表中的所有的行的所有可能的组合。这相当于内连接没有连接条件或者连接条件永远为真。

如果一个有 m 行的表和另一个有 n 行的表，它们交叉连接将返回 m * n 行数据。

显式的交叉连接 student 和 student_score 表：

SELECT
  student.*,
  student_score.*
FROM
  student CROSS JOIN student_score;

隐式的交叉连接 student 和 student_score 表：

SELECT
  student.*,
  student_score.*
FROM
  student, student_score;

这两种方式的输出一样。

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          3 | Lucy |          1 | English |    90 |
|          2 | Jim  |          1 | English |    90 |
|          1 | Tim  |          1 | English |    90 |
|          3 | Lucy |          1 | Math    |    80 |
|          2 | Jim  |          1 | Math    |    80 |
|          1 | Tim  |          1 | Math    |    80 |
|          3 | Lucy |          2 | English |    85 |
|          2 | Jim  |          2 | English |    85 |
|          1 | Tim  |          2 | English |    85 |
|          3 | Lucy |          2 | Math    |    88 |
|          2 | Jim  |          2 | Math    |    88 |
|          1 | Tim  |          2 | Math    |    88 |
|          3 | Lucy |          5 | English |    92 |
|          2 | Jim  |          5 | English |    92 |
|          1 | Tim  |          5 | English |    92 |
+------------+------+------------+---------+-------+
15 rows in set (0.00 sec)

内连接

内连接基于连接条件组合两个表中的数据。内连接相当于加了过滤条件的交叉连接。

内连接将第一个表的每一行与第二个表的每一行进行比较，如果满足给定的连接条件，则将两个表的行组合在一起作为结果集中的一行。

表示 A 表和 B 表之间的内连接的维恩图
表示 A 表和 B 表之间的内连接的维恩图。

以下 SQL 语句将 student 表和 student_score 表内连接：

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score
  ON student.student_id = student_score.student_id;

等价于：

SELECT
  student.*,
  student_score.*
FROM
  student, student_score
  WHERE student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | English |    90 |
|          1 | Tim  |          1 | Math    |    80 |
|          2 | Jim  |          2 | English |    85 |
|          2 | Jim  |          2 | Math    |    88 |
+------------+------+------------+---------+-------+
4 rows in set (0.00 sec)

注意输出结果中，student 表中 student_id 为 3 的行和 student_score 表中 student_id 为 5 的行没有出现在输出结果中，这是因为他们没有满足连接条件：student.student_id = student_score.student_id。

由于两个表都使用相同的字段进行等值匹配，因此您可以使用 USING 以下查询中所示的子句：

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score USING(student_id);

左连接

左连接是左外连接的简称，左连接需要连接条件。

两个表左连接时，第一个表称为左表，第二表称为右表。例如 A LEFT JOIN B，A 是左表，B 是右表。

左连接以左表的数据行为基础，根据连接匹配右表的每一行，如果匹配成功则将左表和右表的行组合成新的数据行返回；如果匹配不成功则将左表的行和 NULL 值组合成新的数据行返回。

表示 A 表和 B 表之间的左连接的维恩图
表示 A 表和 B 表之间的左连接的维恩图。

以下 SQL 语句将 student 表和 student_score 表左连接：

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score
  ON student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | Math    |    80 |
|          1 | Tim  |          1 | English |    90 |
|          2 | Jim  |          2 | Math    |    88 |
|          2 | Jim  |          2 | English |    85 |
|          3 | Lucy |       NULL | NULL    |  NULL |
+------------+------+------------+---------+-------+
5 rows in set (0.00 sec)

注意：

  1. 结果集中包含了 student 表的所有记录行。
  2. student_score 表中不包含 student_id = 3 的记录行，因此结果几种最后一行中来自 student_score 的列的内容为 NULL。
  3. student_score 表存在多条 student_id 为 1 和 2 的记录，因此 student 表也产生了多行数据。

由于两个表都使用相同的字段进行等值匹配，因此您可以使用 USING 以下查询中所示的子句：

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score USING(student_id);

右连接

右连接是右外连接的简称，右连接需要连接条件。

右连接与左连接处理逻辑相反，右连接以右表的数据行为基础，根据条件匹配左表中的数据。如果匹配不到左表中的数据，则左表中的列为 NULL 值。

表示 A 表和 B 表之间的右连接的维恩图
表示 A 表和 B 表之间的右连接的维恩图。

以下 SQL 语句将 student 表和 student_score 表右连接：

SELECT
  student.*,
  student_score.*
FROM
  student
  RIGHT JOIN student_score
  ON student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | English |    90 |
|          1 | Tim  |          1 | Math    |    80 |
|          2 | Jim  |          2 | English |    85 |
|          2 | Jim  |          2 | Math    |    88 |
|       NULL | NULL |          5 | English |    92 |
+------------+------+------------+---------+-------+
5 rows in set (0.00 sec)

从结果集可以看出，由于左表中不存在到与右表 student_id = 5 匹配的记录，因此最后一行左表的列的值为 NULL。

右连接其实是左右表交换位置的左连接，即 A RIGHT JOIN B 就是 B LEFT JOIN A，因此右连接很少使用。

上面例子中的右连接可以转换为下面的左连接：

SELECT
  student.*,
  student_score.*
FROM
  student_score
  LEFT JOIN student
  ON student.student_id = student_score.student_id;

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | English |    90 |
|          1 | Tim  |          1 | Math    |    80 |
|          2 | Jim  |          2 | English |    85 |
|          2 | Jim  |          2 | Math    |    88 |
|       NULL | NULL |          5 | English |    92 |
+------------+------+------------+---------+-------+
5 rows in set (0.00 sec)

从输出结果可以看出，此例子中的左连接的结果集与上面例子中的右连接的结果集是一样的。

结论

本文介绍了 MySQL 中的连接语句包括交叉连接、内连接、左连接和右连接。连接的要点包括：

  • 连接用于组合两个表的数据。
  • 交叉连接返回两个表中的所有的行的所有可能的组合。
  • 内连接基于连接条件组合两个表中的数据。
  • 左连接以左表为基础组合两个表中的数据。
  • 右连接以右表为基础组合两个表中的数据。
  • 互换左表和右表后，左右连接可以互换。