IN vs. ANY in PostgreSQL: When Should You Use Each Operator?

Comparison of IN and ANY operators in PostgreSQL

Overview

PostgreSQL provides two similar structures, IN and ANY, for matching values ​​against sets or lists. Although their logical functionality is the same, there are significant differences in syntax and usage.

Syntax and matching

IN:

• Accepts a comma separated list of values.
• is equivalent to = ANY applied to the set of these values.

ANY:

• can accept both set and array.
• In addition to the equality operator (=), a wider range of operators are accepted.

Set matching

IN and ANY are equivalent for matching values ​​against sets. For example:

<code class="language-sql">SELECT * FROM table WHERE id IN (1, 2, 3);
SELECT * FROM table WHERE id = ANY('{1, 2, 3}');</code>

Array matching

However, there is an additional syntax variant of ANY that supports array matching. This allows you to compare the value to the actual array data type:

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

Query Optimization

In some cases, the choice between IN and ANY affects query optimization. For large collections, you may get better performance by passing the collection using an IN structure.

Other Features of ANY

• Versatility: Supports multiple operators such as LIKE or >.
• Negative match: You can use the NOT operator to exclude values ​​from an array (for example, id ALL (ARRAY[1, 2])).
• NULL handling: By default, NULL values ​​do not match ANY or IN expressions. To include NULL values, use IS NOT TRUE or IS DISTINCT FROM.

Example

To demonstrate these differences, consider the following example:

<code class="language-sql">SELECT * FROM table WHERE id IN ('1', '2', NULL, 3);
SELECT * FROM table WHERE id = ANY('{1, 2, NULL, 3}');</code>

IN expression will exclude rows with NULL values, while ANY expression will include them. Additionally, ANY expressions can use array-specific operators, such as id > ANY('{1, 2, 3}').

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