30、SQL 子查询

1、子查询（Subquery）

是指嵌套在另一个 SQL 语句中的 SELECT 查询。

它可以在 SELECT、FROM、WHERE、HAVING 甚至 INSERT/UPDATE/DELETE 中使用，是实现复杂逻辑的核心工具。

• 主查询：外部的主要查询。

• 子查询：内部的嵌套查询。

特性	说明
子查询必须用括号 () 包裹	如 (SELECT ...)
可嵌套多层	但通常不超过 2~3 层（影响可读性和性能）
返回结果类型	可返回单值、单列多行、或多列多行

1）按返回结果分类：

• 标量子查询：返回单个值（一行一列），可直接当作字段使用。常用于 SELECT 列表或 WHERE 条件中

• 行子查询：返回单行多列，通常配合 IN、NOT IN、ANY、ALL 使用。

• 列子查询：返回单列多行，需用括号匹配。

• 表子查询：返回多行多列，用在 FROM 子句中，必须用 AS alias 起别名（如 dept_avg）。

如：两张表：employees 表

id	name	dept_id	salary
1	Alice	1	95000
2	Bob	2	45000
3	Carol	1	110000
4	Dave	3	60000

departments 表

id	name
1	HR
2	Engineering
3	Sales

例子1：标量子查询（Scalar Subquery），需求：显示每位员工的姓名、工资，以及公司平均工资

SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS company_avg_salary
FROM employees;
-- 先执行子查询：(SELECT AVG(salary) FROM employees) → 返回 77500
-- 外层查询对每一行都加上这个固定值

结果：

name	salary	company_avg_salary
Alice	95000	77500
Bob	45000	77500
Carol	110000	77500
Dave	60000	77500

例子 2：IN 子查询（列子查询），需求：查询在 HR 部门工作的员工姓名

SELECT name
FROM employees
WHERE dept_id IN (
    SELECT id 
    FROM departments 
    WHERE name = 'HR'
);
-- 子查询：SELECT id FROM departments WHERE name = 'HR' → 返回 (1)
-- 外层：WHERE dept_id IN (1)

结果：

name

Alice

Carol

2）按是否依赖外层查询分

(1) 非相关子查询（Non-correlated Subquery），

子查询独立执行，不引用外层表的任何字段。

执行顺序：

　　先执行子查询，然后将结果传递给外层查询，再执行外层查询。

如：

-- 查询工资高于公司平均工资的员工
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);  -- 不依赖外层

执行过程：

1. 先执行子查询：

   SELECT AVG(salary) FROM employees  → 返回 77500

2. 再执行外层查询（使用已知值）：

   SELECT name, salary FROM employees WHERE salary > 77500;

总共执行 1 次子查询，无论 employees 有 10 行还是 100 万行。

这就像先算出一个常量，再用它过滤数据。

(2) 相关子查询（Correlated Subquery）

子查询引用了外层查询的字段。它的执行依赖于外层查询。

对于外部查询的每一行，相关子查询都会执行一次。

执行顺序：

1. 首先执行外层查询，从 FROM 表中取出第一行数据。　
2. 拿着从外层查询取出的这一行的值，去执行子查询。
3. 子查询计算并返回一个结果。
4. 对外层查询的 WHERE 条件进行判断。
   • 条件为 True，外层查询的第一行数据将被放入结果集中。
   • 条件为 false，这行数据不会被放入结果集。
5. 重复循环，接着获取外层查询的第二行数据。，重复以上步骤，直到外层查询的所有行都被处理完毕。

如：employees 表，共有 5 行数据，分布在 3 个部门（dept_id = 1, 2, 3）。

id	name	dept_id	salary
1	Alice	1	95000
2	Bob	2	45000
3	Carol	1	110000
4	Dave	3	60000
5	Eve	1	80000

例子 1：找出每个部门工资最高的员工

SELECT e1.name, e1.dept_id, e1.salary
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id  -- 👈 引用了外层的 e1.dept_id！
);

解析：

1. 获取e1的第 1 行：name = Alice，dept_id=1, salary=95000
2. 代入子查询条件：

   SELECT MAX(e2.salary) 
   FROM employees e2 
   WHERE e2.dept_id = 1;  -- 因为 e1.dept_id = 1

   • 在 e2 中找 dept_id = 1 的记录：Alice(95k)、Carol(110k)、Eve(80k)
3. 子查询返回最大值 = 110000
4. 对外层的where条件进行判断：
   • e1.salary = 95000 是否等于 110000？ → ❌ 不相等

   • 结果：不返回 Alice

5. 重复步骤，获取e1的第 2 行：name = Bob,dept_id=2, salary=45000

   SELECT MAX(e2.salary) 
   FROM employees e2 
   WHERE e2.dept_id = 2;

   • dept_id=2 的员工只有 Bob（45000）
   • 最大值 = 45000
   • 判断：45000 = 45000？ → ✅ 相等
   • 结果：✅ 返回 Bob

   输出结果

   name	dept_id	salary
   Bob	2	45000
   Carol	1	110000
   Dave	3	60000