连接查询语法

# 连接查询语法

连接查询就是把各个表中的记录都取出来，组成新的更大的记录，这个组合后的结果集可以方便我们分析数据，就不用老是两个表对照着看了。

# 基本语法

下面用 t1 和 t2 分别指代两张表的名字，m 和 n 指代表中的列名。

在 MySQL 中，连接查询的语法是在 FROM 语句后边用多个用逗号 , 隔开表名，就像这样：

SELECT * FROM t1, t2;

查询列表处的 * 代表从 FROM 语句后列出的表中选取每个列，也可以指定需要查询出来的列名：

SELECT t1.m1, t2.m2 FROM t1, t2;

或者查询某张表的全部列，另一张表的指定列：

SELECT t1.*, t2.m2 FROM t1, t2;

携带过滤条件的连接查询：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在上面这个两表连接查询中，假定把 t1 表作为第一个需要查询的表，于是它称之为 驱动表，相应的 t2 表称之为 被驱动表。在执行过程中，会先在 t1 表中找出所有满足条件的记录，对于获取到的每一条记录，都需要到 t2 表中查找匹配的记录。

因此在两表连接查询中，驱动表只需要查询一次，被驱动表可能会被查询多次。

小贴士

上面这种连接查询有个特点，就是如果 t1（驱动表）中查出来的记录，在 t2（被驱动表）中没有对应的记录，就直接不显示到结果集了。

那么如果换个需求：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。为了解决这个问题，就有了内连接和外连接的概念。

# 内连接和外连接

# 区别

对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，前面提到的连接都是所谓的内连接。
对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

在 MySQL 中，根据选取驱动表的不同，外连接可以细分为 2 种：

左外连接：选取左侧的表为驱动表。
右外连接：选取右侧的表为驱动表。

小贴士

左外连接和右外连接可以简称左连接和右连接。

# WHERE 和 ON 子句

即使对于外连接来说，有时候我们也并不想把驱动表的全部记录都加入到最后的结果集。于是过滤条件的写法除了 WHERE 子句，又引入了 ON 子句。

WHERE 子句中的过滤条件

WHERE 子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集。
ON 子句中的过滤条件

对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。

需要注意的是，这个 ON 子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的，所以如果把 ON 子句放到内连接中，MySQL 会把它和 WHERE 子句一样对待，也就是说：内连接中的 WHERE 子句和 ON 子句是等价的。

一般情况下，我们都把只涉及单表的过滤条件放到 WHERE 子句中，把涉及两表的过滤条件都放到 ON 子句中，我们也一般把放到 ON 子句中的过滤条件也称之为连接条件。

# 左（外）连接的语法

# 语法：

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

中括号里的 OUTER 单词是可以省略的。
对于 LEFT JOIN 类型的连接来说，放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表。
对于左连接和右连接来说，必须使用 ON 子句来指出连接条件。

# 示例：

要求把所有的学生的成绩信息都查询出来，即使是缺考的考生也应该被放到结果集中（这里用到了数据的增删改里插入的表和数据）。

mysql> SELECT student_info.number, name, major, subject, score FROM student_info LEFT JOIN student_score ON student_info.number = student_score.number;
+----------+-----------+--------------------------+--------------+-------+
| number   | name      | major                    | subject      | score |
+----------+-----------+--------------------------+--------------+-------+
| 20220101 | 张星星    | 计算机科学与工程         | 大学英语     |    88 |
| 20220101 | 张星星    | 计算机科学与工程         | 高等数学     |    78 |
| 20220102 | 王二狗    | 计算机科学与工程         | 大学英语     |    98 |
| 20220102 | 王二狗    | 计算机科学与工程         | 高等数学     |   100 |
| 20220103 | 陈珊珊    | 软件工程                 | 大学英语     |    61 |
| 20220103 | 陈珊珊    | 软件工程                 | 高等数学     |    59 |
| 20220104 | 李思思    | 软件工程                 | 大学英语     |    46 |
| 20220104 | 李思思    | 软件工程                 | 高等数学     |    55 |
| 20220105 | 孙小武    | 飞行器设计               | NULL         |  NULL |
| 20220106 | 刘大彪    | 电子信息                 | NULL         |  NULL |
+----------+-----------+--------------------------+--------------+-------+
10 rows in set (0.00 sec)

mysql>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

从结果集中可以看出来，虽然 孙小武 和 刘大彪 并没有对应的成绩记录，但是由于采用的是连接类型为左（外）连接，所以仍然把它放到了结果集中，只不过在对应的成绩记录的各列使用了 NULL 值填充。

# 右（外）连接的语法

# 语法：

右（外）连接和左（外）连接的原理是一样一样的，语法也只是把 LEFT 换成 RIGHT 而已。

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

只不过驱动表是右边的表，被驱动表是左边的表，具体就不重复展开了。

# 内连接的语法

内连接和外连接的根本区别就是在驱动表中的记录不符合 ON 子句中的连接条件时不会把该记录加入到最后的结果集。

本文一开始写的连接查询都是内连接，不过之前仅仅提到了一种最简单的内连接语法，就是直接把需要连接的多个表都放到 FROM 子句后边。

其实针对内连接，MySQL 提供了好多不同的语法，以 t1 和 t2 表为例：

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

也就是说在 MySQL 中，下边这几种内连接的写法都是等价的：

SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;

这些写法和直接把需要连接的表名放到 FROM 语句之后，用逗号 , 分隔开的写法也是等价的：

 SELECT * FROM t1, t2;

虽然有多种内连接的书写方式，不过还是推荐 INNER JOIN 的形式书写内连接（因为语义很明确，可以和 LEFT JOIN 及 RIGHT JOIN 很轻松地区分开）。

由于在内连接中 ON 子句和 WHERE 子句是等价的，所以内连接中不强制要求写明 ON 子句。

# 总结

连接的本质就是把各个连接表中的记录都取出来，将依次匹配的组合加入结果集并返回给用户。不论哪个表作为驱动表，两表连接产生的结果集数量肯定是一样的。

而对于内连接来说，由于凡是不符合 ON 子句或 WHERE 子句中的条件的记录都会被过滤掉，其实也就相当于从两表连接的结果集中把不符合过滤条件的记录给踢出去，所以对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。

但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合 ON 子句连接条件的记录也会被加入结果集，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

（完）

子查询语法组合查询语法