MySQL / 系统课时

列子查询

列子查询

列子查询返回一列中的多个值,适合表达“属于某个集合”“大于其中任意值”或“大于全部值”这类条件。本课通过 IN、ANY 和 ALL 对比它们处理多行单列结果时的不同语义。

学习目标

  • 能使用 IN 匹配列子查询返回的值集合
  • 能区分 ANY 与 ALL 的比较语义
  • 能根据业务条件选择合适的多值比较方式

列子查询返回的是值集合

与标量子查询不同,列子查询允许返回多行,但每行只能提供一列用于比较。最常见的写法是与 IN 配合,判断外层值是否存在于子查询结果集合中。

下面查询研发部门员工。内层查询先返回研发部门的部门编号,外层再根据这些编号筛选员工。

sql
CREATE TEMPORARY TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL
);

CREATE TEMPORARY TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    department_id INT NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

INSERT INTO departments (id, name) VALUES
(10, '研发一部'),
(20, '研发二部'),
(30, '销售部');

INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Alice', 10, 7000.00),
(2, 'Bob',   20, 9000.00),
(3, 'Carol', 30, 11000.00),
(4, 'David', 30, 8000.00);

SELECT id, name, department_id
FROM employees
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE name LIKE '研发%'
)
ORDER BY id;

预期结果

text
+----+-------+---------------+
| id | name  | department_id |
+----+-------+---------------+
|  1 | Alice |            10 |
|  2 | Bob   |            20 |
+----+-------+---------------+

这里内层查询产生集合 {10, 20},IN 判断员工的 department_id 是否属于该集合。

ANY 表示满足任意一个值

ANY 会把左侧表达式分别与子查询返回值比较,只要至少一次比较为真,整个条件就为真。以 > ANY 为例,它表示“大于集合中的至少一个值”。

下面找出薪资高于研发部门任意一名员工薪资的员工。研发员工薪资为 7000.00 和 9000.00,因此只要薪资大于其中较低的 7000.00 就满足条件。

sql
SELECT id, name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary
    FROM employees
    WHERE department_id IN (10, 20)
)
ORDER BY id;

预期结果

text
+----+-------+----------+
| id | name  | salary   |
+----+-------+----------+
|  2 | Bob   |  9000.00 |
|  3 | Carol | 11000.00 |
|  4 | David |  8000.00 |
+----+-------+----------+

对于不含 NULL 的普通数值集合,x > ANY (...) 可以直观理解为 x 大于集合最小值,但实际阅读 SQL 时仍应按“至少满足一个比较”理解,避免在其他比较符号下误套规律。

ALL 表示满足全部值

ALL 要求左侧表达式与子查询返回的每一个值比较时都成立。> ALL 因此表示“大于集合中的所有值”。

sql
SELECT id, name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id IN (10, 20)
)
ORDER BY id;

预期结果

text
+----+-------+----------+
| id | name  | salary   |
+----+-------+----------+
|  3 | Carol | 11000.00 |
+----+-------+----------+

研发员工的最高薪资是 9000.00,因此只有 Carol 的 11000.00 同时大于子查询返回的两个值。

IN、ANY 与 ALL 怎么选

当需求是“值是否属于集合”时优先使用 IN,语义最直接。= ANY 与 IN 在常见非 NULL 场景下表达相近,但 IN 更易读;当需求包含大小比较时,再使用 > ANY、< ANY、> ALL 或 < ALL。

需要特别注意空结果和 NULL。子查询返回的集合如果包含 NULL,三值逻辑可能让条件结果变成未知,而不是简单的真或假。业务字段允许 NULL 时,应先明确是否需要在子查询中排除它,例如加入 WHERE salary IS NOT NULL。

列子查询适合表达一个明确的动态集合。当查询逐渐演变为多表取字段、需要同时展示关联表数据,或者执行计划显示关联方式更合适时,也应考虑改写为 JOIN,而不是把所有多表逻辑都堆进子查询。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

列子查询

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通