【MYSQL】MYSQL 的学习教程（二）之 MYSQL 的七种连接

1. 概述

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。一个业务都会对应多张表，比如：学生和班级，起码两张表

①：连接分类（功能划分）：

1. 交叉连接：笛卡尔积
2. 内连接：等值连接、非等值连接、自连接
3. 外连接：左(外)连接、左(外)连接特殊情况、右(外)连接、右(外)连接特殊情况、全连接、全外连接

②：连接分类（年代划分）：

• SQL 92 年语法（仅仅支持内连接）

SELECT *
FROM TEST1, TEST2
WHERE TEST1.SId = TEST2.sId

• SQL 99 年语法 （推荐使用）

SELECT *
FROM TAB_TEST1 test1
INNER JOIN TAB_TEST2 test2
ON test1.sId = test2.sId

③：内连接和外连接的区别：

假设 A 和 B 表进行连接

• 内连接：凡是 A 表和 B 表能够匹配上的记录，都查询出来。A、B 两张表没有主副之分，两张表是平等的
• 外连接：A、B 两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表。当副表中的数据没有和主表中的数据匹配上，副表自动模拟出 NULL 与之匹配

测试数据

# 构建表
CREATE TABLE `student`  (
  `id` int NOT NULL,
  `name` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int NOT NULL,
  `teacher_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `teacher`  (
  `id` int NOT NULL,
  `name` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int NOT NULL,
  `class_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

# 插入数据
INSERT INTO `student` VALUES (1, '刘峰', 20, 1);
INSERT INTO `student` VALUES (2, '李福', 22, 2);
INSERT INTO `student` VALUES (3, '王紫', 21, 3);
INSERT INTO `student` VALUES (4, '赵兰', 24, 3);

INSERT INTO `teacher` VALUES (1, '夏晴', 36, 1);
INSERT INTO `teacher` VALUES (2, '李淳', 32, 2);
INSERT INTO `teacher` VALUES (3, '张叶', 34, 3);

2. 交叉连接（笛卡尔积）：CROSS JOIN

假设集合 A = {a,b}，集合 B = {0,1,2}，则两个集合的笛卡尔积为 {(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。如果 A 表示某学校学生的集合，B 表示该学校所有教师的集合，则 A 与 B 的笛卡尔积表示学生选择老师所有可能的情况

笛卡尔积特点：它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行 一 一 匹配

案例：

查询学生对应的老师

对应的 SQL 语句如下：

SELECT * FROM student CROSS JOIN teacher;

或者：

SELECT * FROM student, teacher;

结果如下：学生表 中数据每 1 个学生都和 教师表 中的 所有教师 都匹配一次

问题：

当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。这就是笛卡尔积现象。 查询出来的结果是两张表的记录的乘积 4 * 3 = 12，许多数据是无效数据。如何避免笛卡尔积现象？

解决方案：

增加条件进行过滤，但只会显示有效记录

案例：

根据教师 id 查询学生对应的选课老师

对应的 SQL 语句如下：

SELECT st.*,th.* FROM student st ,teacher th WHERE st.teacher_id = th.id

结果如下：

3. 内连接：INNER JOIN（INNER 可以省略）

3.1 等值连接

等值连接：条件是等量关系

案例：

根据教师 id 查询学生对应的选课老师

对应的 SQL 语句如下：

SELECT st.*,th.* FROM student st JOIN teacher th ON st.teacher_id = th.id;

等值连接与自然连接的区别

等值连接：当条件为“=”的连接为等值连接，是连接属性值相等的那些元组

自然连接：自然连接不仅要求连接的两个字段必须同名，还要求将结果中重复的属性列去掉

上面的等值连接进行比较的都是 B 属性， 但还可以进行 R.B = S.E 等值连接， 而自然连接只能是同名属性组

3.2 非等值连接

非等值连接：条件不是等量关系

案例：

查询教师 id 在 1-2 之间所教的学生和老师信息

对应的 SQL 语句如下：

SELECT st.*,th.* FROM student st JOIN teacher th ON st.teacher_id = th.id AND th.id BETWEEN 1 AND 2;

3.3 自连接

自连接：就是一张表看做两张表，自己连接自己

案例：

查询学生 id 和教师 id 相同的学生

对应的 SQL 语句如下：

 SELECT s.*,st.teacher_id FROM student s, student st WHERE s.id = st.teacher_id;

4. 外连接：OUTER JOIN（OUTER 可以省略）

4.1 左外连接：LEFT JOIN

①：左外连接：左边的是主表，左表数据全部显示，右表显示符合 ON 后的条件的数据，不符合的用 NULL 代替

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id;

②：左外特殊情况：返回没有匹配的记录

案例：

查询没有教师的学生信息

对应的 SQL 语句如下：

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id WHERE th.id IS NULL;

4.2 右外连接：RIGHT JOIN

①：右外连接：右边的是主表，右表数据全部显示，左表显示符合 ON 后的条件的数据，不符合的用 NULL 代替

SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id;

②：右外特殊情况：

SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id WHERE st.teacher_id IS NULL;

4.3 全外连接： FULL OUT JOIN（OUT 可以省略）

全外连接：Mysql 不支持，可以使用 union 组合并去重实现

全外接查询：就是 左表独有的数据 加上 右表独有的数据

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id WHERE th.id IS NULL
UNION
SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id WHERE st.teacher_id IS NULL

4.4 全连接

全连接查询的是 左表所有的数据 加上 右表所有的数据 并去重。

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id
UNION
SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id

Mysql中的七种常用查询连接详解