When Should I Use ANY Instead of IN in a WHERE Clause?

How to use ANY instead of IN in the WHERE clause

Suppose you have a query like MyModel.where(id: ids) in Rails that generates SQL using IN containing a list of IDs:

<code class="language-sql">SELECT "my_models".* FROM "my_models"
WHERE  "my_models".`"id"` IN (1, 28, 7, 8, 12)</code>

To use ANY instead of IN you can try:

<code class="language-ruby">MyModel.where("id = ANY(VALUES(#{ids.join '),('}))")</code>

However, when the ids array is empty, this approach fails and results in an SQL syntax error.

Understand IN and ANY

IN and ANY expressions have two forms:

• expression IN (subquery)
• expression IN (value [, ...]) or expression ANY (array expression)

For the second form, IN expects a list of values, while ANY expects an array.

When to use ANY and IN

ANY is a more general option that can be used with any operator that returns a boolean value. IN is a special case of ANY.

In terms of performance, ANY is never faster than = ANY, and = ANY is not much faster than IN. The choice should be based on convenience.

If the ID comes from a database, it is more efficient to use a subquery or JOIN. For best performance when passing long lists of values ​​from the client, consider using arrays, unnest() or VALUES expressions.

= Syntax of ANY

Postgres accepts the following forms of array expressions:

• Array constructor: ARRAY[1,2,3]
• Array literal: {1,2,3}

To avoid type conversion, cast the array explicitly: ARRAY[1,2,3]::numeric[].

How to pass array from Ruby

Assume id is an integer:

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

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