【MYSQL】MYSQL 的学习教程（二）之 MYSQL 的七种连接
1. 概述
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。一个业务都会对应多张表，比如：学生和班级，起码两张表
①：连接分类（功能划分）：
- 交叉连接：笛卡尔积
- 内连接：等值连接、非等值连接、自连接
- 外连接：左(外)连接、左(外)连接特殊情况、右(外)连接、右(外)连接特殊情况、全连接、全外连接
②：连接分类（年代划分）：
- 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
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！