多表连接方式

## 1 实验表

### 1.1 学生表

```sql
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `sex` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, '小强', '男', 8);
INSERT INTO `student` VALUES (2, '小花', '女', 5);
INSERT INTO `student` VALUES (3, '小名', '女', 6);
INSERT INTO `student` VALUES (4, '小五', '女', 6);
INSERT INTO `student` VALUES (5, '小星', '男', 30);
COMMIT;
```

### 1.2 学分表

```sql
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_type` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `teacher` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  `sid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES (1, '物理', '王老师', 90, 1);
INSERT INTO `score` VALUES (2, '数学', '李老师', 70, 2);
INSERT INTO `score` VALUES (3, '英语', '黄老师', 80, 3);
INSERT INTO `score` VALUES (4, '体育', '易老师', 99, 4);
INSERT INTO `score` VALUES (5, '化学', '科老师', 50, 4);
INSERT INTO `score` VALUES (6, '语文', '张老师', 88, 2);
INSERT INTO `score` VALUES (7, '地理', '地老师', 60, 6);
INSERT INTO `score` VALUES (8, '历史', '历老师', 65, 7);
COMMIT;
```

## 2 连接方式

### 2.1 内连接

1. 内连接是使用**比较运算符**根据每个表**共有的列的值**匹配两个表中的行，根据**比较运算符的不同**可分为**等值连接**、**非等值连接**，在**等值连接中把重复的属性列去掉则称为自然连接**。![Xnip2020-09-19_16-30-12](/media/202107/2021-07-04_102235.png)
2. 内连接实际上是先**将两张表进行笛卡尔积**，然后再**从笛卡尔积中找出符合条件的数据**。
3. 内连接查询方式主要有两种，一种是使用`where`，一种是使用`inner join`，具体如下：

```sql
select ... from ... where table1.column1 < 比较运算符 > table2.column2;
```

```sql
select ... from table1 inner join table2 on table1.column1 < 比较运算符 > table2.column2;
```

#### 2.1.1 等值连接

1. 内连接中当**比较运算符为 `=`**时称为**等值连接**。
2. 例如：

```shell
mysql> select * from  test.student  stu inner join test.score sco on  stu.id = sco.sid;
+----+--------+------+------+----+------------+-----------+--------+------+
| id | name   | sex  | age  | id | class_type | teacher   | number | sid  |
+----+--------+------+------+----+------------+-----------+--------+------+
|  1 | 小强   | 男   |    8 |  1 | 物理       | 王老师    |     90 |    1 |
|  2 | 小花   | 女   |    5 |  2 | 数学       | 李老师    |     70 |    2 |
|  3 | 小名   | 女   |    6 |  3 | 英语       | 黄老师    |     80 |    3 |
|  4 | 小五   | 女   |    6 |  4 | 体育       | 易老师    |     99 |    4 |
|  4 | 小五   | 女   |    6 |  5 | 化学       | 科老师    |     50 |    4 |
|  2 | 小花   | 女   |    5 |  6 | 语文       | 张老师    |     88 |    2 |
+----+--------+------+------+----+------------+-----------+--------+------+
```

#### 2.1.2 非等值连接

1. 内连接中当**比较运算符为非 `=` 时称为非等值连接**。
2. 例如：

```shell
mysql> select * from  test.student  stu inner join test.score sco on  stu.id > sco.sid;
+----+--------+------+------+----+------------+-----------+--------+------+
| id | name   | sex  | age  | id | class_type | teacher   | number | sid  |
+----+--------+------+------+----+------------+-----------+--------+------+
|  2 | 小花   | 女   |    5 |  1 | 物理       | 王老师    |     90 |    1 |
|  3 | 小名   | 女   |    6 |  1 | 物理       | 王老师    |     90 |    1 |
|  4 | 小五   | 女   |    6 |  1 | 物理       | 王老师    |     90 |    1 |
|  5 | 小星   | 男   |   30 |  1 | 物理       | 王老师    |     90 |    1 |
|  3 | 小名   | 女   |    6 |  2 | 数学       | 李老师    |     70 |    2 |
|  4 | 小五   | 女   |    6 |  2 | 数学       | 李老师    |     70 |    2 |
|  5 | 小星   | 男   |   30 |  2 | 数学       | 李老师    |     70 |    2 |
|  4 | 小五   | 女   |    6 |  3 | 英语       | 黄老师    |     80 |    3 |
|  5 | 小星   | 男   |   30 |  3 | 英语       | 黄老师    |     80 |    3 |
|  5 | 小星   | 男   |   30 |  4 | 体育       | 易老师    |     99 |    4 |
|  5 | 小星   | 男   |   30 |  5 | 化学       | 科老师    |     50 |    4 |
|  3 | 小名   | 女   |    6 |  6 | 语文       | 张老师    |     88 |    2 |
|  4 | 小五   | 女   |    6 |  6 | 语文       | 张老师    |     88 |    2 |
|  5 | 小星   | 男   |   30 |  6 | 语文       | 张老师    |     88 |    2 |
+----+--------+------+------+----+------------+-----------+--------+------+
```

#### 2.1.3 自然连接

1. 在**等值连接的基础上去掉重复的属性列**称为**自然连接**。
2. 例如[2.1.1 等值连接](#2-1-1-等值连接)中的第一列的`id` 和最后一列的`sid` 表示的都是学号，在等值连接中会将两列保存为一列：

```shell
mysql> select stu.*, sco.id, sco.class_type, sco.teacher, sco.number from  test.student  stu inner join test.score sco on  stu.id = sco.sid;
+----+--------+------+------+----+------------+-----------+--------+
| id | name   | sex  | age  | id | class_type | teacher   | number |
+----+--------+------+------+----+------------+-----------+--------+
|  1 | 小强   | 男   |    8 |  1 | 物理       | 王老师    |     90 |
|  2 | 小花   | 女   |    5 |  2 | 数学       | 李老师    |     70 |
|  3 | 小名   | 女   |    6 |  3 | 英语       | 黄老师    |     80 |
|  4 | 小五   | 女   |    6 |  4 | 体育       | 易老师    |     99 |
|  4 | 小五   | 女   |    6 |  5 | 化学       | 科老师    |     50 |
|  2 | 小花   | 女   |    5 |  6 | 语文       | 张老师    |     88 |
+----+--------+------+------+----+------------+-----------+--------+
```

### 2.2 外连接

外连接可分为三种，分别是**左连接**（left outer join/left join）、**右连接**（right outer join/right join）、**全外连接**（full outer join/full join）。

#### 2.2.1 左连接

1. 左连接是指**左边表的所有数据都显示出来**，**右边的表数据只显示共有的部分**，**没有对应的部分补** `NULL`。![Xnip2020-09-19_16-34-21](/media/202107/2021-07-04_102614.png)
2. 例如：

```shell
   mysql> select * from  test.student  stu left join test.score sco on  stu.id = sco.sid;
   +----+--------+------+------+------+------------+-----------+--------+------+
   | id | name   | sex  | age  | id   | class_type | teacher   | number | sid  |
   +----+--------+------+------+------+------------+-----------+--------+------+
   |  1 | 小强   | 男   |    8 |    1 | 物理       | 王老师    |     90 |    1 |
   |  2 | 小花   | 女   |    5 |    2 | 数学       | 李老师    |     70 |    2 |
   |  3 | 小名   | 女   |    6 |    3 | 英语       | 黄老师    |     80 |    3 |
   |  4 | 小五   | 女   |    6 |    4 | 体育       | 易老师    |     99 |    4 |
   |  4 | 小五   | 女   |    6 |    5 | 化学       | 科老师    |     50 |    4 |
   |  2 | 小花   | 女   |    5 |    6 | 语文       | 张老师    |     88 |    2 |
   |  5 | 小星   | 男   |   30 | NULL | NULL       | NULL      |   NULL | NULL |
   +----+--------+------+------+------+------------+-----------+--------+------+
   ```

#### 2.2.2 右连接

1. 右连接是指**右边表的所有数据都显示出来**，**左边的表数据只显示共有的部分**，**没有对应的部分补** `NULL`。
2. 例如：

```shell
   mysql> select * from  test.student  stu right join test.score sco on  stu.id = sco.sid;
   +------+--------+------+------+----+------------+-----------+--------+------+
   | id   | name   | sex  | age  | id | class_type | teacher   | number | sid  |
   +------+--------+------+------+----+------------+-----------+--------+------+
   |    1 | 小强   | 男   |    8 |  1 | 物理       | 王老师    |     90 |    1 |
   |    2 | 小花   | 女   |    5 |  2 | 数学       | 李老师    |     70 |    2 |
   |    2 | 小花   | 女   |    5 |  6 | 语文       | 张老师    |     88 |    2 |
   |    3 | 小名   | 女   |    6 |  3 | 英语       | 黄老师    |     80 |    3 |
   |    4 | 小五   | 女   |    6 |  4 | 体育       | 易老师    |     99 |    4 |
   |    4 | 小五   | 女   |    6 |  5 | 化学       | 科老师    |     50 |    4 |
   | NULL | NULL   | NULL | NULL |  7 | 地理       | 地老师    |     60 |    6 |
   | NULL | NULL   | NULL | NULL |  8 | 历史       | 历老师    |     65 |    7 |
   +------+--------+------+------+----+------------+-----------+--------+------+
   ```

#### 2.2.3 全外连接

1. 全外连接是指**左边和右边表的所有数据都显示出来**，如果**哪一边的表没有另一边表对应的部分**，则**补 `NULL`。**
2. **MySQL 不支持全外连接**，不过**可以使用左连接 `UNION` 右连接来实现**。
3. 例如：
   ```shell
   mysql> select * from  test.student  stu left join test.score sco on  stu.id = sco.sid
       -> union
       -> select * from  test.student  stu right join test.score sco on  stu.id = sco.sid;
   +------+--------+------+------+------+------------+-----------+--------+------+
   | id   | name   | sex  | age  | id   | class_type | teacher   | number | sid  |
   +------+--------+------+------+------+------------+-----------+--------+------+
   |    1 | 小强   | 男   |    8 |    1 | 物理       | 王老师    |     90 |    1 |
   |    2 | 小花   | 女   |    5 |    2 | 数学       | 李老师    |     70 |    2 |
   |    3 | 小名   | 女   |    6 |    3 | 英语       | 黄老师    |     80 |    3 |
   |    4 | 小五   | 女   |    6 |    4 | 体育       | 易老师    |     99 |    4 |
   |    4 | 小五   | 女   |    6 |    5 | 化学       | 科老师    |     50 |    4 |
   |    2 | 小花   | 女   |    5 |    6 | 语文       | 张老师    |     88 |    2 |
   |    5 | 小星   | 男   |   30 | NULL | NULL       | NULL      |   NULL | NULL |
   | NULL | NULL   | NULL | NULL |    7 | 地理       | 地老师    |     60 |    6 |
   | NULL | NULL   | NULL | NULL |    8 | 历史       | 历老师    |     65 |    7 |
   +------+--------+------+------+------+------------+-----------+--------+------+
   ```

### 2.3 交叉连接

1. 交叉连接会**返回两张表的笛卡尔积**。
2. 例如：
   ```shell
   mysql> select * from  test.student  stu cross join test.score sco;
   +----+--------+------+------+----+------------+-----------+--------+------+
   | id | name   | sex  | age  | id | class_type | teacher   | number | sid  |
   +----+--------+------+------+----+------------+-----------+--------+------+
   |  1 | 小强   | 男   |    8 |  1 | 物理       | 王老师    |     90 |    1 |
   |  2 | 小花   | 女   |    5 |  1 | 物理       | 王老师    |     90 |    1 |
   |  3 | 小名   | 女   |    6 |  1 | 物理       | 王老师    |     90 |    1 |
   |  4 | 小五   | 女   |    6 |  1 | 物理       | 王老师    |     90 |    1 |
   |  5 | 小星   | 男   |   30 |  1 | 物理       | 王老师    |     90 |    1 |
   |  1 | 小强   | 男   |    8 |  2 | 数学       | 李老师    |     70 |    2 |
   |  2 | 小花   | 女   |    5 |  2 | 数学       | 李老师    |     70 |    2 |
   |  3 | 小名   | 女   |    6 |  2 | 数学       | 李老师    |     70 |    2 |
   |  4 | 小五   | 女   |    6 |  2 | 数学       | 李老师    |     70 |    2 |
   |  5 | 小星   | 男   |   30 |  2 | 数学       | 李老师    |     70 |    2 |
   |  1 | 小强   | 男   |    8 |  3 | 英语       | 黄老师    |     80 |    3 |
   |  2 | 小花   | 女   |    5 |  3 | 英语       | 黄老师    |     80 |    3 |
   |  3 | 小名   | 女   |    6 |  3 | 英语       | 黄老师    |     80 |    3 |
   |  4 | 小五   | 女   |    6 |  3 | 英语       | 黄老师    |     80 |    3 |
   |  5 | 小星   | 男   |   30 |  3 | 英语       | 黄老师    |     80 |    3 |
   |  1 | 小强   | 男   |    8 |  4 | 体育       | 易老师    |     99 |    4 |
   |  2 | 小花   | 女   |    5 |  4 | 体育       | 易老师    |     99 |    4 |
   |  3 | 小名   | 女   |    6 |  4 | 体育       | 易老师    |     99 |    4 |
   |  4 | 小五   | 女   |    6 |  4 | 体育       | 易老师    |     99 |    4 |
   |  5 | 小星   | 男   |   30 |  4 | 体育       | 易老师    |     99 |    4 |
   |  1 | 小强   | 男   |    8 |  5 | 化学       | 科老师    |     50 |    4 |
   |  2 | 小花   | 女   |    5 |  5 | 化学       | 科老师    |     50 |    4 |
   |  3 | 小名   | 女   |    6 |  5 | 化学       | 科老师    |     50 |    4 |
   |  4 | 小五   | 女   |    6 |  5 | 化学       | 科老师    |     50 |    4 |
   |  5 | 小星   | 男   |   30 |  5 | 化学       | 科老师    |     50 |    4 |
   |  1 | 小强   | 男   |    8 |  6 | 语文       | 张老师    |     88 |    2 |
   |  2 | 小花   | 女   |    5 |  6 | 语文       | 张老师    |     88 |    2 |
   |  3 | 小名   | 女   |    6 |  6 | 语文       | 张老师    |     88 |    2 |
   |  4 | 小五   | 女   |    6 |  6 | 语文       | 张老师    |     88 |    2 |
   |  5 | 小星   | 男   |   30 |  6 | 语文       | 张老师    |     88 |    2 |
   |  1 | 小强   | 男   |    8 |  7 | 地理       | 地老师    |     60 |    6 |
   |  2 | 小花   | 女   |    5 |  7 | 地理       | 地老师    |     60 |    6 |
   |  3 | 小名   | 女   |    6 |  7 | 地理       | 地老师    |     60 |    6 |
   |  4 | 小五   | 女   |    6 |  7 | 地理       | 地老师    |     60 |    6 |
   |  5 | 小星   | 男   |   30 |  7 | 地理       | 地老师    |     60 |    6 |
   |  1 | 小强   | 男   |    8 |  8 | 历史       | 历老师    |     65 |    7 |
   |  2 | 小花   | 女   |    5 |  8 | 历史       | 历老师    |     65 |    7 |
   |  3 | 小名   | 女   |    6 |  8 | 历史       | 历老师    |     65 |    7 |
   |  4 | 小五   | 女   |    6 |  8 | 历史       | 历老师    |     65 |    7 |
   |  5 | 小星   | 男   |   30 |  8 | 历史       | 历老师    |     65 |    7 |
   +----+--------+------+------+----+------------+-----------+--------+------+
   ```

### 2.4 UNION

1. UNION 可以**把两次或多次查询结果合并起来**。
2. 两次或多次**查询的列数必须一致**，**列的类型可以不一样**，但**推荐查询的每一列**，**相对应的类型一样**。
3. 多次 SQL 语句取出的**列名可以不一致**，此时**以第一个 SQL 语句的列名为准**。
4. 如果不同语句取出的行有完全相同（每个列的值都相同），那么 UNION 会**将相同的行合并**，最终**只保留一行**，即 UNION 会**去掉重复的行**，如果**不想去掉重复的行**，可以使用**UNION ALL**。
5. 例如：

```shell
   mysql> (select * from  test.student  stu
       -> left join test.score sco on  stu.id = sco.sid
       -> limit 0,4)
       ->
       -> union
       ->
       -> (select * from  test.student  stu
       -> right join test.score sco on  stu.id = sco.sid
       -> limit 0,4);
   +------+--------+------+------+------+------------+-----------+--------+------+
   | id   | name   | sex  | age  | id   | class_type | teacher   | number | sid  |
   +------+--------+------+------+------+------------+-----------+--------+------+
   |    1 | 小强   | 男   |    8 |    1 | 物理       | 王老师    |     90 |    1 |
   |    2 | 小花   | 女   |    5 |    2 | 数学       | 李老师    |     70 |    2 |
   |    3 | 小名   | 女   |    6 |    3 | 英语       | 黄老师    |     80 |    3 |
   |    4 | 小五   | 女   |    6 |    4 | 体育       | 易老师    |     99 |    4 |
   |    2 | 小花   | 女   |    5 |    6 | 语文       | 张老师    |     88 |    2 |
   +------+--------+------+------+------+------------+-----------+--------+------+
   5 rows in set (0.00 sec)
   ```

```shell
   mysql> (select * from  test.student  stu
       -> left join test.score sco on  stu.id = sco.sid
       -> limit 0,4)
       ->
       -> union all
       ->
       -> (select * from  test.student  stu
       -> right join test.score sco on  stu.id = sco.sid
       -> limit 0,4);
   +------+--------+------+------+------+------------+-----------+--------+------+
   | id   | name   | sex  | age  | id   | class_type | teacher   | number | sid  |
   +------+--------+------+------+------+------------+-----------+--------+------+
   |    1 | 小强   | 男   |    8 |    1 | 物理       | 王老师    |     90 |    1 |
   |    2 | 小花   | 女   |    5 |    2 | 数学       | 李老师    |     70 |    2 |
   |    3 | 小名   | 女   |    6 |    3 | 英语       | 黄老师    |     80 |    3 |
   |    4 | 小五   | 女   |    6 |    4 | 体育       | 易老师    |     99 |    4 |
   |    1 | 小强   | 男   |    8 |    1 | 物理       | 王老师    |     90 |    1 |
   |    2 | 小花   | 女   |    5 |    2 | 数学       | 李老师    |     70 |    2 |
   |    2 | 小花   | 女   |    5 |    6 | 语文       | 张老师    |     88 |    2 |
   |    3 | 小名   | 女   |    6 |    3 | 英语       | 黄老师    |     80 |    3 |
   +------+--------+------+------+------+------------+-----------+--------+------+
   ```

可以看出**UNION 会去除重复的行**，而**UNION ALL 会保留重复的行**。

## 参考文献

1. [列举几种表连接方式？](https://github.com/wolverinn/Waking-Up/blob/master/Database.md#%E5%88%97%E4%B8%BE%E5%87%A0%E7%A7%8D%E8%A1%A8%E8%BF%9E%E6%8E%A5%E6%96%B9%E5%BC%8F)
2. [mysql 系列-多表连接方式](https://maizitoday.github.io/post/mysql%E7%B3%BB%E5%88%97-%E6%9F%A5%E6%89%BE%E4%BC%98%E5%8C%96-%E5%A4%96%E8%BF%9E%E6%8E%A5%E5%A4%9A%E8%A1%A8%E8%81%94%E5%90%88%E6%9F%A5%E8%AF%A2%E4%BB%A5%E5%8F%8A%E6%9F%A5%E8%AF%A2%E6%B3%A8%E6%84%8F%E7%82%B9)。
3. [Mysql 的 4 种表连接方式](https://mp.weixin.qq.com/s?src=3×tamp=1625298778&ver=1&signature=ld2NTvrEOCIur*bieWxzywDrT0ImsCB*dxwEsP29zXas4CtaRSFnSCpwXGUzwGM7U1dvWTAJ7hxP4lugkWvz9P3MNZG1vWVTLVgzjmeJP-7c0iN82c6aZpOSk1YFBO9MCU1xOdZKvL7F*-oOCZ0XcHGt1vDg2xvOw-jVk4pje*k=#fromHistory)。
4. [数据库系统概论-第 5 版_完整版（王珊，萨师煊）]()