The working mechanism of the two seems to be the same. Can anyone explain this with an example?
INis equivalent to= ANY.
But there are two grammatical variants of IN, and two variants of ANY structure. Details:
> How to use ANY instead of IN in a WHERE clause with Rails?
Taking a group of IN() variants is equivalent to = ANY() taking a group, as shown below:
> postgreSQL – in vs any
But the second variant of each is not equivalent to the other. The second variant of the ANY construction takes an array (must be the actual array type), while the second variant of IN takes a comma-separated list of values. This leads to different restrictions on the value passed, and in special cases also leads to different query plans:
> Index not used with =any() but used with in
> Pass multiple sets or arrays of values to a function
The ANY structure is more versatile because it can be combined with various operators, not just =. LIKE Example:
SELECT'foo' LIKE ANY('{FOO,bar,%oo%}');
For a large number of values, each A better set of values:
> Optimizing a Postgres query with a large IN
Related:
> Can PostgreSQL index array columns?
Reverse/Against/Exclude
Reverse:
SELECT * FROM foo WHERE id = ANY (ARRAY[1, 2]) ;< /pre>"Find the row whose id is not in the array" – Yes:
SELECT * FROM foo WHERE id <> ALL (ARRAY [1, 2]);This is the same as:
SELECT * FROM foo WHERE NOT (id = ANY (ARRAY[1 , 2]));The row whose ID is IS NULL will not pass any of these expressions. In addition, the NULL value should be included:
SELECT * FROM foo WHERE (id = ANY (ARRAY[1, 2])) IS NOT TRUE;
PostgreSQL What is the difference between the IN and ANY operators?
The working mechanism of the two seems to be the same. Can anyone explain this with an example?
Logically, quoting the manual:
INis equivalent to= ANY.
But IN has There are two grammatical variants, and the ANY structure has two variants. Details:
> How to use ANY instead of IN in a WHERE clause with Rails?
Take a set of IN ()Variation is equivalent to = ANY() to take a group, as shown below:
> postgreSQL – in vs any
But the second variant of each is not equivalent In another. The second variant of the ANY construct uses an array (must be the actual array type), while the second variant of IN uses a comma-separated list of values. This will lead to different restrictions on the value passed, and in special Circumstances can also lead to different query plans:
> Index not used with =any() but used with in
> Pass multiple sets or arrays of values to a function
The ANY structure is more versatile because it can be used in combination with various operators, not just =. LIKE example:
SELECT'foo' LIKE ANY('{FOO,bar,%oo%}');
For a large number of values, provide a better set for each value:
> Optimizing a Postgres query with a large IN
Related:
> Can PostgreSQL index array columns?
Reverse/oppose/exclude
p>
Reverse:
SELECT * FROM foo WHERE id = ANY (ARRAY[1, 2]);
"The id is not found Rows in the array" – yes:
SELECT * FROM foo WHERE id < b><> ALL (ARRAY[1, 2]);
This is the same as:
SELECT * FROM foo WHERE < b>NOT (id = ANY (ARRAY[1, 2]));
A row with ID IS NULL will not pass any of these expressions. It must also contain a NULL value :
SELECT * FROM foo WHERE (id = ANY (ARRAY[1, 2])) IS NOT TRUE;