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
);

---

五、补充知识点

1. 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)

2. 子查询使用规范

   • 非相关子查询优先使用，可读性和性能更好；
   • 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
);