数据库的联合查询

        在数据库世界里，“数据拆分” 是常态 —— 为了避免冗余、保证数据一致性，我们会按照范式将信息分散到不同表中。比如学生信息存在 “学生表”，班级信息存在 “班级表”，成绩信息存在 “成绩表”。但实际业务中，我们往往需要 “完整视图”：想知道 “孙悟空在哪个班”，就得同时从学生表和班级表取数据；想统计 “唐三藏每门课的分数”，又得关联学生表、成绩表和课程表。这时候，联合查询就成了连接 “分散表” 与 “完整数据” 的关键桥梁。

一、为什么需要联合查询？

首先我们得明白：为什么数据不能都存在一个表里？举个例子，如果把学生、班级、成绩都塞到 “大杂烩表” 里，会出现什么问题？

• 冗余严重：一个班级有 50 个学生，“班级名称” 就要重复存储 50 次；
• 修改麻烦：如果班级名称改了（比如 “Java001 班” 改成 “Java2301 班”），50 条记录都要改，容易出错；
• 扩展性差：想加 “课程大纲” 信息，就得给所有学生记录加字段，不合理。

所以按照数据库范式，我们会把数据拆分成多个关联表，用 “主键 - 外键” 建立联系：

• 学生表（student）：存学生姓名、学号等，用class_id关联班级表；
• 班级表（class）：存班级名称，用id作为主键；
• 成绩表（score）：存分数，用student_id关联学生表，course_id关联课程表；
• 课程表（course）：存课程名称，用id作为主键。

但拆分后，查 “学生 + 班级”“成绩 + 课程” 这类组合信息，就需要联合查询把多个表 “拼” 起来。

二、联合查询的底层逻辑

1. 第一步：先算 “笛卡尔积”，生成临时大表

当你查询多个表时，MySQL 会先把这些表的所有记录 “两两组合”甚至多个组合的时候，生成一个临时表，这就是笛卡尔积。比如学生表有 2 条记录，班级表有 3 条记录，笛卡尔积就有 2×3=6 条记录：

但这里大部分记录是无效的（比如孙悟空的class_id是 1，却和班级表 id=2、3 的记录组合），所以需要第二步过滤。

2. 第二步：用 “连接条件” 过滤无效数据

我们需要告诉 MySQL：“学生表的 class_id 要和班级表的 id 相等，才是有效数据”。这个 “相等条件” 就是连接条件，用WHERE或ON子句指定。

加上条件后，无效记录被过滤，剩下的就是我们需要的关联数据：

⚠️ 注意：表越多、数据量越大，笛卡尔积临时表就越大，查询效率越低。所以实际开发中，尽量控制联合查询的表数量（一般不超过 3-4 张），并给关联字段加索引。

三、核心技能：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));

create table student (student_id int primary key auto_increment,name varchar(20), sno varchar(20), class_id int);

create table score (student_id int, course_id int ,score int);

为四张表插入需要的数据

insert into course (name) values ('Java'), ('C++'), ('MySQL'), 
('操作系统'), ('计算机⽹络'), ('数据结构');

insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');

insert into student (name, sno, class_id) values
('唐三藏', '100001', 1),
('孙悟空', '100002', 1),
('猪悟能', '100003', 1),
('沙悟净', '100004', 1),
('宋江', '200001', 2),
('武松', '200002', 2),
('李逹', '200003', 2),
('不想毕业', '200004', 2);

insert into score (score, student_id, course_id) 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);

示例：查询学生姓名为孙悟空的详细信息，包括学生个人信息和班级信息
1. 根据需求来确定需要查询而用到的表格，将两个甚至多个表格进行笛卡尔积操作

select *from student,class;

2. 确定连接条件，student表中的class_id与class表中id列的值相等（即筛选有效数据）
把笛卡尔积中出来的无效数据筛选出去

select* from student,class where student.class_id = class.class_id;

3.进一步加入查询条件，获得最终的结果

select* from student as s,class as c where s.class_id = c.class_id and s.name = '孙悟空';

1.内连接

语法：

select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;

下面的查询过程的所有示例与上个示例一样分为三步：

1.根据需求来确定需要查询而用到的表格，将两个甚至多个表格进行笛卡尔积操作

2. 确定连接条件，student表中的class_id与class表中id列的值相等（即筛选有效数据）
把笛卡尔积中出来的无效数据筛选出去

3.进一步加入查询条件，获得最终的结果

示例一：查询"唐三藏"同学的成绩

select s.name, sc.score from student s join score sc on sc.student_id =
s.student_id where s.name = '唐三藏';

示例二：查询所有同学的总成绩，及同学的个⼈信息

select s.name,sum(sc.score)
from student as s,score as sc 
where s.student_id = sc.student_id group by(s.student_id);

说明：Group by 对student.id进⾏分组，查询表列表中的student.name没有出现在Group by分组中，也没有包含在聚合函数中，这是因为SQL规定在Group by分组查询时，如果查询列表中的列没有出现在GROUP BY⼦句中，但这些列的值在每个分组内部是相同的，那么它们可以出现在查询结果中

2.外连接

• 外连接分为左外连接、右外连接和全外连接三种类型，MySQL不⽀持全外连接。

• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。

• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。

• 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹配的记录，则结果集中对应字段会显⽰为NULL。

-- 左外连接，表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;

示例1：查询没有参加考试的同学信息

第一步：左连接以JOIN左边的表为基准，左表显⽰全部记录，右表中没有匹配的记录⽤NULL填充

select s.student_id, s.name, s.sno, sc.* from student s left join score
sc on sc.student_id = s.student_id;

第二步：过滤参加了考试的同学

select s.student_id, s.name, s.sno, sc.* from student s left join score
sc on sc.student_id = s.student_id where sc.score is null;

示例二：查询没有学生的班级

第一步：右连接以JOIN右边的表为基准，右表显⽰全部记录，左表中没有匹配的记录⽤NULL填充

select *from  student as s right join class as c on c.class_id = s.class_id;

第二步：过滤有学⽣的班级

select c.* from student s RIGHT JOIN class c on c.class_id = s.class_id where
s.class_id is null;

3.自连接

自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者实现行与行之间的比较。在做表连接时为表起不同的别名。
语法：

//不为表指定别名
select * from score, score;
//指定别名
select * from score s1, score s2;

示例一：显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息

第一步：先查出JAVA和MySQL的课程Id，分别为1和3

select * from course where name = 'java' or name = 'MySQL';

第二步：查询JAVA成绩比MySQL成绩好的信息

select s1.* from score s1, score s2 where s1.student_id = s2.student_id
and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

4.表的连接的综合题

显示所有"MySQL"成绩比"JAVA"成绩高的学生信息和班级以及成绩信息

select stu.name as 姓名, c.name as 班级, s1.score as MySQL分数, s2.score as Java分数 from 
score s1,
score s2,
course c1,
course c2,
student stu,
class c
where s1.student_id = s2.student_id
and s1.course_id = c1.course_id
and s2.course_id = c2.course_id
and s1.score > s2.score
and stu.student_id = s1.student_id
and stu.class_id = c.class_id
and c1.name = 'MySQL'
and c2.name = 'Java';