在 SQLite 中，子查询（Subquery） 是一种嵌套在主查询中的 SELECT 语句，用于 动态生成数据，常用于 WHERE、FROM、SELECT 等子句。
一、子查询分类
| 类型 | 位置 | 返回 | 示例 |
|---|---|---|---|
| 标量子查询 | WHERE / SELECT | 单个值 | WHERE age > (SELECT AVG(age) ...) |
| 行子查询 | WHERE | 一行多列 | WHERE (id, name) = (SELECT ...) |
| 列子查询 | IN / EXISTS | 一列多行 | WHERE id IN (SELECT ...) |
| 表子查询 | FROM | 多行多列 | FROM (SELECT ...) AS t |
二、基本语法与示例
示例表
CREATE TABLE users (id INT, name TEXT, dept_id INT, salary INT);
CREATE TABLE departments (id INT, name TEXT);
INSERT INTO users VALUES
(1, 'Alice', 10, 6000),
(2, 'Bob', 20, 8000),
(3, 'Charlie', 10, 5500),
(4, 'David', 20, 9000);
INSERT INTO departments VALUES (10, 'HR'), (20, 'IT');三、常见用法详解
1. 标量子查询（返回一个值）
-- 查找薪水高于平均薪水的员工
SELECT name, salary
FROM users
WHERE salary > (SELECT AVG(salary) FROM users);输出：
Bob,David
2. 列子查询 + IN
-- 查找 HR 部门的所有员工
SELECT name FROM users
WHERE dept_id IN (
SELECT id FROM departments WHERE name = 'HR'
);输出：
Alice,Charlie
3. 列子查询 + EXISTS（性能更好）
-- 查找有员工的部门
SELECT name FROM departments d
WHERE EXISTS (
SELECT 1 FROM users u WHERE u.dept_id = d.id
);输出：
HR,IT
EXISTS一旦找到一行就返回TRUE，比IN更快
4. 表子查询（派生表）
-- 按部门统计平均薪水，再筛选高薪部门
SELECT dept_name, avg_salary
FROM (
SELECT d.name AS dept_name, AVG(u.salary) AS avg_salary
FROM users u
JOIN departments d ON u.dept_id = d.id
GROUP BY d.id
) AS t
WHERE avg_salary > 6000;输出：
IT | 8500
5. 相关子查询（Correlated Subquery）
-- 查找每个部门薪水最高的员工
SELECT u1.name, u1.salary, u1.dept_id
FROM users u1
WHERE u1.salary = (
SELECT MAX(u2.salary)
FROM users u2
WHERE u2.dept_id = u1.dept_id
);输出：
Alice | 6000 | 10,David | 9000 | 20外层每行执行一次内层子查询
四、子查询 vs JOIN 性能对比
| 场景 | 推荐 |
|---|---|
| 简单过滤 | IN / EXISTS |
| 多列关联 | JOIN 更快 |
| 聚合统计 | JOIN + GROUP BY |
| 动态表 | 表子查询（FROM） |
-- 慢（子查询）
SELECT name FROM users WHERE dept_id IN (SELECT id FROM departments WHERE name = 'IT');
-- 快（JOIN）
SELECT u.name FROM users u
JOIN departments d ON u.dept_id = d.id
WHERE d.name = 'IT';五、常见陷阱与注意事项
| 问题 | 说明 | 解决 |
|---|---|---|
| 多于一行返回 | 标量子查询不能返回多行 | 用 IN、ANY、ALL |
NULL 干扰 IN | IN (1, NULL) → 永远 FALSE | 用 COALESCE 或 EXISTS |
| 性能差 | 相关子查询重复执行 | 改写为 JOIN |
六、特殊操作符（与子查询搭配）
| 操作符 | 含义 | 示例 |
|---|---|---|
ANY / SOME | 满足任意一个 | salary > ANY (SELECT salary FROM ...) |
ALL | 满足全部 | salary > ALL (SELECT salary FROM ...) |
-- 薪水高于 HR 任意一人
SELECT name FROM users
WHERE salary > ANY (SELECT salary FROM users WHERE dept_id = 10);七、实战案例
1. 分页 + 总数（单 SQL）
SELECT
u.*,
(SELECT COUNT(*) FROM users) AS total_count
FROM users u
LIMIT 10 OFFSET 20;2. Top-N 每组
-- 每个部门薪水最高的前 2 人
WITH ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM users
)
SELECT name, salary, dept_id FROM ranked WHERE rn <= 2;虽然用了窗口函数，但逻辑类似子查询
3. 动态条件
-- 只在有 IT 部门时才查 IT 员工
SELECT name FROM users
WHERE
(SELECT COUNT(*) FROM departments WHERE name = 'IT') > 0
AND dept_id = (SELECT id FROM departments WHERE name = 'IT');八、调试技巧
-- 单独运行子查询验证
SELECT AVG(salary) FROM users;
-- 用 EXPLAIN QUERY PLAN 看执行计划
EXPLAIN QUERY PLAN
SELECT name FROM users WHERE salary > (SELECT AVG(salary) FROM users);九、总结：子查询使用指南
| 类型 | 推荐场景 | 替代方案 |
|---|---|---|
| 标量子查询 | 单个聚合值 | JOIN + GROUP BY |
| 列子查询 | IN / EXISTS 过滤 | JOIN 更快 |
| 相关子查询 | 行级比较 | 可用 JOIN + 窗口函数 |
| 表子查询 | 复杂中间结果 | CTE 更清晰 |
推荐：用 CTE 替代复杂子查询（更可读）
-- 原来嵌套子查询
SELECT * FROM (SELECT * FROM (SELECT ...)) ...
-- 推荐 CTE
WITH high_salary_users AS (
SELECT * FROM users WHERE salary > 7000
),
dept_stats AS (
SELECT dept_id, AVG(salary) AS avg_sal FROM users GROUP BY dept_id
)
SELECT * FROM high_salary_users h
JOIN dept_stats d ON h.dept_id = d.dept_id;快速参考表
| 写法 | 返回 | 位置 |
|---|---|---|
(SELECT 1) | 单个值 | WHERE, SELECT |
(SELECT id FROM ...) | 一列多行 | IN, EXISTS |
(SELECT id, name FROM ...) | 多行多列 | FROM |
EXISTS (SELECT 1 ...) | 布尔 | WHERE |
需要我帮你：
- 把一个嵌套子查询改写成 JOIN/CTE？
- 分析某个子查询的性能？
- 实现 Top-N 分组查询？
把你的 SQL 发我！
