P45 45. 基础-多表查询-列子查询

一句话理解

列子查询返回一列多行，等于给你一个集合：在集合里挑值用 IN，拿一个值和整个集合逐个比用 ANY / ALL。

按这 4 步做

1. 先确认内层只查一列。多查一列就变成表子查询，IN 接不住；一列多行意味着结果可能是 0 行、1 行或很多行。
2. IN 判断「在不在这个集合里」，最常用；NOT IN 是它的反面，但集合里有 NULL 时整体变成 unknown，结果常常是 0 行。
3. ANY（与 SOME 完全同义）是「至少有一个满足」：研发部工资是 8000、10000，salary > ANY（那个子查询）只要大于 8000 就成立，也就是大于集合里的最小值。
4. ALL 是「全部都满足」：同一份数据里 salary > ALL（那个子查询）要大于 10000，也就是大于集合里的最大值。写完用行数反推，别只看语法。

小例

先准备本集练习数据：p45.sql · 练习库说明

-- 表：department(1 研发部, 2 测试部, 3 运营部)
-- employee：小林22/研发8000/上级2，小周25/研发10000/无上级，小陈30/测试12000/上级2，小李21/无部门/NULL/上级2
-- 建表与插入语句见本页练习脚本 p45.sql（独立练习库 mysqlnotes_p45，跑一次就有这两张表）

-- IN：研发部和测试部的员工
SELECT name AS 员工, dept_id AS 部门号
FROM employee
WHERE dept_id IN (
  SELECT id FROM department WHERE name IN ('研发部','测试部')
);

-- ALL：比研发部所有人都高（= 大于最大值）
SELECT name AS 员工, salary AS 工资
FROM employee
WHERE salary > ALL (
  SELECT salary FROM employee WHERE dept_id = 1
);

-- ANY：比研发部“任意一人”工资高即可（= 大于最小值）
SELECT name AS 员工, salary AS 工资
FROM employee
WHERE salary > ANY (
  SELECT salary FROM employee WHERE dept_id = 1
)
ORDER BY id;

第一段内层返回 1、2 两个部门号，外层 3 行：小林、小周、小陈。第二段研发部工资是 8000、10000，> ALL 要大于 10000，只剩小陈 12000（1 行）。第三段 > ANY 只要大于 8000 就成立，小周 10000、小陈 12000 都入选（2 行）——同一份数据，ALL 与 ANY 的差别就是「全部」还是「任意一个」。记住方向：> ANY ≡ > MIN，> ALL ≡ > MAX；反过来 < ANY ≡ < MAX，< ALL ≡ < MIN。ALL 的好处是不用自己先想到 MAX，语义直接；ANY 的好处是「比其中任意一个高」这种需求能直说。

-- 集合 {3000, 6000, 9000}：5000 比最小的大、比最大的小
SELECT 5000 > ANY (SELECT 3000 UNION SELECT 6000 UNION SELECT 9000) AS 比任意一个大,
       5000 > ALL (SELECT 3000 UNION SELECT 6000 UNION SELECT 9000) AS 比全部都大;

SELECT (1 > ALL (SELECT salary FROM employee WHERE 1 = 0)) AS all_empty,
       (1 > ANY (SELECT salary FROM employee WHERE 1 = 0)) AS any_empty,
       ((5 > ALL (SELECT NULL)) IS NULL) AS all_null_unknown;

易错点

• NOT IN 的子查询结果含 NULL 时，整个条件永远不为真，结果 0 行；要么子查询加 WHERE dept_id IS NOT NULL，要么改用 NOT EXISTS。
• IN 后面的子查询只能返回一列；返回两列会报错，需要改成整行比较。
• 把 > ALL 读成「大于其中某一个」就全错了，那是 ANY 的语义；ALL 是「全部都要满足」。
• 以为 ANY 遇空集会全选：空集时 ANY 恒假（0 行），ALL 反而恒真。

30 秒速记

自测（3 题，先答再展开）

材料与来源