PostgreSQL 中的 IN 与 ANY 运算符 [英] IN vs ANY operator in PostgreSQL

问题描述

PostgreSQL 中的 IN 和 ANY 运算符有什么区别?
两者的工作机制似乎是一样的.谁能用例子解释一下?

What is the difference between IN and ANY operator in PostgreSQL?
The working mechanism of both seems to be the same. Can anyone explain this with an example?

推荐答案

(IN 和 ANY 都不是运算符".一个构造"或";语法元素".)

(Neither IN nor ANY is an "operator". A "construct" or "syntax element".)

逻辑上，引用手册:

IN 等价于 = ANY.

但是IN有两个语法变体和ANY的两个变体.详情:

But there are two syntax variants of IN and two variants of ANY. Details:

• 如何在 Rails 的 WHERE 子句中使用 ANY 而不是 IN?

IN 取一个set等价于= ANY取一个set，如下所示:

• postgreSQL - in vs any

但是每个的第二个变体并不等同于另一个.ANY 构造的第二个变体采用 array(必须是实际的数组类型)，而 IN 的第二个变体采用逗号 -分隔的值列表.这会导致传递值的不同限制，并且还可能在特殊情况下导致不同的查询计划:

But the second variant of each is not equivalent to the other. The second variant of the ANY construct takes an array (must be an actual array type), while the second variant of IN takes a comma-separated list of values. This leads to different restrictions in passing values and can also lead to different query plans in special cases:

• 索引未与 =any() 一起使用，但与 in 一起使用>
• 传递多个集合或数组一个函数的值
• 如何匹配数组中的元素复合类型?

• Index not used with =any() but used with in
• Pass multiple sets or arrays of values to a function
• How to match elements in an array of composite type?

ANY 结构更加通用，因为它可以与各种运算符结合使用，而不仅仅是 =.示例:

The ANY construct is far more versatile, as it can be combined with various operators, not just =. Example:

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

对于大量的值，为每个值提供一个集合更好:

For a big number of values, providing a set scales better for each:

• 使用大 IN 优化 Postgres 查询

相关:

• PostgreSQL 可以索引数组列吗?

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

"Find rows where id is in the given array":

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

反转:在数组中查找id 不是的行":

Inversion: "Find rows where id is not in the array":

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}'));

三个等价物.第一个带有 数组构造函数，另外两个带有 数组文字.数据类型可以明确地从上下文中导出.否则，可能需要显式转换，例如 '{1,2}'::int[].

All three equivalent. The first with array constructor, the other two with array literal. The data type can be derived from context unambiguously. Else, an explicit cast may be required, like '{1,2}'::int[].

具有 id IS NULL 的行不通过这些表达式中的任何一个.额外包含 NULL 值:

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

这篇关于PostgreSQL 中的 IN 与 ANY 运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！