12.生产环境差点被笛卡尔积干崩后，我整理了这份多表查询规范

目录

零、准备条件：

一、从一个简单的例子说起：学生表和班级表

二、连接条件：救星来了

三、实战练习：查询孙悟空的信息

四、内连接：更优雅的写法

五、内连接练习：从简单到复杂

练习1：查询唐三藏的成绩

​编辑

练习2：查询所有同学的总成绩

​编辑

练习3：查询所有同学每门课的成绩（三表连接）

六、外连接：处理"孤儿"数据

总结

原来你写的SQL查询这么危险！从笛卡尔积到内外连接，我用这篇彻底搞懂了多表查询！

今天整理学习笔记--MySQL多表查询的内容。原本以为查几个表就是简单的把表名一写就完事了，结果差点把数据库干崩溃， 今天就把这段心路历程分享出来，希望能帮到同样在学SQL的你！

本身需求中，就会出现带有关联关系的实体~~ => 查询的时候，通常要把多个实体的信息，放到一起进行查询~~

联合查询，也称为 “多表查询”

比单个表的查询更复杂 / 更低效~~

零、准备条件：

-- 0.1 切换或创建数据库 create database if not exists java117_2; use java117_2; -- 0.2 【清理旧环境】为了避免和之前课程的表冲突，先把之前创建的表删掉（如果存在） -- 注意：生产环境慎用 DROP，这里是教学演示，确保环境纯净 drop table if exists student; drop table if exists score; drop table if exists course; drop table if exists class; -- 0.3 【创建核心表结构】 -- 课程表：存储课程信息 create table course ( course_id int primary key auto_increment, name varchar(20) ); -- 班级表：存储班级信息 create table class ( class_id int primary key auto_increment, name varchar(20) ); -- 学生表：存储学生信息，包含外键 class_id 关联班级 create table student ( student_id int primary key auto_increment, name varchar(20), sno varchar(10), class_id int ); -- 成绩表：存储学生成绩，包含外键 student_id 和 course_id create table score ( student_id int, course_id int, score int ); -- 0.4 【批量插入初始化数据】 -- 插入课程数据 insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计算机网络'), ('数据结构'); -- 插入班级数据 insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班'); -- 插入学生数据（包含学号、姓名、所属班级ID） insert into student values (null, '唐三藏', '100001', 1), (null, '孙悟空', '100002', 1), (null, '猪八戒', '100003', 1), (null, '沙悟净', '100004', 1), (null, '宋江', '200001', 2), (null, '武松', '200002', 2), (null, '李逵', '200003', 2), (null, '不想毕业', '200004', 2); -- 插入成绩数据（student_id, course_id, score） insert into score (student_id, course_id, score) values (70.5, 1, 1), (98.5, 1, 3), (33, 1, 5), (98, 1, 6), (60, 2, 1), (59.5, 2, 5), (33, 3, 1), (68, 3, 3), (99, 3, 5), (67, 4, 1), (23, 4, 3), (56, 4, 5), (72, 4, 6), (81, 5, 1), (37, 5, 5), (56, 6, 2), (43, 6, 4), (79, 6, 6), (80, 7, 2), (92, 7, 6); -- 0.5 【验证数据是否创建成功】 select * from course; select * from class; select * from student; select * from score;

运行完上述代码后会得到以下图片的四张表格：

一、从一个简单的例子说起：学生表和班级表

一开始我遇到的问题是这样的：我有两个表，student表存学生信息（学号、姓名、班级ID），class表存班级信息（班级ID、班级名称）。现在我想查某个学生的详细信息，包括他所在的班级名称。

我天真地以为直接SELECT * FROM student, class;就行了，结果你猜怎么着？

笛卡尔积来了！​

就像图片里展示的那样，如果student表有4条数据，class表有3条数据，那么直接这样查，会得到 4×3=12 条数据！而且很多都是无效的，比如张三（学号1）被错误地配对了Java100、Java101、Java102三个班级。

这就好比你有一件上衣和三条裤子，结果你试穿的时候把这件上衣和每条裤子都拍了照，但其实你只想看它搭配其中一条的效果。

二、连接条件：救星来了

要避免这种无效的笛卡尔积，我们必须加上连接条件：student.class_id = class.class_id。

这样就能过滤掉那些无效的匹配，只保留学生和班级真正对应的记录。特别提醒了一个细节：二义性问题。当两个表都有class_id这个字段时，直接写WHERE class_id = class_id数据库会懵逼，不知道你指的是哪个表的class_id。所以必须加上前缀：student.class_id = class.class_id，或者使用别名来简化：

SELECT * FROM student AS s, class AS c WHERE s.class_id = c.class_id;

这样一来，查询就清晰多了！

此处使用"."这个操作符有点像JavaSE语法中的--成员访问操作符

那么我们可以这样理解：

数据库的表-->对象

数据库表中的列-->对象的属性

三、实战练习：查询孙悟空的信息

先明白：

在进行多表查询时, 必须存在某一列, 同时存在于两个表里~~

(如果不存在这样的列, 可以认为笛卡尔积, 没有意义)

需求来了：查询学生姓名为"孙悟空"的详细信息，包括学生个人信息和班级信息。

我这样思考：

1. 明确要从哪些表查：student表和class表

2. 先算笛卡尔积：SELECT * FROM student, class;（虽然知道数据会很多，但这是基础）

3. 增加连接条件去掉无效数据：WHERE s.class_id = c.class_id;

4. 再补充查询条件：AND s.name = '孙悟空';

最后，为了精简，我还可以只选择需要的列：

SELECT s.name, s.sno, s.class_id, c.name FROM student AS s, class AS c WHERE s.class_id = c.class_id AND s.name = '孙悟空';

看，结果就完美了，只有孙悟空那一条记录，带着他的班级信息！

四、内连接：更优雅的写法

其实上面那种写法，就是内连接（INNER JOIN）。MySQL提供了更直观的语法：

-- 方式一：传统写法（隐式内连接） SELECT * FROM student, class WHERE student.class_id = class.class_id; -- 方式二：标准内连接写法（显式内连接） SELECT * FROM student INNER JOIN class ON student.class_id = class.class_id;

两种方式效果一样，但第二种可读性更好，特别是当你需要连接多个表的时候。

PS：inner可以省略

那么：可以把第三点中的SQL语句写成下方的形式：

五、内连接练习：从简单到复杂

练习1：查询唐三藏的成绩

需要联合student表和score表。思路一样：找连接条件student.student_id = score.student_id，然后加上条件student.name = '唐三藏'。

SELECT student.name, score.score FROM student JOIN score ON student.student_id = score.student_id WHERE student.name = '唐三藏';

练习2：查询所有同学的总成绩

这次需要用到聚合函数SUM()了。先连接两个表，然后按学生分组求和：

SELECT student.name, SUM(score.score) AS total FROM student JOIN score ON student.student_id = score.student_id GROUP BY student.name;

看，数据就出来了！

再次复习一下思考流程：

1. 明确表

   学生表, 分数表

2. 笛卡尔积~~

3. 指定连接条件

4. 指定其他条件 先跳过~~ 此问题不涉及

5. 精简列,

6. 加入聚合操作~~

练习3：查询所有同学每门课的成绩（三表连接）

思考流程：

查询所有同学每门课的成绩，及同学的个人信息：

列出同学信息, 课程信息, 分数

1. 从三个表进行联合查询

   学生表, 课程表, 分数表

2. 笛卡尔积~

3. 指定连接条件

4. 指定进一步的筛选条件~~ （不涉及）

5. 针对列进行调整

这次要同时显示学生姓名、课程名称、分数，需要连接三个表：student、course、score。

笛卡尔积阶段：SELECT * FROM student, course, score;

然后指定连接条件：

• student.student_id = score.student_id

• course.course_id = score.course_id

最后精简列并加上别名：

SELECT student.name AS '学生姓名', course.name AS '课程名', score.score FROM student, course, score WHERE student.student_id = score.student_id AND course.course_id = score.course_id;

或者用显式JOIN语法，更清晰。

这里有个很重要的提醒：多表连接，尤其是笛卡尔积，是一个低效操作！​ 如果表很大，千万要谨慎，不要一次性联合太多的表。早期数据量小的时候可能感觉不到，但随着数据量增加，联合查询会越来越慢，甚至系统卡顿。为了优化，有时候甚至需要在分数表中冗余存储课程名字段，减少连接操作。

最后附上思路流程的代码：

# 查询每个同学每个课程的成绩，列出同学信息和课程信息。 select * from student, course, score; select * from student, course, score where student.student_id = score.student_id and course.course_id = score.course_id; select student.name as '学生姓名', course.name as '课程名', score.score from student, course, score where student.student_id = score.student_id and course.course_id = score.course_id; -- 也可以加别名 select student.name as '学生姓名', course.name as '课程名', score.score from student join score on student.student_id = score.student_id join course on course.course_id = score.course_id;

六、外连接：处理"孤儿"数据

-- === 六、外连接：处理“孤儿”数据 === -- 1. 准备测试环境 create database java117_2; use java117_2; -- 创建学生表和成绩表 create table student(student_id int, name varchar(20)); create table score(student_id int, score int); -- 插入初始数据：张三、李四、王五及其成绩 insert into student values (1, '张三'), (2, '李四'), (3, '王五'); insert into score values (1, 100), (2, 99), (3, 98); -- 验证初始数据（此时数据一一对应） select * from student; select * from score; -- 2. 模拟“孤儿”数据：故意修改王五的成绩，让其指向不存在的学生ID 4 -- 此时 score 表中 98分 这条记录就变成了“孤儿”数据（student_id=4 在 student 表中找不到） update score set student_id = 4 where score = 98; -- 3. 验证修改后的数据状态 select * from student; select * from score; -- 4. 查询对比：内连接 vs 左外连接 vs 右外连接 -- 内连接：只查两个表都能对应上的数据 -- 结果：只会查出张三、李四，王五的成绩因为ID对不上，被过滤掉了 select student.name, score.score from student inner join score on student.student_id = score.student_id; -- 左外连接：以左表（student）为主，保留左表所有数据 -- 结果：会查出张三、李四，以及王五（但王五的成绩显示为 NULL，因为他变成了孤儿） select student.name, score.score from student left join score on student.student_id = score.student_id; -- 右外连接：以右表（score）为主，保留右表所有数据 -- 结果：会查出张三、李四，以及那条 ID=4 的孤儿成绩（学生姓名显示为 NULL） select student.name, score.score from student right join score on student.student_id = score.student_id;

没有第2点的内连接和左/右外连接是完全一样的：如下图：

内连接只能查出两个表中都能对应上的数据。但如果有些学生还没有成绩呢？或者有些成绩没有对应的学生呢？

这时候就需要外连接（OUTER JOIN）了。

我创建了一个测试环境：student表有张三、李四、王五；score表有张三(100分)、李四(99分)、王五(98分)。一开始数据一一对应，内连接、左外连接、右外连接结果都一样。

然后我故意把成绩表改了一下：UPDATE score SET student_id = 4 WHERE score = 98;（让王五的成绩指向不存在的4号学生）。

这时候再查：

• 内连接：只返回有对应成绩的张三和李四，王五没了（因为成绩表里的王五现在对应不到学生）

• 左外连接：以左表（student）为主，王五依然会出现，成绩部分显示为NULL

• 右外连接：以右表（score）为主，4号学生的成绩也会出现，学生部分显示为NULL

看图里的韦恩图就明白了：

• 内连接：只取交集（中间重叠部分）

• 左外连接：取左边全部 + 交集

• 右外连接：取右边全部 + 交集

• 全外连接：取两边全部（MySQL不支持，Oracle支持）

特别提醒：内连接可以用FROM 表1, 表2，但左右外连接只能用JOIN语法，所以掌握JOIN ... ON语法非常重要！

总结

从笛卡尔积的灾难，到连接条件的救赎，再到内连接外连接的灵活运用，多表查询真的是SQL里最常用也最容易踩坑的地方。记住几点：

1. 先明确要从哪些表查

2. 笛卡尔积是所有多表查询的基础（虽然我们要避免无效数据）

3. 连接条件（ON或WHERE）必不可少，注意二义性

4. 大表慎连，考虑冗余字段优化

5. 内连接取交集，外连接保全集，根据业务需求选择

希望这篇笔记能帮你少走弯路，别像我一样一开始傻乎乎地直接查笛卡尔积了😅

有问题的评论区交流！一起进步！