SQL Academy · 课时

IN、ANY 与 ALL 对比

Q: 「IN、ANY 与 ALL 对比」这节课中我会学到什么？

与值集合进行比较 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

与值集合进行比较

IN、ANY 与 ALL 对比 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

IN、ANY 和 ALL 是什么

编写 SQL 查询时，您经常需要将一个值与一组值进行比较。SQL 为此提供了三个强大的运算符：IN、ANY 和 ALL。

这三个运算符都可以与返回值列表的子查询配合使用。它们看起来很相似，但行为各不相同——选择正确的运算符可以让查询更具表达力，也更高效。

IN 运算符

IN 运算符会检查某个值是否匹配列表或子查询结果中的任意值。这是三个运算符中最简单的一个。

下面的示例会获取在 Sales、HR 或 Engineering 部门工作的员工。

SELECT name, department
FROM employees
WHERE department IN ('Sales', 'HR', 'Engineering');

使用子查询的 IN

将 IN 与子查询结合使用时，它会变得尤其强大。您不必使用硬编码列表，而是可以直接从另一个表中获取用于比较的值。

这里，我们要查找所有属于预算超过 100,000 的部门的员工。

SELECT name, department
FROM employees
WHERE department IN (
  SELECT dept_name
  FROM departments
  WHERE budget > 100000
);

ANY 运算符

ANY（也写作 SOME）会使用 =、>、<、>= 或 <= 等比较运算符，将某个值与子查询返回的每个值进行比较。

只要子查询结果中至少有一个值满足该比较条件，整个条件就为真。

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary
  FROM employees
  WHERE department = 'HR'
);

= ANY 等价于 IN

需要了解的一个重要等价关系是：= ANY (subquery) 在逻辑上与 IN (subquery) 完全相同。当某个值匹配子查询返回的至少一个结果时，两者都会返回真。

下面两个查询会产生相同的结果：

-- Using IN
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NYC');

-- Using = ANY
SELECT name FROM employees
WHERE department_id = ANY (SELECT id FROM departments WHERE location = 'NYC');

ALL 运算符

ALL 会将某个值与子查询结果中的每个值进行比较。只有当比较条件对返回的所有值都成立时，整个条件才为真。

这个示例会查找工资高于 Intern 级别所有工资的员工——也就是说，他们的工资高于工资最高的实习生。

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE job_grade = 'Intern'
);

ANY 与 ALL：并列比较

关键区别在于：ANY 要求条件对至少一个值成立，而 ALL 要求条件对每一个值都成立。

可以这样理解：ANY 就像对所有比较执行 OR，而 ALL 就像对所有比较执行 AND。

-- Returns rows where salary > at least one value in the subquery
SELECT name, salary FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department = 'Support');

-- Returns rows where salary > every value in the subquery
SELECT name, salary FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Support');

NOT IN 与 <> ALL

正如 = ANY 与 IN 相对应，运算符 <> ALL 也等价于 NOT IN。只有当某个值与子查询中的任何值都不匹配时，两者才会返回真。

不过请注意——如果子查询哪怕返回一个 NULL，由于 SQL 的三值逻辑，NOT IN 将不会返回任何行。

-- Using NOT IN
SELECT name FROM employees
WHERE department_id NOT IN (SELECT id FROM departments WHERE location = 'Remote');

-- Using <> ALL (equivalent)
SELECT name FROM employees
WHERE department_id <> ALL (SELECT id FROM departments WHERE location = 'Remote');

使用 NOT IN 时的 NULL 陷阱

一个关键陷阱是：如果与 NOT IN 配合使用的子查询返回了 NULL，那么整个 NOT IN 条件对于每一行都会计算为 UNKNOWN，从而返回空结果集。

为避免这种情况，请在子查询中过滤掉 NULL，或者改用 NOT EXISTS——它能够安全地处理 NULL。

-- Dangerous: if manager_id has any NULL, no rows are returned
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

-- Safe: filter NULLs explicitly
SELECT name FROM employees
WHERE id NOT IN (
  SELECT manager_id FROM employees
  WHERE manager_id IS NOT NULL
);

实际示例：查找高薪员工

下面让我们看看这三个运算符在实际场景中的应用。我们有一个 employees 表，其中包含 name、department 和 salary 列。

我们想查找工资高于任一单独部门平均工资的员工——也就是说，工资高于至少一个部门的平均工资。

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT AVG(salary)
  FROM employees
  GROUP BY department
)
ORDER BY salary DESC;

如何选择运算符

下面是选择这三个运算符的快速指南：

IN——当您有一个静态列表，或需要针对子查询执行简单的相等匹配时使用。
= ANY——与 IN 相同，但允许您使用 >、< 等其他比较运算符。
ALL——当条件必须对每个值都成立时使用（例如，不使用 MAX 来实现大于最大值的条件）。
NOT IN——使用方便，但要注意 NULL；当子查询可能包含 NULL 时，优先使用 NOT EXISTS。

快速检查

测试您对 IN、ANY 和 ALL 的理解。

课程回顾

在本课中，您学习了如何使用 IN、ANY 和 ALL 将值与一组值进行比较。

要点：

IN 检查成员关系——当某个值匹配列表或子查询中的任意项时，结果为真。
= ANY 等价于 IN；使用 > ANY 等其他运算符时，只要至少有一个匹配项，结果就为真。
ALL 表示条件必须对所有由子查询返回的值都成立。
当子查询包含 NULL 时，NOT IN 会悄悄地不返回任何行——请始终防范这种情况。
当可能存在 NULL 时，请使用 NOT EXISTS 作为比 NOT IN 更安全的替代方案。

常见问题解答