MySQL 子查询

 子查询基本概念

子查询是指嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的查询语句，必须放在圆括号内。子查询可以多层嵌套。

子查询执行顺序

 先执行最内层的子查询

 将子查询结果传递给外层查询

 最后执行外层查询

WHERE 子句中的子查询

1. IN/NOT IN 子查询

用于判断表达式是否在子查询结果集中。

示例1：查询选修了特定课程的学生

SELECT name FROM students 
WHERE course_id IN (SELECT id FROM courses WHERE course_name IN ('Java', 'Python'));

示例2：查询没有选修任何课程的学生

SELECT name FROM students
WHERE id NOT IN (SELECT student_id FROM student_courses);

2. 比较运算符子查询

使用 =, <>, >, <, >=, <= 等比较运算符。

示例3：查询成绩高于平均成绩的学生

SELECT student_name FROM scores
WHERE score > (SELECT AVG(score) FROM scores);

3. EXISTS/NOT EXISTS 子查询

检查子查询是否返回结果，不关心具体内容。

示例4：查询有选修课程的学生

SELECT name FROM students s
WHERE EXISTS (SELECT 1 FROM student_courses sc WHERE sc.student_id = s.id);

示例5：查询没有选修任何课程的学生

SELECT name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM student_courses sc WHERE sc.student_id = s.id);

FROM 子句中的子查询（派生表）

子查询可以作为临时表使用。

示例6：查询每门课程的平均成绩

SELECT c.course_name, avg_scores.avg_score
FROM courses c
JOIN (SELECT course_id, AVG(score) as avg_score FROM scores GROUP BY course_id) avg_scores
ON c.id = avg_scores.course_id;

SELECT 子句中的子查询（标量子查询）

示例7：查询每个学生的姓名和他们的成绩与平均成绩的差值

SELECT 
    student_name,
    score,
    score - (SELECT AVG(score) FROM scores) as diff_from_avg
FROM scores;

多列子查询

示例8：查询与特定学生年龄和性别相同的学生

SELECT name FROM students
WHERE (age, gender) = (SELECT age, gender FROM students WHERE name = '张三');

相关子查询

子查询引用外层查询的列。

示例9：查询成绩高于该课程平均成绩的学生

SELECT s.student_name, c.course_name, sc.score
FROM scores sc
JOIN students s ON sc.student_id = s.id
JOIN courses c ON sc.course_id = c.id
WHERE sc.score > (
    SELECT AVG(score) 
    FROM scores 
    WHERE course_id = sc.course_id
);

子查询性能优化建议

1. 对于 IN 子查询，当数据量大时考虑改用 JOIN

2. EXISTS 通常比 IN 性能更好，特别是子查询结果集大时

3. 避免在子查询中使用 SELECT *，只选择需要的列

4. 为子查询中的连接条件建立索引

子查询与连接的选择

 当需要从多个表获取数据但只关心一个表的记录时，使用子查询

 当需要从多个表获取数据并组合显示时，使用连接

 子查询通常更易读，但连接有时性能更好