MySQL 子查询（多行）
MySQL 子查询（多行）学习笔记
一、子查询基础概念
子查询（也叫内查询）是嵌套在 SELECT、FROM、WHERE、HAVING 等语句中的查询，用于分步完成复杂筛选。
- 按返回结果分为：单行子查询（返回1行）、多行子查询（返回多行）
- 按执行方式分为：非相关子查询（独立执行）、相关子查询（依赖外层查询）
二、多行子查询与比较运算符
1. 多行比较运算符说明
| 运算符 | 含义 | 使用场景 |
|---|---|---|
IN |
等于列表中的任意一个值 | 匹配子查询结果中存在的值 |
ANY |
与子查询返回的某一个值比较 | 需配合 > < = 等比较符 |
ALL |
与子查询返回的所有值比较 | 需配合 > < = 等比较符 |
SOME |
ANY 的别名，作用完全相同 |
不常用，一般用 ANY |
2. 多行子查询示例
示例1：查询各部门最低工资的员工
-- 子查询：先查出每个部门的最低工资（多行结果）
-- 外层查询：匹配工资在这些最低工资列表中的员工
SELECT employee_id, last_name
FROM employees
WHERE salary IN (SELECT MIN(salary)FROM employeesGROUP BY department_id
);
示例2：ANY 运算符示例
需求：返回非 IT_PROG 岗位中，工资比 IT_PROG 任一员工低的员工信息
-- 子查询：先查出所有 IT_PROG 岗位的工资列表
-- 外层查询：筛选工资 < 列表中任意一个值（即比最低工资低）
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG'
);
说明：
salary < ANY (...)等价于salary < 列表中的最大值，只要比列表中任意一个值小就满足条件。
示例3：ALL 运算符示例
需求：返回非 IT_PROG 岗位中，工资比所有 IT_PROG 员工都低的员工信息
-- 子查询：先查出所有 IT_PROG 岗位的工资列表
-- 外层查询：筛选工资 < 列表中所有值（即比最低工资还低）
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG'
);
说明：
salary < ALL (...)等价于salary < 列表中的最小值，必须比列表中所有值都小才满足条件。
三、聚合函数嵌套问题
1. 错误示例：聚合函数不能直接嵌套
-- ❌ 错误写法：MySQL 不支持聚合函数直接嵌套
SELECT MIN(AVG(salary))
FROM employees
GROUP BY department_id;
错误原因：MySQL 中聚合函数不能直接嵌套使用，需要通过子查询实现。
2. 正确实现：查询平均工资最低的部门ID
方式1：子查询 + 临时表
-- 第一步：先查询每个部门的平均工资，作为临时表
-- 第二步：在临时表中取最小平均工资
-- 第三步：筛选平均工资等于最小值的部门
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(avg_sal)FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) t_dept_avg_sal
);
方式2：ALL 运算符实现
-- 利用 ALL 运算符，筛选平均工资 <= 所有部门平均工资的部门（即最小值）
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id
);
四、子查询中的空值问题
问题示例：NOT IN 与子查询空值陷阱
-- ❌ 问题写法：子查询返回包含 NULL 的列表时，NOT IN 结果为空
SELECT last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_idFROM employees-- 缺少 WHERE manager_id IS NOT NULL，子查询结果包含 NULL
);
问题原因：NOT IN (列表) 本质是 != 值1 AND != 值2 AND ...，如果列表中存在 NULL，则条件结果为 UNKNOWN，导致整个查询返回空。
正确写法：过滤子查询中的 NULL
-- ✅ 正确写法：在子查询中排除 NULL
SELECT last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_idFROM employeesWHERE manager_id IS NOT NULL
);
五、补充知识点
-
ANY与ALL的等价关系salary > ANY (list)→salary > MIN(list)salary < ANY (list)→salary < MAX(list)salary > ALL (list)→salary > MAX(list)salary < ALL (list)→salary < MIN(list)
-
子查询使用规范
- 非相关子查询优先使用，可读性和性能更好；
IN子查询中，子查询结果尽量避免 NULL；- 复杂嵌套子查询可拆分为临时表，提升可读性和性能。
六、完整代码汇总
-- 1. 各部门最低工资的员工
SELECT employee_id, last_name
FROM employees
WHERE salary IN (SELECT MIN(salary)FROM employeesGROUP BY department_id
);-- 2. ANY：比 IT_PROG 任一工资低的非 IT_PROG 员工
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG'
);-- 3. ALL：比 IT_PROG 所有工资都低的非 IT_PROG 员工
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL (SELECT salaryFROM employeesWHERE job_id = 'IT_PROG'
);-- 4. 平均工资最低的部门（方式1：子查询+临时表）
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(avg_sal)FROM (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id) t_dept_avg_sal
);-- 5. 平均工资最低的部门（方式2：ALL运算符）
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (SELECT AVG(salary) avg_salFROM employeesGROUP BY department_id
);-- 6. 无下属的员工（NOT IN 过滤 NULL）
SELECT last_name
FROM employees
WHERE employee_id NOT IN (SELECT manager_idFROM employeesWHERE manager_id IS NOT NULL
);