在数据库设计中，多对多关系指的是两张表之间的记录可以相互关联多条，比如学生和课程的关系，一个学生可以选多门课程，一门课程也可以被多个学生选择。这种关系无法直接通过两张表实现，需要额外创建一张中间表来存储双方的关联信息。
多对多关系的基本表结构
以学生和课程为例，三张表的结构如下：
- 学生表
student：存储学生基本信息，主键为student_id - 课程表
course：存储课程基本信息，主键为course_id - 选课中间表
student_course：存储学生和课程的关联关系，包含student_id和course_id两个外键
创建这三张表的SQL语句如下：
-- 创建学生表
CREATE TABLE student (
student_id INT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(50) NOT NULL,
age INT
);
-- 创建课程表
CREATE TABLE course (
course_id INT PRIMARY KEY AUTO_INCREMENT,
course_name VARCHAR(50) NOT NULL,
teacher VARCHAR(50)
);
-- 创建选课中间表
CREATE TABLE student_course (
student_id INT,
course_id INT,
score DECIMAL(5,2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES student(student_id),
FOREIGN KEY (course_id) REFERENCES course(course_id)
);
基础多对多JOIN查询写法
如果需要查询所有学生及其选择的课程信息，需要将三张表依次关联，先关联学生表和中间表，再关联中间表和课程表。
SELECT
s.student_id,
s.student_name,
c.course_id,
c.course_name,
sc.score
FROM student s
JOIN student_course sc ON s.student_id = sc.student_id
JOIN course c ON sc.course_id = c.course_id;
上述查询中，JOIN默认是INNER JOIN，只会返回存在关联关系的学生和课程记录，如果学生没有选课或者课程没有被选择，对应的记录不会出现在结果中。
添加过滤条件的多对多查询
查询指定学生的选课信息
如果只需要查询某个学生的选课情况，可以在上述查询基础上添加WHERE条件过滤学生ID。
SELECT
s.student_name,
c.course_name,
sc.score
FROM student s
JOIN student_course sc ON s.student_id = sc.student_id
JOIN course c ON sc.course_id = c.course_id
WHERE s.student_id = 1;
查询选择了指定课程的学生
同理，如果需要查询选择了某门课程的所有学生，可以过滤课程ID。
SELECT
s.student_name,
s.age,
c.course_name
FROM student s
JOIN student_course sc ON s.student_id = sc.student_id
JOIN course c ON sc.course_id = c.course_id
WHERE c.course_id = 3;
左连接查询未选课的学生
如果需要查询所有学生，包括没有选课的学生，可以使用LEFT JOIN，让学生表作为左表，即使中间表没有对应的关联记录，学生的信息也会保留，课程相关字段会显示为NULL。
SELECT
s.student_id,
s.student_name,
c.course_name
FROM student s
LEFT JOIN student_course sc ON s.student_id = sc.student_id
LEFT JOIN course c ON sc.course_id = c.course_id;
去重查询场景
如果需要查询所有选择了课程的学生姓名，不关心具体选了哪些课，可以使用DISTINCT去重，避免同一个学生因为选多门课出现多次。
SELECT DISTINCT
s.student_id,
s.student_name
FROM student s
JOIN student_course sc ON s.student_id = sc.student_id;
注意事项
- 多对多查询必须关联中间表，不能直接让两张主表做JOIN，否则会出现笛卡尔积，导致结果错误
- 如果中间表有额外字段比如选课成绩，需要在查询中正确关联这些字段
- 根据实际需求选择
INNER JOIN或者LEFT JOIN，避免遗漏需要的记录