9.1 概述
子查询指一个查询语句嵌套在另一个查询语句内部，这个特性从 MySQL 4.1 开始引入。
从相对位置来说，子查询又被称为内查询，主查询又被称为外查询
9.1.1 子查询的结构
子查询的结构如下所示：
SELECT select_list
FROM table
WHERE expr operator
(
SELECT select_list
FROM table
);
说明：将在下面的内容中逐一细说
- 子查询在主查询之前执行（不相关子查询执行一次，相关子查询执行多次）
- 子查询的结果被主查询使用
- 子查询要包含在一对括号内
- 将子查询放在比较条件的右侧（规范，不遵守不会导致出错）
- 单行操作符对应单行子查询，多行操作符对应多行子查询
- 编写子查询 SQL 语句的技巧：① 从里往外写；② 从外往里写
9.1.2 子查询的分类
(1) 分类方式一
根据内查询的结果，是返回一条还是多条记录，将子查询分为单行子查询和多行子查询。
-
单行子查询：
-
多行子查询：
(2) 分类方式二
按照内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。
-
不相关子查询：子查询从数据表中查询得到结果，这个结果只执行一次，并且作为主查询的条件执行
-
相关子查询：从外部查询开始，主查询“传给”子查询条件，然后子查询根据条件进行查询，并将结果反馈给外部，这个过程需要重复多次
9.2 单行子查询
9.2.1 单行比较操作符
| 操作符 | 含义 |
|---|---|
| = | 等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| <> | 不等于 |
代码示例：
例题 1：查询工资大于 149 号员工工资的员工的信息
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE employee_id = 149
);
通常，我们将子查询放在比较条件的右侧。当然，放在左侧不会产生错误，只是不美观，如下所示：
SELECT employee_id, last_name, salary
FROM employees
WHERE (
SELECT salary
FROM employees
WHERE employee_id = 149
) < salary;
9.2.2 HAVING中使用子查询
执行的流程：
- 首先执行子查询
- 向主查询中的 HAVING 子句返回结果
例题 2：查询最低工资大于 110 号部门最低工资的部门 id 和其最低工资
SELECT department_id, MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 110
);
9.2.3 CASE中的子查询
例题 3：显示员工的 employee_id、last_name 和 location。其中，若员工 department_id 与 location_id 为 1800 的 department_id 相同，则 location 为 “Canada”，其余则为 “USA”
SELECT employee_id, last_name, CASE department_id WHEN (
SELECT department_id
FROM departments
WHERE location_id = 1800
) THEN 'Canada'
ELSE 'USA' END AS "location"
FROM employees;
9.2.4 可能遇到的问题 1
(1) 空值问题
子查询返回的是空值，导致主查询失败。
SELECT last_name, job_id
FROM employees
WHERE job_id =
(
SELECT job_id
FROM employees
WHERE last_name = 'Haas'
);
说明：公司没 “Hass” 这个人，内查询的值为 NULL，导致外查询啥也查不到，如下图所示
(2) 子查询包含在括号内
子查询必须包含在一对括号内，否则报错，如下所示：
SELECT employee_id, last_name, salary
FROM employees
WHERE salary >
SELECT salary
FROM employees
WHERE employee_id = 149;
强调：子查询要包含在一对括号内
(3) 非法使用操作符
在多行子查询中使用单行操作符，导致错误。
SELECT employee_id, last_name
FROM employees
WHERE salary =
(
SELECT MIN(salary)
FROM employees
GROUP BY department_id
);
说明：单行操作符对应单行子查询，多行操作符对应多行子查询。有时，由于没有预判到子查询会返回多条数据（多行子查询），而使用了单行操作符，导致出错，上述的查询结果如下
由上面的几个例题可以体会到：
子查询在主查询之前一次执行完成：无论主查询执行多少次，子查询都只执行一次，所有的主查询使用的都是子查询返回的那个唯一结果（不相关的单行子查询）
子查询的结果被主查询使用
9.3 多行子查询
说明：
- 子查询返回多条记录
- 使用多行操作符
9.3.1 多行操作符
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的任一一个 |
| ANY | 需要和单行比较操作符一起使用，和子查询返回的某一个值比较 |
| ALL | 需要和单行比较操作符一起使用，和子查询返回的所有值比较 |
| SOME | 实际上是 ANY 的别名，作用相同，一般常使用 ANY |
例题 1：返回其它 job_id 中比 job_id 为 “IT_PROG” 部门任一工资低的员工的员工号、姓名、job_id以及 salary。
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (
# 查询部门 id 为 "IT_PROG" 的员工的工资
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
说明：“任一”、“低” 表示只要小于 “IT_PROG” 部门中的最高工资即可，等价代码如下
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < (
# 查询'IT_PROG'部门的最高员工工资
SELECT MAX(salary)
FROM (
# 查询'IT_PROG'部门的员工工资
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
) it_prog_sal
) AND job_id <> 'IT_PROG';
例题 2：返回其它 job_id 中比 job_id 为 “IT_PROG” 部门所有工资低的员工的员工号、姓名、job_id 以及 salary。
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
)
AND job_id <> 'IT_PROG';
说明：“所有” 意味着要低于 “IT_PROG” 部门中的最低工资。
附：可以使用 ALL 搭配 “<=” 或 “>=” 求解最小值或最大值。
例题 3：查询平均工资最低的部门 id
(1) 常规思路：求每个部门的平均工资 → 求最小的平均工资 → 求哪个部门的平均工资是这个最小值
# 求哪个部门的平均工资是这个最小值 SELECT department_id # 50 FROM employees GROUP BY department_id HAVING AVG(salary) = ( # 求最小的平均工资 SELECT MIN(avg_sal) FROM ( # 求每个部门的平均工资 SELECT AVG(salary) AS "avg_sal" FROM employees GROUP BY department_id ) t_dept_avg_sal );本题可以使用 <= 和 ALL 来求解：
SELECT department_id FROM employees GROUP BY department_id # <= ALL：只有最小值满足这个条件，所以这是在求最小值 HAVING AVG(salary) <= ALL ( # 求所有部门的平均工资 SELECT AVG(salary) FROM employees GROUP BY department_id );
9.3.2 可能遇到的问题 2
空值问题
如下面代码所示，在子查询中包含一个 NULL 值，而 NOT IN 要与子查询中的所有值比较，也就是会与 NULL 值运算，所以导致最终的查询为空
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
SELECT manager_id
FROM employees
);
将 NOT IN 修改为 IN 会如何呢？成功查询，不报错。因为 IN 表示“任一”，只要与多行子查询返回结果中的一个值匹配就可以，不一定与子查询中的 NULL 值进行比较。
SELECT last_name
FROM employees
WHERE employee_id IN (
SELECT manager_id
FROM employees
);
9.4 相关子查询
9.4.1 相关子查询的执行流程
每执行一次主查询，子查询都要执行一次，并且在主查询之前执行。子查询使用主查询中的某个列，并返回给主查询某个（些）行。流程如下图所示：
相关子查询的书写格式：
SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator
(
SELECT column1, column2
FROM table2 inner
WHERE inner.expr1 = outer.expr2
);
例题 1：查询员工中工资大于本部门平均工资的员工的 last_name、salary 及其 department_id
SELECT last_name, salary, department_id
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.`department_id` = e1.`department_id`
);
说明：由这个例题可以看出
- 子查询在主查询之前执行，并且需要执行多次
- 子查询的结果被主查询使用
- 子查询包含在一对小括号内
9.4.2 FROM中使用相关子查询
例题 1 可以改写为多表连接的形式：
SELECT e.`last_name`, e.`salary`, e.`department_id`
# 两张表连接，求笛卡尔积
FROM employees e, (
# “虚拟表”：部门 + 部门平均工资
SELECT department_id, AVG(salary) AS "dept_avg_sal"
FROM employees
GROUP BY department_id
) t_dept_avg_sal
# 过滤数据：只有 department_id 相同的才可以拼接为一行数据
WHERE e.`department_id` = t_dept_avg_sal.`department_id`
# 一行中，员工的工资需大于部门平均工资
AND e.`salary` > t_dept_avg_sal.dept_avg_sal;
说明：子查询作为 FROM 从句中的一张“虚拟表”，要使用一对小括号 ( ) 括起来，而且必须要为“虚拟表”起别名，否则报错，如下所示：
9.4.3 ORDER BY中使用相关子查询
例题 2：查询员工的 id、salary，按照 department_name 排序
SELECT employee_id, salary
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE d.`department_id` = e.`department_id`
);
9.4.4 关键字EXISTS与NOT EXISTS
相关子查询通常也会与 EXISTS 关键字一起使用，用来检查在子查询中是否存在满足条件的行。
EXISTS：
- 如果子查询中的当前行不满足条件，则条件返回 FALSE，并继续查询下一行
- 如果子查询中的当前行满足条件，则条件返回 TRUE，并停止查找
附：
NOT EXISTS与EXISTS相反，如果不满足条件则返回 TRUE，满足条件则返回 FALSE。
例题 3：查询公司管理者的 employee_id、last_name、job_id、department_id
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS (
SELECT *
FROM employees e2
WHERE e2.`manager_id` = e1.`employee_id`
);
解释：对于当前员工 e 1 e_1 e1，如果他有下属 e 2 e_2 e2，这说明 e 1 e_1 e1 是管理者。
当然，还有其他方法：
方法二：自连接
SELECT DISTINCT emp.`employee_id`, emp.`last_name`, emp.`job_id`, emp.`department_id` FROM employees mgr, employees emp WHERE mgr.`manager_id` = emp.`employee_id`;方法三：不相关的多行子查询，先查找出所有管理者的 employee_id，然后逐一核实每个员工是否为管理者中的一员
SELECT employee_id, last_name, job_id, department_id FROM employees # 逐一核实每个员工是否为管理者中的一员 WHERE employee_id IN ( # 查找出所有管理者的 employee_id SELECT DISTINCT manager_id FROM employees );思考：方式二的自连接好还是方式一、方式三的子查询好？
- 自连接好。在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多，DBMS 将子查询转化成了自连接。
例题 4：查询 departments 表中，不存在于 employees 表中的部门的 department_id 和 department_name
SELECT d.`department_id`, d.`department_name`
FROM departments d
WHERE NOT EXISTS (
SELECT *
FROM employees e
WHERE e.`department_id` = d.`department_id`
);
解释：NOT EXISTS 与 EXISTS 相反，子查询为 FALSE，即 employees 表中不存在当前这个 department_id 时，子查询返回 TRUE，主查询则返回当前这个 department_id 和 department_name。
附：这个题翻译一下就是求 departments 表相对于 employees 表中独有的数据，也就是求下图中的紫色区域，利用外连接的变式求解
SELECT d.`department_id`, d.`department_name`
FROM employees e
RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
总结：
- 由上面子查询的所有内容可知，在查询语句中，除了
GROUP BY和LIMIT，其他位置都可以声明子查询。- 子查询 SQL 编写技巧：
- 如果子查询相对简单，则可以从外往里写；如果子查询较复杂，则从里往外写
- 如果子查询是相关子查询，通常是从外往里写
1294
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
