SQL PostgreSQL 中的 IN 与 ANY 运算符

IN vs ANY operator in PostgreSQL

提问by mrg

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

PostgreSQL 中的IN和ANY运算符有什么区别？
两者的工作机制似乎是一样的。任何人都可以用一个例子来解释这一点吗？

回答by Erwin Brandstetter

Logically, quoting the manual:

从逻辑上讲，引用手册：

INis equivalent to = ANY.

IN相当于= ANY。

But there are two syntax variantsof INand two variants of the ANYconstruct. Details:

但有两个语法变种的IN和的两种变体ANY结构。细节：

• How to use ANY instead of IN in a WHERE clause with Rails?

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

The IN ()variant taking a setis equivalent to = ANY()taking a set, as demonstrated here:

该IN ()变种采取了一系列相当于= ANY()服用一集，如下所示：

• postgreSQL - in vs any

• postgreSQL - 在与任何

But the second variant of each is notequivalent to the other. The second variant of the ANYconstruct takes an array(must be an actual array type), while the second variant of INtakes a comma-separated list of values. This leads to different restrictions in passing values and canalso 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

• 索引未使用=any()但与in
• 将多组或多组值传递给函数

---

---

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

该ANY构造的用途要广泛得多，因为它可以与各种运算符结合使用，而不仅仅是=. 示例LIKE：

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

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

对于大量值，为每个值提供一组更好的比例：

• Optimizing a Postgres query with a large IN

• 使用大 IN 优化 Postgres 查询

Related:

有关的：

• Can PostgreSQL index array columns?

• PostgreSQL 可以索引数组列吗？

Inversion / opposite / exclusion

反转/相反/排斥

The inversion of:

反转：

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

"find rows where idis notin the array"- is:

“找到行，其中id是不是数组中的”-是：

SELECT * FROM foo WHERE id <> ALL (ARRAY[1, 2]);

Which is the same as:

这与以下内容相同：

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

Rows with id IS NULLdo not pass either of these expressions. To include NULLvalues additionally:

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

SELECT * FROM foo WHERE (id = ANY (ARRAY[1, 2])) IS NOT TRUE;

回答by Manngo

There are two obvious points, as well as the points in the other answer:

有两个明显的观点，以及另一个答案中的观点：

• They are exactly equivalent when using sub queries:

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

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

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

On the other hand:

另一方面：

• Only the INoperator allows a simple list:

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

• 只有IN运算符允许一个简单的列表：

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

Presuming they are exactly the same has caught me out several times when forgetting that ANYdoesn't work with lists.

当忘记这ANY不适用于列表时，假设它们完全相同已经让我多次陷入困境。