子查询
子查询
想象你去超市买东西，你问店员："你们店里最贵的商品是什么？"店员查了一下说："是MacBook Pro。"然后你接着问："那买过这个商品的客户都有谁？"——你刚才做的事情就是子查询：先用一个问题的答案，去问下一个问题。SQL中的子查询也是同样的逻辑：把一个查询的结果，作为另一个查询的条件或数据源。
1. 核心概念
什么是子查询？
子查询（Subquery）是嵌套在另一个SQL语句内部的查询。外层查询叫主查询，内层查询叫子查询。子查询先执行，结果交给主查询使用。
SQL
-- 子查询：找出薪资最高的员工
SELECT name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
执行顺序：先执行SELECT MAX(salary) FROM employees得到20000，再执行外层查询找出salary = 20000的员工。
子查询的分类
按子查询出现的位置，可以分为三大类：
| 类型 | 位置 | 用途 | 返回值 |
|---|---|---|---|
| WHERE子查询 | WHERE条件中 | 过滤数据 | 单值或列表 |
| FROM子查询（派生表） | FROM子句中 | 作为临时表 | 结果集 |
| SELECT子查询（标量子查询） | SELECT列列表中 | 作为计算列 | 单个值 |
WHERE 中的子查询
最常见的用法。子查询出现在WHERE条件中，用于动态确定过滤条件。
SQL
-- 找出薪资高于平均薪资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
子查询可以返回：
- 单个值（标量子查询）：配合
=、>、<等比较运算符 - 多个值（多行子查询）：配合
IN、ANY、ALL等运算符
SQL
-- 返回多个值：用IN匹配
SELECT name, salary
FROM employees
WHERE department_id IN (
SELECT id FROM departments WHERE location = '北京'
);
FROM 中的子查询（派生表）
子查询出现在FROM子句中，相当于创建一个临时表（也叫派生表）。必须给子查询起别名。
SQL
-- 先算出每个部门的平均薪资，再从中筛选
SELECT dept_name, avg_salary
FROM (
SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.name
) AS dept_avg
WHERE avg_salary > 12000;
💡 提示：FROM子查询必须有别名（如上面的
AS dept_avg），否则SQL会报错。
SELECT 中的子查询（标量子查询）
子查询出现在SELECT列列表中，作为计算列。每次主查询处理一行，子查询就执行一次。
SQL
-- 显示每个员工的薪资和公司平均薪资
SELECT name, salary,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;
输出：
TEXT
name salary avg_salary
------ -------- ----------
张三 15000.00 14000.00
李四 18000.00 14000.00
王五 12000.00 14000.00
赵六 13000.00 14000.00
钱七 20000.00 14000.00
孙八 11000.00 14000.00
周九 9000.00 14000.00
吴十 14000.00 14000.00
💡 提示：标量子查询必须只返回一行一列，否则会报错。它适合用来添加"汇总信息"列。
EXISTS 和 NOT EXISTS
EXISTS检查子查询是否返回至少一行数据。它不关心返回什么值，只关心"有没有结果"。
SQL
-- 找出有员工的部门
SELECT d.name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.id
);
SQL
-- 找出没有员工的部门
SELECT d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1 FROM employees e WHERE e.department_id = d.id
);
💡 提示：
EXISTS中的SELECT 1是惯例写法，因为EXISTS只关心"有没有行"，不关心列的值。换成SELECT *或SELECT NULL效果一样。
子查询 vs JOIN 性能对比
| 对比项 | 子查询 | JOIN |
|---|---|---|
| 可读性 | 更接近自然语言思维 | 需要理解连接逻辑 |
| 性能 | 简单场景差异不大；相关子查询可能较慢 | 通常更优，有专门的优化器 |
| 灵活性 | 可以用聚合函数结果做条件 | 需要配合GROUP BY |
| 推荐场景 | "找最大的"、"找不存在的" | "关联展示"、"多表拼接" |
💡 提示：现代数据库（如PostgreSQL、MySQL 8.0）的优化器已经很智能，子查询和JOIN的性能差异在很多场景下可以忽略。建议优先选择可读性更好的写法，遇到性能问题时再用
EXPLAIN分析优化。
2. 基本语法/用法
WHERE 子查询语法
SQL
-- 标量子查询（返回单值）
SELECT 列名 FROM 表名
WHERE 列名 比较运算符 (SELECT 聚合函数 FROM 表名);
-- 多行子查询（返回列表）
SELECT 列名 FROM 表名
WHERE 列名 IN (SELECT 列名 FROM 表名 WHERE 条件);
FROM 子查询语法
SQL
SELECT 列名
FROM (SELECT ... FROM ... WHERE ...) AS 别名
WHERE 条件;
💡 提示：FROM子查询的别名是必须的，不同数据库的要求不同，但加上别名是最安全的写法。
SELECT 标量子查询语法
SQL
SELECT 列1, 列2,
(SELECT 聚合函数 FROM 表名 WHERE 条件) AS 别名
FROM 表名;
💡 提示：标量子查询中如果需要引用外层查询的列（相关子查询），可以直接在外层查询中使用表别名。
EXISTS 语法
SQL
SELECT 列名 FROM 表A a
WHERE EXISTS (SELECT 1 FROM 表B b WHERE b.外键 = a.主键);
💡 提示：EXISTS是"相关子查询"——子查询引用了外层查询的列（如
a.id），因此子查询对外层的每一行都会执行一次。
💡 提示：对于大数据集，EXISTS通常比IN更高效，因为EXISTS找到第一个匹配就停止，而IN需要返回所有结果。
示例：查找薪资高于平均值的员工（难度⭐）
SQL
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
输出：
TEXT
name salary
------ --------
钱七 20000.00
李四 18000.00
张三 15000.00
吴十 14000.00
先算出平均薪资（约14000），再筛选高于平均值的员工。
示例：查找每个部门薪资最高的员工（难度⭐⭐）
SQL
SELECT e.name, d.name AS department, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary = (
SELECT MAX(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);
输出：
TEXT
name department salary
------ ---------- --------
钱七 技术部 20000.00
王五 市场部 12000.00
吴十 财务部 14000.00
💡 关键：这是一个相关子查询——子查询中的
e.department_id引用了外层查询的当前行。子查询对外层每一行执行一次，找出该员工所在部门的最高薪资，然后判断当前员工是否就是最高薪资者。
示例：用派生表统计各部门员工薪资水平（难度⭐⭐⭐）
SQL
SELECT dept_name, emp_count, avg_salary,
CASE
WHEN avg_salary >= 15000 THEN '高薪部门'
WHEN avg_salary >= 12000 THEN '中等薪资'
ELSE '待提升'
END AS level
FROM (
SELECT d.name AS dept_name,
COUNT(e.id) AS emp_count,
AVG(e.salary) AS avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name
) AS dept_stats
WHERE emp_count > 0
ORDER BY avg_salary DESC;
输出：
TEXT
dept_name emp_count avg_salary level
--------- --------- ---------- ----------
技术部 3 17666.67 高薪部门
财务部 2 13500.00 中等薪资
市场部 2 11500.00 待提升
💡 思路：FROM子查询先计算每个部门的员工数和平均薪资，外层查询再对这些统计数据进行分类和过滤。这种"先聚合、再分析"的模式在报表开发中非常常见。
3. 常见应用场景
场景一：查找从未被订购的商品
SQL
SELECT p.name, p.category, p.price
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.product_id = p.id
);
输出（取决于数据）：
TEXT
name category price
-------- -------- -------
Mac Mini 电脑 4499.00
💡 对比：同样的需求用LEFT JOIN实现：
SQL
SELECT p.name, p.category, p.price
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.id IS NULL;
两种写法性能接近，选择可读性更好的即可。
场景二：查找下单金额超过平均订单金额的客户
SQL
SELECT customer_name, total_spent
FROM (
SELECT o.customer_name,
SUM(o.quantity * p.price) AS total_spent
FROM orders o
JOIN products p ON o.product_id = p.id
GROUP BY o.customer_name
) AS customer_totals
WHERE total_spent > (
SELECT AVG(o.quantity * p.price)
FROM orders o
JOIN products p ON o.product_id = p.id
);
输出：
TEXT
customer_name total_spent
------------- -----------
小李 14397.00
小刚 17998.00
小王 11998.00
💡 思路：FROM子查询计算每个客户的总消费，WHERE子查询计算所有订单的平均金额，外层查询筛选出高于平均的客户。
❓ 常见问题
Q：子查询可以嵌套多少层？ A： 理论上没有限制，但实际开发中建议不超过3层。层数太多说明查询逻辑过于复杂，应该考虑拆分成多个步骤或使用JOIN重写。
Q：IN和EXISTS该选哪个？ A： 当子查询结果集较小且外层表较大时，IN更高效；当外层表较小且子查询表较大时，EXISTS更高效（因为EXISTS找到一个匹配就停止）。简单场景差异不大，复杂场景建议用EXPLAIN对比。
Q：标量子查询返回多行会怎样？ A： 数据库会报错。标量子查询必须只返回一行一列。如果不确定返回行数，用
LIMIT 1或聚合函数（如MAX、MIN）确保只返回单值。
Q：相关子查询和非相关子查询有什么区别？ A： 非相关子查询独立执行一次（如"查平均薪资"），结果被主查询使用。相关子查询引用了外层查询的列，对外层每一行执行一次（如"查每个部门的最高薪资"）。相关子查询在数据量大时可能较慢，需要注意性能。
📖 小节
- 子查询是嵌套在另一个SQL语句中的查询，先执行内层，结果交给外层使用
- WHERE子查询：用于动态过滤，配合
IN、=、>等运算符 - FROM子查询（派生表）：作为临时数据源，必须起别名，适合"先聚合再分析"
- SELECT标量子查询：作为计算列，每次外层查询处理一行时执行一次
- EXISTS/NOT EXISTS：只关心子查询有没有结果，适合"查找存在/不存在关联"的场景
- 子查询 vs JOIN：优先考虑可读性，性能问题用EXPLAIN分析
📝 作业
作业1（⭐）：用子查询找出"技术部"的所有员工（提示：先用子查询查出技术部的id）。
作业2（⭐⭐）：用FROM子查询（派生表）计算每个客户的订单数量和总消费金额，然后筛选出总消费超过5000的客户。
作业3（⭐⭐⭐）：用EXISTS找出"所有产品都被订购过的客户"——即该客户不存在"未订购的产品"（提示：双重NOT EXISTS逻辑，或用其他方式实现）。
下一课
👉 10-set-operations - 集合操作：学习UNION、UNION ALL、INTERSECT和EXCEPT，掌握多查询结果的集合运算！
