SQL基础⑦ | 子查询

0 序言

本文将系统讲解SQL中子查询的概念、分类、执行逻辑及实际用法，包括单行子查询、多行子查询、相关子查询的操作符与示例，以及子查询的注意事项。

通过学习，你将掌握如何使用子查询解决复杂查询问题，理解不同类型子查询的适用场景，提升SQL查询的灵活性与效率。

1 子查询概述

1.1 什么是子查询

什么是子查询呢？

子查询是嵌套在另一个查询语句内部的查询。

从MySQL 4.1开始引入，它能增强SELECT查询能力，支持从结果集获取数据，或用一个查询结果作为另一个查询的条件，

打个例子，如"谁的工资比TOM高"可通过子查询先获取Abel的工资，再筛选更高工资的员工。

这样子查询的效率就提高了。

1.2 基本语法

sql

复制代码

SELECT select_list

FROM table

WHERE expr operator (

SELECT select_list

FROM table

);

子查询需包含在括号内，通常放在比较条件右侧。

子查询（内查询）先执行，结果供主查询（外查询）使用。

1.3 分类

1.3.1 按结果行数分类

单行子查询：内查询返回一条记录，对应单行比较操作符（=、>、<等）。

多行子查询：内查询返回多条记录，对应多行比较操作符（IN、ANY、ALL等）。

1.3.2 按执行次数分类

不相关子查询：内查询仅执行一次，结果作为主查询条件。比方说先查A的工资，再用该值筛选其他员工）。

相关子查询：内查询执行次数依赖外部查询，每次外部查询行变化时，子查询需重新计算。比如查询员工工资高于本部门平均工资）。

2 单行子查询

2.1 单行比较操作符

操作符

含义

=

等于

>

大于

>=

大于等于

<

小于

<=

小于等于

<>

不等于

2.2 代码示例

2.2.1 子查询基础

查询工资大于149号员工工资的员工姓名

sql

复制代码

SELECT last_name

FROM employees

WHERE salary > (

SELECT salary

FROM employees

WHERE employee_id = 149

);

说明：子查询先获取149号员工的工资，主查询用该值筛选工资更高的员工。

2.2.2 多条件子查询

返回job_id与141号员工相同、工资比142号员工高的员工信息

sql

复制代码

SELECT 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 = 142

);

2.2.3 在HAVING中使用子查询

查询最低工资大于50号部门最低工资的部门ID及最低工资

sql

复制代码

SELECT department_id, MIN(salary)

FROM employees

GROUP BY department_id

HAVING MIN(salary) > (

SELECT MIN(salary)

FROM employees

WHERE department_id = 50

);

结果如下：

2.3 注意事项

子查询返回多行时，使用单行操作符会报错！！！

子查询结果为空时，主查询可能返回空集（如查询与"Haas"同job_id的员工，若"Haas"不存在则无结果）。

3 多行子查询

3.1 多行比较操作符

操作符

含义

IN

等于列表中的任意一个值

ANY

与单行操作符配合，和子查询返回的某一个值比较

ALL

与单行操作符配合，和子查询返回的所有值比较

SOME

ANY的别名，作用相同

3.2 代码示例

3.2.1 使用ANY

返回其他job_id中比IT_PROG部门任一工资低的员工信息

sql

复制代码

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

3.2.2 使用ALL

返回其他job_id中比"IT_PROG"部门所有工资都低的员工信息

sql

复制代码

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

这里需要明白两者之间的区别，

ANY是存在一个即可，ALL是全部满足才行，两者筛选范围不同，导致结果集大小有差异,

通常来说，ANY的结果要多于ALL。

3.2.3 查询平均工资最低的部门ID

sql

复制代码

-- 方式1：嵌套子查询

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

) dept_avg_sal

);

-- 方式2：使用ALL

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的值，可能导致结果异常。

例如

sql

复制代码

SELECT last_name FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees)

因manager_id可能为NULL，NOT IN会判定为未知，返回空集。

就是这么一个情况。

4 相关子查询

4.1 执行流程

相关子查询的执行依赖外部查询，需循环执行：

从主查询获取一行数据；

子查询使用主查询的当前行数据进行计算；

若满足条件，则主查询保留该行；

重复上述步骤，直到主查询所有行处理完毕。

4.2 代码示例

4.2.1 基础示例

查询工资大于本部门平均工资的员工信息

sql

复制代码

USE demo_hr;

SELECT last_name, salary, department_id

FROM employees AS outer_tbl

WHERE salary > (

SELECT AVG(salary)

FROM employees

WHERE department_id = outer_tbl.department_id

);

（注：子查询中outer.department_id引用主查询的当前部门ID，每次主查询行变化时重新计算部门平均工资）

4.2.2 使用EXISTS

查询公司管理者的信息（存在下属的员工）

sql

复制代码

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

);

要注意哦，

EXISTS仅判断子查询是否有结果，有则保留主查询行，它的效率是高于IN的！

4.2.3 相关更新与删除

相关更新：依据另一表数据更新当前表

sql

复制代码

-- 为employees表增加department_name字段并填充部门名称

ALTER TABLE employees ADD (department_name VARCHAR(14));

UPDATE employees e

SET department_name = (

SELECT department_name

FROM departments d

WHERE e.department_id = d.department_id

);

相关删除：依据另一表数据删除当前表记录

sql

复制代码

-- 删除与emp_history表中重复的员工数据

DELETE FROM employees e

WHERE employee_id IN (

SELECT employee_id

FROM emp_history

WHERE employee_id = e.employee_id

);

5 子查询与自连接的对比

查询工资比Abel高的员工，可用子查询或自连接：

sql

复制代码

-- 子查询

SELECT last_name, salary

FROM employees

WHERE salary > (

SELECT salary

FROM employees

WHERE last_name = 'Abel'

);

-- 自连接

SELECT e2.last_name, e2.salary

FROM employees e1, employees e2

WHERE e1.last_name = 'Abel'

AND e1.salary < e2.salary;

这两个运行的结果都一样，从直观观感来说，

自连接的程序会更简洁一些，

但这个因人而异，熟练了其实都差不多。

但大多数来讲，自连接更高效，多数数据库对自连接优化更好，

因为自连接基于已知表关联，而子查询需先处理未知结果集，执行速度较慢。

6 总结

子查询是嵌套在其他查询中的查询，按结果行数可分为单行和多行两种。

其中单行用=、>等，多行用IN、ANY等

按执行次数可分为不相关（一次执行）和相关（依赖外部查询，循环执行）。

我们在使用时要注意操作符与子查询类型匹配、空值对结果的影响，以及EXISTS的高效性。

实际开发中，

如果你遇到的是比较复杂查询可优先考虑自连接，

因为执行速度会更快，小型数据库里看不出太大差距，

但是数据库一庞大起来的话，自连接的优势就会体现出来啦！

子查询则适用于逻辑更清晰的场景。

就讲这么多了。