IN vs. ANY in PostgreSQL: What are the Key Differences and When Should I Use Each?

The key differences and usage scenarios of IN and ANY in PostgreSQL

Understanding the IN and ANY structures

In PostgreSQL, IN and ANY are both constructs that perform set comparisons to determine whether a value exists in a set.

Logical equivalence

Logically, IN is equivalent to = ANY. However, their syntax and functionality differ.

Grammar Variations

IN and ANY each have two syntax variants:

• IN accepts an array of values ​​or a comma-separated list of values.
• ANY accepts a set of values ​​or an array of values.

Functional differences

1. Value passed: IN expects a collection or a list of values, while ANY expects an array (the actual array type).
2. Index usage: IN with a collection can use an index, while ANY with an array may not use an index in some cases.
3. Query Plan: IN and ANY produce different query plans under certain circumstances.

ADVANTAGES OF ANY

ANY offers greater flexibility as it can be combined with various operators, including =. For example:

<code class="language-sql">SELECT 'foo' LIKE ANY('{FOO,bar,%oo%}');</code>

Scale and Performance

For large numbers of values, using sets for both IN and ANY can improve performance.

Invert and Exclude

To find rows whose values ​​are not in the given array:

<code class="language-sql">SELECT * FROM tbl WHERE id <> ALL (ARRAY[1, 2]);</code>

All expressions in the above code block are equivalent and will exclude rows with id values ​​1 and 2.

Inclusion of NULL values

By default, rows with id NULL will be excluded. To include them, use the following expression:

<code class="language-sql">SELECT * FROM tbl WHERE (id = ANY ('{1, 2}')) IS NOT TRUE;</code>

The above is the detailed content of IN vs. ANY in PostgreSQL: What are the Key Differences and When Should I Use Each?. For more information, please follow other related articles on the PHP Chinese website!