PostgreSQL中的IN运算符与ANY运算符的区别

Question

IN和ANY运算符在PostgreSQL中有什么区别？
这两者的工作机制似乎是相同的。有人能举个例子来解释一下吗？

- mohangraj

---

可能是 postgreSQL - in vs any 的重复问题。 - Vivek S.

这个回答解决了你的问题吗？SQL中IN和ANY运算符的区别 - philipxy

Answer 1

（严格来说，IN 和 ANY 是Postgres的“构造”或“语法元素”，而不是“操作符”。）

从逻辑上讲，引用手册：

IN 等效于 = ANY。

但是 IN 有两个语法变体，ANY 也有两个变体。详情如下：

• 如何在 WHERE 子句中使用 ANY 替代 IN？

采用一个集合的 IN 等同于采用一个集合的 = ANY，如下所示：

• PostgreSQL - IN vs ANY

但是每个构造的第二种变体略有不同。第二种 ANY 构造采用一个数组（必须是实际的数组类型），而 IN 的第二种变体采用逗号分隔的值列表。这会导致在传递值时有不同的限制，在特定情况下还可能导致不同的查询计划：

• 使用 =any() 时索引未被使用但使用了 in
• 将多个集合或值数组传递给函数
• 如何匹配复合类型的数组中的元素？

ANY 更加通用

ANY 构造更加通用，因为它可以与各种操作符组合使用，而不仅仅是 =。例如：

SELECT 'foo' LIKE ANY('{FOO,bar,%oo%}');

对于大量的值，提供一个集合在每个方面都更好：

• 优化具有大IN的Postgres查询

相关：

• PostgreSQL可以索引数组列吗？

反转/相反/排除

"查找在给定数组中的id行":

SELECT * FROM tbl WHERE id = ANY (ARRAY[1, 2]);

Inversion: "查找数组中不包含id的行"：

SELECT * FROM tbl WHERE id <> ALL (ARRAY[1, 2]);
SELECT * FROM tbl WHERE id <> ALL ('{1, 2}');  -- equivalent array literal
SELECT * FROM tbl WHERE NOT (id = ANY ('{1, 2}'));

所有三个等效。第一个使用数组构造器，其他两个使用数组字面值。未经类型标注的数组字面值的类型是从（已知）左边元素类型派生出来的。
在其他情况下（已有类型的数组值/需要不同类型/非默认类型的数组构造器），您可能需要显式转换。

id IS NULL 的行也不会通过这些表达式。要额外包含NULL值：

SELECT * FROM tbl WHERE (id = ANY ('{1, 2}')) IS NOT TRUE;

- Erwin Brandstetter

---

明确表述第二个变体的结果始终相同会很好。我99%确认这确实是情况，但答案似乎没有说明。这意味着SELECT * from mytable where id in (1, 2, 3)和SELECT * from mytable where id = ANY('{1, 2, 3}')将始终返回相同的行，即使它们可能有不同的查询计划。 - KPD

ANY 不能 与 != 运算符结合使用。我认为这并没有被记录在文档中，但是 select * from foo where id != ANY (ARRAY[1, 2]) 与 select * from foo where id NOT IN (1, 2) 不同。另一方面，select * from foo where NOT (id = ANY (ARRAY[1, 2])) 的效果如预期。 - qris

@qris：ANY 可以和 != 运算符结合使用，但这并不是全部。我在上面添加了一个章节。（请注意，在标准 SQL 中 < > 是运算符，尽管在 Postgres 中也接受 != 。） - Erwin Brandstetter

@dvtan: (id = ...) IS NOT TRUE 之所以有效是因为 id = ... 只有在实际匹配时才会计算为 TRUE。结果为 FALSE 或 NULL 也会通过我们的测试。参见：https://dev59.com/K2Ag5IYBdhLWcg3wS5WC#23767625。您添加的表达式测试的是其他内容。这将等同于 WHERE id <> ALL (ARRAY [1, 2]) OR id IS NULL;。 - Erwin Brandstetter

@ErwinBrandstetter 我发现这个提交已经修复了。不确定如何更快地加速IN子句。https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=50e17ad28 - jian

Answer 2

除了另一个答案中提到的点外，还有两个明显的观点：

• 在使用子查询时它们是完全等效的：

SELECT * FROM table
WHERE column IN(subquery);

SELECT * FROM table
WHERE column = ANY(subquery);

另一方面：

• 仅IN运算符允许使用简单的列表：

SELECT * FROM table
WHERE column IN(… , … , …);

在假定它们完全相同的情况下，有几次我会犯错，因为忘记了 ANY 不能与列表一起使用。

- Manngo

---

对于“简单列表”情况，WHERE id = ANY(array[1,2]) 就可以了。 - undefined

或者，使用这个答案中的示例，WHERE id = ANY(array(<子查询>))。 - undefined

Answer 3

'in' 是语法糖，你可以查看计划分析，会发现 'in' 会被转换为 =ANY('...,...')::yourType[]

- java developer 111

---

我不认为这是真的。我有一个查询，使用一个IN约束（来自子查询的值）执行需要26秒，但当我切换到一个= ANY(array(<subquery>))时，相同的查询只需要几百毫秒。查询计划明显发生了变化。PG 12.3 - undefined

第一次尝试是否有热缓存呢？运行两次并观察时间是否有差异。 - undefined