{T}

子查询

子查询是嵌套在另一个查询中的查询。当无法直接从数据表中获取结果时，可以通过子查询从查询结果集中再次查询，得到最终想要的结果。

准备数据

player 表（球员表）

sql
CREATE TABLE player (
    player_id INT(11) NOT NULL AUTO_INCREMENT COMMENT '球员ID',
    team_id INT(11) NOT NULL COMMENT '球队ID',
    player_name VARCHAR(255) NOT NULL COMMENT '球员姓名',
    height FLOAT(3, 2) COMMENT '球员身高',
    PRIMARY KEY (player_id) USING BTREE,
    UNIQUE INDEX uk_player_name (player_name) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 示例数据
INSERT INTO player VALUES 
(10001, 1001, '韦恩-艾灵顿', 1.93),
(10002, 1001, '雷吉-杰克逊', 1.91),
(10003, 1001, '安德烈-德拉蒙德', 2.11),
(10004, 1001, '索恩-马克', 2.16),
(10021, 1002, '维克多-奥拉迪波', 1.93),
(10022, 1002, '博扬-博格达诺维奇', 2.03),
(10023, 1002, '多曼塔斯-萨博尼斯', 2.11);

team 表（球队表）

sql
CREATE TABLE team (
    team_id INT(11) NOT NULL COMMENT '球队ID',
    team_name VARCHAR(255) NOT NULL COMMENT '球队名称',
    PRIMARY KEY (team_id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 示例数据
INSERT INTO team VALUES 
(1001, '底特律活塞'),
(1002, '印第安纳步行者'),
(1003, '亚特兰大老鹰');

player_score 表（球员比赛成绩表）

sql
CREATE TABLE player_score (
    game_id INT(11) NOT NULL COMMENT '比赛ID',
    player_id INT(11) NOT NULL COMMENT '球员ID',
    is_first TINYINT(1) NOT NULL COMMENT '是否首发',
    playing_time INT(11) NOT NULL COMMENT '出场时间',
    score INT(11) NOT NULL COMMENT '得分',
    rebound INT(11) NOT NULL COMMENT '篮板',
    assist INT(11) NOT NULL COMMENT '助攻'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

height_grades 表（身高等级表）

sql
CREATE TABLE height_grades (
    height_level VARCHAR(255) NOT NULL COMMENT '身高等级',
    height_lowest FLOAT(3, 2) NOT NULL COMMENT '最低身高',
    height_highest FLOAT(3, 2) NOT NULL COMMENT '最高身高'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO height_grades VALUES 
('A', 2.00, 2.50),
('B', 1.90, 1.99),
('C', 1.80, 1.89),
('D', 1.60, 1.79);

子查询分类

非关联子查询

子查询只执行一次，结果作为主查询的条件：

sql
-- 查询身高最高的球员
SELECT player_name, height 
FROM player 
WHERE height = (SELECT MAX(height) FROM player);

执行过程：

  1. 子查询 SELECT MAX(height) FROM player 先执行，返回 2.16
  2. 主查询变为 SELECT player_name, height FROM player WHERE height = 2.16

关联子查询

子查询需要执行多次，每次依赖外部查询的值：

sql
-- 查询每个球队中身高大于该球队平均身高的球员
SELECT player_name, height, team_id 
FROM player AS a 
WHERE height > (
    SELECT AVG(height) 
    FROM player AS b 
    WHERE a.team_id = b.team_id
);

执行过程：

  1. 外部查询取出第一条记录，team_id = 1001
  2. 子查询计算 team_id = 1001 的平均身高
  3. 判断该球员身高是否大于平均值
  4. 重复以上步骤处理每一条记录

对比总结

类型执行次数是否依赖外部查询性能
非关联子查询1 次否较好
关联子查询N 次是较差

EXISTS 子查询

EXISTS 用于判断子查询是否返回结果，返回 True 或 False。

EXISTS 示例

sql
-- 查询出场过的球员
SELECT player_id, team_id, player_name 
FROM player 
WHERE EXISTS (
    SELECT player_id 
    FROM player_score 
    WHERE player.player_id = player_score.player_id
);

NOT EXISTS 示例

sql
-- 查询未出场过的球员
SELECT player_id, team_id, player_name 
FROM player 
WHERE NOT EXISTS (
    SELECT player_id 
    FROM player_score 
    WHERE player.player_id = player_score.player_id
);

EXISTS 工作原理

  1. 外部查询的每一行都会执行一次子查询
  2. 子查询返回任何行，EXISTS 就返回 True
  3. 子查询不返回任何行，EXISTS 就返回 False

集合比较子查询

IN 子查询

判断值是否在子查询结果集中：

sql
-- 查询出场过的球员（使用 IN）
SELECT player_id, team_id, player_name 
FROM player 
WHERE player_id IN (
    SELECT player_id 
    FROM player_score
);

ANY 子查询

与子查询返回的任意一个值比较：

sql
-- 查询比步行者任意球员身高都高的球员
SELECT player_id, player_name, height 
FROM player 
WHERE height > ANY (
    SELECT height 
    FROM player 
    WHERE team_id = 1002
);
-- 等价于：height > (SELECT MIN(height) FROM player WHERE team_id = 1002)

ALL 子查询

与子查询返回的所有值比较：

sql
-- 查询比步行者所有球员身高都高的球员
SELECT player_id, player_name, height 
FROM player 
WHERE height > ALL (
    SELECT height 
    FROM player 
    WHERE team_id = 1002
);
-- 等价于：height > (SELECT MAX(height) FROM player WHERE team_id = 1002)

SOME 子查询

SOME 是 ANY 的别名，作用相同：

sql
SELECT player_id, player_name, height 
FROM player 
WHERE height > SOME (
    SELECT height FROM player WHERE team_id = 1002
);

集合比较总结

操作符含义等价形式
IN在集合中= ANY
NOT IN不在集合中<> ALL
> ANY大于任意一个> (MIN(...))
> ALL大于所有> (MAX(...))
< ANY小于任意一个< (MAX(...))
< ALL小于所有< (MIN(...))

子查询作为计算字段

子查询可以作为 SELECT 语句中的计算字段：

sql
-- 查询每个球队的球员数量
SELECT 
    team_name, 
    (SELECT COUNT(*) FROM player WHERE player.team_id = team.team_id) AS player_num 
FROM team;

结果：

team_nameplayer_num
底特律活塞4
印第安纳步行者3
亚特兰大老鹰0

IN vs EXISTS 选择

选择原则

sql
-- IN 子查询
SELECT * FROM A WHERE cc IN (SELECT cc FROM B);

-- EXISTS 子查询
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc);
场景推荐原因
表 A 大，表 B 小INB 表索引有效
表 A 小，表 B 大EXISTSA 表索引有效
子查询结果集小IN直接匹配
子查询结果集大EXISTS逐行判断

性能分析

sql
-- 场景：player 表大，player_score 表小
-- 推荐 IN
SELECT player_name FROM player 
WHERE player_id IN (SELECT player_id FROM player_score);

-- 场景：player 表小，player_score 表大
-- 推荐 EXISTS
SELECT player_name FROM player p
WHERE EXISTS (
    SELECT 1 FROM player_score ps 
    WHERE ps.player_id = p.player_id
);

子查询位置

WHERE 子句

sql
SELECT * FROM player 
WHERE height > (SELECT AVG(height) FROM player);

HAVING 子句

sql
SELECT team_id, AVG(height) AS avg_height
FROM player
GROUP BY team_id
HAVING AVG(height) > (
    SELECT AVG(height) FROM player
);

FROM 子句（派生表）

sql
-- 子查询作为临时表
SELECT t.team_name, p.avg_height
FROM team t
JOIN (
    SELECT team_id, AVG(height) AS avg_height
    FROM player
    GROUP BY team_id
) p ON t.team_id = p.team_id;

SELECT 子句

sql
SELECT 
    player_name,
    height,
    (SELECT AVG(height) FROM player) AS overall_avg,
    height - (SELECT AVG(height) FROM player) AS diff
FROM player;

子查询优化建议

1. 使用连接替代子查询

sql
-- 子查询方式
SELECT player_name 
FROM player 
WHERE player_id IN (SELECT player_id FROM player_score);

-- 连接方式（通常更快）
SELECT DISTINCT p.player_name 
FROM player p
INNER JOIN player_score ps ON p.player_id = ps.player_id;

2. 避免相关子查询

sql
-- 差：相关子查询
SELECT player_name, height,
    (SELECT team_name FROM team WHERE team.team_id = player.team_id) AS team_name
FROM player;

-- 好：使用连接
SELECT p.player_name, p.height, t.team_name
FROM player p
LEFT JOIN team t ON p.team_id = t.team_id;

3. 使用 EXISTS 替代 IN

sql
-- 当子查询结果集很大时
-- 差
SELECT * FROM player 
WHERE player_id IN (SELECT player_id FROM player_score WHERE score > 20);

-- 好
SELECT * FROM player p
WHERE EXISTS (
    SELECT 1 FROM player_score ps 
    WHERE ps.player_id = p.player_id AND ps.score > 20
);

4. 限制子查询返回的列

sql
-- 好：只返回需要的列
SELECT player_name FROM player
WHERE player_id IN (SELECT player_id FROM player_score);

-- 差：返回所有列
SELECT player_name FROM player
WHERE player_id IN (SELECT * FROM player_score);

实战案例

案例 1：查找各部门薪资最高的员工

sql
SELECT e.name, e.salary, e.department_id
FROM employee e
WHERE salary = (
    SELECT MAX(salary) 
    FROM employee e2 
    WHERE e2.department_id = e.department_id
);

案例 2：查找有重复记录的数据

sql
SELECT * FROM player
WHERE player_name IN (
    SELECT player_name 
    FROM player 
    GROUP BY player_name 
    HAVING COUNT(*) > 1
);

案例 3：分页统计

sql
-- 统计每个球队的球员数，并分页
SELECT * FROM (
    SELECT 
        t.team_name,
        (SELECT COUNT(*) FROM player p WHERE p.team_id = t.team_id) AS player_count
    FROM team t
) AS team_stats
ORDER BY player_count DESC
LIMIT 5 OFFSET 0;

总结

子查询类型

类型说明使用场景
非关联子查询独立执行一次单值比较
关联子查询依赖外部查询逐行判断
EXISTS判断是否存在存在性检查
IN集合成员判断离散值匹配
ANY/ALL集合比较范围判断

最佳实践

  1. 优先使用连接：连接通常比子查询性能更好
  2. 合理选择 IN/EXISTS：根据表大小选择
  3. 避免多层嵌套：复杂查询考虑使用 CTE 或临时表
  4. 注意 NULL 值：NOT IN 遇到 NULL 会返回空结果
  5. 使用索引：确保连接字段有索引