Should I Use IN or ANY in PostgreSQL WHERE Clauses?

Use ANY instead of IN

in the WHERE clause

In addition to the traditional IN clause, you may consider using ANY to filter the results in the WHERE clause. PostgreSQL provides two syntax variants:

IN expression:

• expression IN (subquery)
• expression IN (value [, ...])

ANY expression:

• expression operator ANY (subquery)
• expression operator ANY (array expression)

While both methods can use subqueries, their second form differs in expectations.

How to choose?

Consider the following:

• ANY provides greater flexibility and can be used with any binary operator that returns a boolean value.
• IN is a special case of ANY and operates like = ANY.

Performance-wise, ANY is unlikely to be significantly faster than IN. The choice between the two mainly depends on the convenience of providing filtered values.

Subquery method:

If the filter value already exists in the database, using a subquery or joining the source table with the target table can improve efficiency.

Array method:

For best performance, provide large numbers from the client via an array, unnest() or join, or provide a table expression using VALUES.

= ANY syntax:

For array expressions, PostgreSQL accepts an array constructor (ARRAY[1,2,3]) or an array literal ('{1,2,3}'). For type safety, explicit type conversion can be used:

<code class="language-sql">ARRAY[1,2,3]::numeric[]
'{1,2,3}'::bigint[]</code>

Passing an array from Ruby:

To pass an array from Ruby, use the following syntax:

<code class="language-ruby">MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})</code>

The above is the detailed content of Should I Use IN or ANY in PostgreSQL WHERE Clauses?. For more information, please follow other related articles on the PHP Chinese website!