引言
子查询（Subquery）是嵌套在其他查询中的SELECT语句，用于实现复杂的查询逻辑。
一、子查询分类
1.1 按返回结果分类
- 标量子查询：返回单个值（1行1列）
- 列子查询：返回一列多行
- 行子查询：返回一行多列
- 表子查询：返回多行多列
1.2 按执行方式分类
- 非相关子查询：独立执行，与外层查询无关
- 相关子查询：依赖外层查询，每行都执行一次
二、标量子查询
返回单个值，可用于比较运算。
-- 查询价格高于平均价格的商品
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
-- 查询销量最高的商品
SELECT name, sales
FROM products
WHERE sales = (SELECT MAX(sales) FROM products);
三、列子查询
返回一列多行，通常配合IN、ANY、ALL使用。
3.1 IN / NOT IN
-- 查询有订单的用户
SELECT name FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- 查询没有订单的用户
SELECT name FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
⚠️ NOT IN的陷阱：子查询包含NULL会返回空结果。
3.2 ANY / SOME
-- 价格高于任意一个手机的商品
SELECT name, price FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = '手机');
-- 等价于
WHERE price > (SELECT MIN(price) FROM products WHERE category = '手机');
3.3 ALL
-- 价格高于所有手机的商品
SELECT name, price FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = '手机');
-- 等价于
WHERE price > (SELECT MAX(price) FROM products WHERE category = '手机');
四、行子查询
返回一行多列，用于多列比较。
-- 查询与商品A相同价格和库存的商品
SELECT name FROM products
WHERE (price, stock) = (SELECT price, stock FROM products WHERE name = '商品A');
五、表子查询
返回多行多列，通常用于FROM子句。
-- 查询每个用户的订单统计
SELECT u.name, t.order_count, t.total_amount
FROM users u
INNER JOIN (
SELECT user_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
) t ON u.id = t.user_id;
六、相关子查询 vs 非相关子查询
6.1 非相关子查询
子查询独立执行，结果用于外层查询。
-- 非相关子查询：只执行一次
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);
6.2 相关子查询
子查询依赖外层查询，每行都执行一次。
-- 相关子查询：查询每个用户的最新订单
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at = (
SELECT MAX(created_at)
FROM orders
WHERE user_id = u.id -- 依赖外层的u.id
);
性能特点：
- 非相关子查询：执行1次，性能好
- 相关子查询：执行N次（N为外层行数），性能差
七、EXISTS vs IN
7.1 EXISTS
检查子查询是否返回结果，返回TRUE/FALSE。
-- 查询有订单的用户
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);
7.2 NOT EXISTS
-- 查询没有订单的用户
SELECT name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);
7.3 EXISTS vs IN 性能对比
-- IN：适合子查询结果集小的情况
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders);
-- EXISTS：适合子查询结果集大的情况
SELECT name FROM users u
WHERE EXISTS (SELECT 1 FROM orders WHERE user_id = u.id);
选择建议：
- 小表驱动大表：用IN
- 大表驱动小表：用EXISTS
- 子查询有NULL：用EXISTS更安全
八、子查询优化
8.1 用JOIN替代子查询
-- ❌ 差：相关子查询
SELECT u.name,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS cnt
FROM users u;
-- ✅ 好：用LEFT JOIN
SELECT u.name, COUNT(o.id) AS cnt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
8.2 避免NOT IN与NULL
-- ❌ 危险
WHERE id NOT IN (SELECT user_id FROM orders);
-- ✅ 安全
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);
-- 或使用NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE user_id = users.id);
8.3 使用索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_category ON products(category);
九、实战案例
案例1：查询高于类别平均价的商品
SELECT name, category, price
FROM products p
WHERE price > (
SELECT AVG(price)
FROM products
WHERE category = p.category
);
案例2：查询每个用户的最大订单
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount = (
SELECT MAX(amount)
FROM orders
WHERE user_id = u.id
);
案例3：查询从未购买过的商品
SELECT name FROM products
WHERE id NOT IN (
SELECT DISTINCT product_id
FROM orders
WHERE product_id IS NOT NULL
);
十、总结
核心要点
- 标量子查询：返回单值，用于比较
- 列子查询：配合IN/ANY/ALL使用
- 表子查询：用于FROM子句
- EXISTS vs IN：EXISTS更安全，处理NULL更好
- 优化：尽量用JOIN替代子查询，避免相关子查询
记忆口诀
子查询嵌套在里面，四种类型要分清，
标量一值列多行，行子查询多列成。
IN配列子查询用，EXISTS检查有没有，
相关子查询性能差，能用JOIN就不用它。
本文字数：约2,800字 难度等级：⭐⭐⭐（SQL进阶）