【MySQL】18-MySQL中子查询的使用详细总结

本文链接：https://blog.csdn.net/Sihang_Xie/article/details/125803183

子查询类似于Java中的嵌套循环，指的是在一个查询语句中嵌套另一个查询语句。

1. 需求分析与问题解决

1.1 实际问题

先从一个实际问题出发，逐渐引入子查询的业务场景。

需求如上图所示。要想查询谁的工资比Abel高，首先要查询Abel的工资是多少。

首先尝试先不使用子查询，思考一下使用普通查询语句如何实现。

【方式一：先查询Abel工资，再查询比他工资高的员工】

SELECT employee_id, last_name, salary
FROM employees
WHERE last_name = 'Abel';

查询结果：

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 11000;

查询结果：

这种方法需要对数据库服务器查询两次，在实际应用场景将是用户通过网络远程与数据库服务器通信两次，才能返回最终想要的结果。显然这样拆分两步的查询效率是非常低下的。

【方式二：采用自连接】

SELECT e2.`employee_id`, e2.`last_name`, e2.`salary`
FROM employees e1 JOIN employees e2
ON e2.`salary` > e1.`salary`
WHERE e1.`last_name` = 'Abel';

查询结果：

毫无疑问，方式二查询效率要优于方式一。因为方式二仅需查询一次就能返回最终结果。但是并不是所有情况都能使用方式二这种自连接的查询，于是子查询就诞生了。

1.2 子查询的基本使用

子查询仅使用一次查询就解决上一节的需求，同时适用范围更广，且更加简单易懂、符合直觉。

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
                SELECT salary
                FROM employees
                WHERE last_name = 'Abel'
                );

查询结果：

与嵌套循环的称谓一样，在外面一层的查询称为 ”外查询“ (或 ”主查询“) ；在里面一层的查询称为 ”内查询” (或 “子查询”) 。
【执行顺序】子查询 (内查询) 先执行，然后再执行主查询 (外查询) 。
子查询的查询结果被主查询使用。

【注意】

子查询要包含在括号内。
将子查询放在比较条件的右侧。

1.3 子查询的分类

【角度一】从子查询返回的结果的条目数来看：

单行子查询 VS 多行子查询
单行子查询：子查询返回一条查询结果，称为单行子查询；
多行子查询：子查询返回多条查询结果，称为多行子查询。

【角度二】从内查询是否执行多次来看：

相关子查询 VS 不相关子查询
相关子查询：子查询多次执行，称为相关子查询。
不相关子查询：子查询只执行一次，称为不相关子查询。

举个栗子：

相关子查询：查询工资大于本部门平均工资的员工信息；

不相关子查询：查询工资大于本公司平均工资的员工信息；

由于不同员工所处的部门可能不相同，子查询中，查询部门平均工资需要在整个主查询的执行周期中反复执行多次，以计算不同部门的平均工资。这种子查询称为相关子查询。

2. 单行子查询

2.1 单行比较操作符

操作符	作用
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
<>	不等于

2.2 代码示例

【例子1】查询工资大于149号员工工资的员工信息。

SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
                SELECT salary
                FROM employees
                WHERE employee_id = 149
                );

查询结果：

【例子2】返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资。

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id = (
                SELECT job_id
                FROM employees
                WHERE employee_id = 141
                )
AND salary > (		
                SELECT salary
                FROM employees
                WHERE employee_id = 143
                );

查询结果：

【例子3】返回公司工资最少的员工的last_name,job_id和salary。

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary = (		
                SELECT MIN(salary)
                FROM employees
                );

查询结果：

【例子4】查询与141号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id。

# 方式一
SELECT employee_id, last_name, manager_id, department_id
FROM employees
WHERE manager_id = (	
                     SELECT manager_id
                     FROM employees
                     WHERE employee_id = 141
                     ) 
AND department_id = (
                     SELECT department_id
                     FROM employees
                     WHERE employee_id = 141
                     )
AND employee_id <> 141;

# 方式二
SELECT employee_id, last_name, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) = (
                                     SELECT manager_id, department_id
                                     FROM employees
                                     WHERE employee_id = 141
                                     )
AND employee_id <> 141;

查询结果：

2.3 HAVING中的子查询

【例子】查询最低工资大于50号部门最低工资的部门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 = 50
                        );

查询结果：

2.4 CASE中的子查询

【例子1】显示员工的 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;

查询结果：

2.5 子查询中的空值问题

【例子】查询与员工 ‘Haas’ 的 job_id 相同的所有员工的信息。员工表 employees 中是没有姓名叫 Haas 的员工的，因此子查询返回的是NULL，主查询也会返回NULL，并不会报错。

SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id = (
                SELECT job_id
                FROM employees
                WHERE last_name = 'Haas'
                );

查询结果：

2.6 非法使用子查询

单行子查询中，如果返回多条查询结果，就会报错。如下例子所示：

【例子8】子查询返回了每个部门中的最低工资，返回了多条结果。在单行子查询中是非法的，会出现报错。

SELECT employee_id, last_name, salary
FROM employees
WHERE salary = (
                SELECT MIN(salary)
                FROM employees
                GROUP BY department_id
                );

查询结果：

3. 多行子查询

子查询返回多条查询结果的，称为多行子查询。也称为集合比较子查询。

3.1 多行比较操作符

操作符	作用
IN	等于列表中的任意一个
ANY	需要和单行比较符结合使用，和子查询返回的某一个值比较
ALL	需要和单行比较符结合使用，和子查询返回的所有值比较
SOME	实际上是ANY的别名，作用相同，一般常常使用ANY

3.2 代码示例

【例子1】返回其它 job_id 中比 job_id 为 ‘IT_PROG’ 部门任一工资低的员工的员工号、姓名、job_id 以及 salary 。

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY(
                   SELECT salary
                   FROM employees
                   WHERE job_id = 'IT_PROG'
                   )
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';

查询结果：

【例子3】查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id，manager_id，department_id 。

SELECT employee_id, last_name, manager_id, department_id
FROM employees
WHERE manager_id IN(
                    SELECT manager_id
                    FROM employees
                    WHERE employee_id IN(141, 174)
                    )
AND department_id IN(
                    SELECT department_id
                    FROM employees
                    WHERE employee_id IN(141, 174)
                    )
AND employee_id NOT IN(141, 174);

查询结果：

【例子4】查询平均工资最低的部门id 。

# 方式一
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
                    SELECT MIN(avg_sal)
                    FROM (
                        SELECT AVG(salary) "avg_sal"
                        FROM employees
                        GROUP BY department_id
                        ) t_dept_avg_sal
);

【注意】

聚合函数不能嵌套。这题不能通过 MIN(AVG(salary)) 的方式来查询。

# 方式二
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
                        SELECT AVG(salary)
                        FROM employees
                        GROUP BY department_id
                        );

查询结果：

3.3 空值问题

如果多行子查询中返回的结果有空值NULL，则主查询永远返回NULL。

【例子1】查询员工表 employees 中，员工编号 employee_id 不是 manager_id 的员工信息。

# 错误代码
SELECT employee_id, last_name
FROM employees
WHERE employee_id NOT IN(
                         SELECT manager_id
                         FROM employees
                         );

查询结果：

原因是子查询返回的结果中有NULL：

正确的查询方法是，把子查询中的空值数据过滤掉。

# 正确代码
SELECT employee_id, last_name
FROM employees
WHERE employee_id NOT IN(
                         SELECT DISTINCT manager_id
                         FROM employees
                         WHERE manager_id IS NOT NULL
                         );

查询结果：

4. 相关子查询

4.1 相关子查询执行流程

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联。因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称为相关子查询。

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

4.2 代码示例

【例子1】查询员工中工资大于本部门平均工资的员工的 last_name，salary 和其department_id 。

# 方式一：使用相关子查询
SELECT employee_id, last_name, department_id, salary
FROM employees e1
WHERE salary > (
                SELECT AVG(salary)
                FROM employees e2
                WHERE department_id = e1.`department_id`
                );

可以看到，相关子查询的关键在于：区分外表 e1 和内表 e2 。每一次主查询都把外表 e1 的 department_id 传入子查询中。

# 方式二：在FROM中声明子查询
SELECT e.employee_id, e.last_name, e.department_id, e.salary		
FROM employees e, (
                   SELECT department_id, AVG(salary) avg_sal
                   FROM employees
                   GROUP BY department_id
                   ) t_avg_sal
WHERE e.department_id = t_avg_sal.department_id
AND e.`salary` > t_avg_sal.avg_sal;

方式二的核心思想是：把子查询查询到的公司各个部门的平均工资作为一张新表 t_avg_sal ，与员工表 employees 进行多表查询。

查询结果：

【例子2】查询员工的 id，salary，按照 department_name 排序。

# 方式一：多表查询
SELECT e.`employee_id`, e.`salary`, d.`department_name`
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
ORDER BY d.`department_name` ASC;

# 方式二：相关子查询
SELECT employee_id, salary
FROM employees e
ORDER BY (SELECT d.`department_name`
          FROM departments d
          WHERE e.`department_id` = d.`department_id`
          ) ASC;

查询结果：

【例子3】若 employees 表中 employee_id 与 job_history 表中 employee_id 相同的数目不小于2，输出这些相同 id 的员工的 employee_id，last_name 和其 job_id 。

# 方式一：在FROM中声明子查询
SELECT e.employee_id, e.last_name, e.job_id
FROM employees e, (
                   SELECT jh.employee_id, COUNT(jh.employee_id) num
                   FROM job_history jh
                   GROUP BY jh.`employee_id`
                   ) t_num
WHERE e.`employee_id` = t_num.employee_id
AND t_num.num >= 2;

# 方式二：相关子查询
SELECT employee_id, last_name, job_id
FROM employees e
WHERE 2 <= (
            SELECT COUNT(*)
            FROM job_history j
            WHERE e.`employee_id` = j.`employee_id`
            );

查询结果：

4.3 EXISTS与NOT EXISTS关键字

关联子查询通常也会和EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。
如果在子查询中不存在满足条件的行：
- 条件返回FALSE
- 继续在子查询中查找
如果在子查询中存在满足条件的行：
- 停止在子查询中继续查找
- 条件返回TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

【例子1】查询公司管理者的 employee_id，last_name，job_id，department_id 信息。

# 方法一：非相关子查询
SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN(
                    SELECT DISTINCT manager_id
                    FROM employees
                    WHERE manager_id IS NOT NULL
                    );

# 方法二：自连接
SELECT DISTINCT m.`employee_id`, m.`last_name`, m.`job_id`, m.`department_id`
FROM employees e JOIN employees m
ON e.`manager_id` = m.`employee_id`;

# 方法三：EXISTS
SELECT e1.employee_id, e1.last_name, e1.job_id, e1.department_id
FROM employees e1
WHERE EXISTS (
              SELECT *
              FROM employees e2
              WHERE e1.`employee_id` = e2.`manager_id`
              );

查询结果：

【例子2】查询 departments 表中，不存在于 employees 表中的部门的 department_id 和department_name 。

# 方法一：右外连接
SELECT d.`department_id`, d.`department_name`
FROM employees e RIGHT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

# 方法二：NOT EXISTS
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
                  SELECT *
                  FROM employees e
                  WHERE d.`department_id` = e.`department_id`
                  );

查询结果：

5. 子查询总结

在SELECT查询语句中，除了 GROUP BY 和 LIMIT 之外，其他位置都可以声明子查询。

# SQL99语法
SELECT ..., ..., ...(存在聚合函数)
FROM ... (LEFT / RIGHT OUTER)JOIN ... ON 多表查询的连接条件
(LEFT / RIGHT OUTER)JOIN ... ON 多表查询的连接条件
WHERE 不包含聚合函数的过滤条件
GROUP BY ..., ...
HAVING 包含聚合函数的过滤条件
ORDER BY ..., ...(ASC / DESC)
LIMIT ..., ...

6. 思考：自连接与子查询谁更优

在1.1节中，解决《谁的工资比Abel高？》这个问题有三种解决方法，其中两次查询不考虑，那么剩下的自连接和子查询两种方法在实际开发中哪个的查询效率更高呢？

# 方式一：自连接
SELECT e2.`employee_id`, e2.`last_name`, e2.`salary`
FROM employees e1 JOIN employees e2
ON e2.`salary` > e1.`salary`
WHERE e1.`last_name` = 'Abel';

# 方式二：子查询
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (
                SELECT salary
                FROM employees
                WHERE last_name = 'Abel'
                );

【答案】自连接方式更好。

【原因】题目中可以使用子查询，也可以使用自连接。一般情况建议使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。