MySQL / 系统课时
列子查询
列子查询
列子查询返回一列中的多个值,适合表达“属于某个集合”“大于其中任意值”或“大于全部值”这类条件。本课通过 IN、ANY 和 ALL 对比它们处理多行单列结果时的不同语义。
学习目标
- 能使用
IN匹配列子查询返回的值集合 - 能区分
ANY与ALL的比较语义 - 能根据业务条件选择合适的多值比较方式
列子查询返回的是值集合
与标量子查询不同,列子查询允许返回多行,但每行只能提供一列用于比较。最常见的写法是与 IN 配合,判断外层值是否存在于子查询结果集合中。
下面查询研发部门员工。内层查询先返回研发部门的部门编号,外层再根据这些编号筛选员工。
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;预期结果
+----+-------+---------------+
| id | name | department_id |
+----+-------+---------------+
| 1 | Alice | 10 |
| 2 | Bob | 20 |
+----+-------+---------------+这里内层查询产生集合 {10, 20},IN 判断员工的 department_id 是否属于该集合。
ANY 表示满足任意一个值
ANY 会把左侧表达式分别与子查询返回值比较,只要至少一次比较为真,整个条件就为真。以 > ANY 为例,它表示“大于集合中的至少一个值”。
下面找出薪资高于研发部门任意一名员工薪资的员工。研发员工薪资为 7000.00 和 9000.00,因此只要薪资大于其中较低的 7000.00 就满足条件。
SELECT id, name, salary
FROM employees
WHERE salary > ANY (
SELECT salary
FROM employees
WHERE department_id IN (10, 20)
)
ORDER BY id;预期结果
+----+-------+----------+
| id | name | salary |
+----+-------+----------+
| 2 | Bob | 9000.00 |
| 3 | Carol | 11000.00 |
| 4 | David | 8000.00 |
+----+-------+----------+对于不含 NULL 的普通数值集合,x > ANY (...) 可以直观理解为 x 大于集合最小值,但实际阅读 SQL 时仍应按“至少满足一个比较”理解,避免在其他比较符号下误套规律。
ALL 表示满足全部值
ALL 要求左侧表达式与子查询返回的每一个值比较时都成立。> ALL 因此表示“大于集合中的所有值”。
SELECT id, name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department_id IN (10, 20)
)
ORDER BY id;预期结果
+----+-------+----------+
| 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,而不是把所有多表逻辑都堆进子查询。