MySQL的子查询

一、前言

• MySQL 子查询是指嵌套在其他 SQL 语句（如 SELECT、WHERE、FROM 等）内部的查询。
• 用于辅助主查询完成复杂的数据筛选或计算。

---

二、子查询分类

• 标量子查询
  • 描述：返回 单行单列（一个值），常用于比较运算（如 =、>、< 等）。
  • 示例：SELECT name FROM employee WHERE salary (SELECT AVG(salary) FROM employee);
• 列子查询
  • 描述：返回 多行单列，需搭配 IN、ANY、ALL 等操作符。
  • 示例： SELECT name FROM department WHERE id IN (SELECT dept_id FROM employee);
• 行子查询
  • 描述：返回 单行多列，需用行表达式（如 (col1, col2)）匹配。
  • 示例： SELECT FROM employee WHERE (age, salary) = (SELECT MAX(age), MAX(salary) FROM employee);
• 表子查询
  • 描述：返回 多行多列，通常作为临时表（派生表）在 FROM 子句中使用。
  • 示例： SELECT deptname, empcount FROM (SELECT deptid, COUNT(*) AS empcount FROM employee GROUP BY dept_id) AS tmp;
• 相关子查询
  • 描述：子查询依赖外部查询的字段值，逐行执行（效率较低）。
  • 示例： SELECT name FROM employee e1 WHERE salary (SELECT AVG(salary) FROM employee e2 WHERE e1.deptid = e2.deptid);

---

三、常用操作符

• IN / NOT IN：用于判断值是否在子查询的结果集中

-- 查询有订单的客户
SELECT customer_name 
FROM customers 
WHERE id IN (SELECT customer_id FROM orders);

• ANY / SOME：比较值与子查询结果中的任意一个是否满足条件

-查询薪资大于任意一个部门平均薪资的员工 
SELECT name  FROM employee  WHERE salary ANY (SELECT AVG(salary) FROM employee GROUP BY dept_id);

• ALL：比较值需满足与子查询结果中的所有值

-查询薪资高于所有部门平均薪资的员工 
SELECT name  FROM employee  WHERE salary ALL (SELECT AVG(salary) FROM employee GROUP BY dept_id);

• XISTS / NOT EXISTS：检查子查询是否返回至少一行结果

-- 查询有下属部门的上级部门
SELECT dept_name 
FROM departments d 
WHERE EXISTS (SELECT 1 FROM departments WHERE parent_id = d.id);

---

四、典型应用场景

• 1、在 WHERE 子句中筛选数据

-- 查找薪资高于平均值的员工
SELECT name, salary 
FROM employee 
WHERE salary > (SELECT AVG(salary) FROM employee);

• 2、在 FROM 子句中作为临时表

-- 统计每个部门的平均薪资，并筛选高于公司平均薪资的部门
SELECT dept_id, avg_salary
FROM (SELECT dept_id, AVG(salary) AS avg_salary FROM employee GROUP BY dept_id) AS dept_avg
WHERE avg_salary > (SELECT AVG(salary) FROM employee);

• 3、在 FROM 子句中作为临时表

-- 显示员工姓名及其部门平均薪资
SELECT name, salary, (SELECT AVG(salary) FROM employee e2 WHERE e1.dept_id = e2.dept_id) AS dept_avg
FROM employee e1;

---

五、注意事项

• 性能优化：相关子查询需逐行执行，可能影响性能，可尝试改写为 JOIN 或临时表。
• 处理 NULL 值：使用 IN 或 NOT IN 时，若子查询结果包含 NULL，可能导致意外结果（如 NOT IN (NULL, …) 永远返回 FALSE）。
• 多层嵌套：MySQL 支持多层子查询嵌套，但需保持代码可读性。
• 别名使用：派生表必须指定别名（如 FROM (SELECT …) AS tmp）。

---

六、示例：复杂查询

-- 查找每个部门薪资最高的员工
SELECT dept_id, name, salary
FROM employee e1
WHERE salary = (SELECT MAX(salary) FROM employee e2 WHERE e1.dept_id = e2.dept_id
);

• 通过灵活运用子查询，可以解决大部分复杂的数据关联和筛选需求。
• 实际开发中需结合执行计划分析，确保查询效率。