How to Use Any Instead of in in a Where Clause

How to use ANY instead of IN in a WHERE clause?

There are two variants of IN expressions:

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

Similarly, two variants with the ANY construct:

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

A subquery works for either technique, but for the second form of each, IN expects a list of values (as defined in standard SQL) while = ANY expects an array.

Which to use?

ANY is a later, more versatile addition, it can be combined with any binary operator returning a boolean value. IN burns down to a special case of ANY. In fact, its second form is rewritten internally:

IN is rewritten with = ANY

NOT IN is rewritten with <> ALL

Check the EXPLAIN output for any query to see for yourself. This proves two things:

• IN can never be faster than = ANY.
• = ANY is not going to be substantially faster.

The choice should be decided by what's easier to provide: a list of values or an array (possibly as array literal - a single value).

If the IDs you are going to pass come from within the DB anyway, it is much more efficient to select them directly (subquery) or integrate the source table into the query with a JOIN (like @mu commented).

To pass a long list of values from your client and get the best performance, use an array, unnest() and join, or provide it as table expression using VALUES (like @PinnyM commented). But note that a JOIN preserves possible duplicates in the provided array / set while IN or = ANY do not. More:

• Optimizing a Postgres query with a large IN

In the presence of NULL values, NOT IN is often the wrong choice and NOT EXISTS would be right (and faster, too):

• Select rows which are not present in other table

Syntax for = ANY

For the array expression Postgres accepts:

• an array constructor (array is constructed from a list of values on the Postgres side) of the form: ARRAY[1,2,3]
• or an array literal of the form '{1,2,3}'.

To avoid invalid type casts, you can cast explicitly:

ARRAY[1,2,3]::numeric[]
'{1,2,3}'::bigint[]

Related:

• PostgreSQL: Issue with passing array to procedure
• How to pass custom type array to Postgres function

Or you could create a Postgres function taking a VARIADIC parameter, which takes individual arguments and forms an array from them:

• Passing multiple values in single parameter

How to pass the array from Ruby?

Assuming id to be integer:

MyModel.where('id = ANY(ARRAY[?]::int[])', ids.map { |i| i})

But I am just dabbling in Ruby. @mu provides detailed instructions in this related answer:

• Sending array of values to a sql query in ruby?

Difference between IN and ANY operators in SQL

SQL>
SQL> -- Use the ANY operator in a WHERE clause to compare a value with any of the values in a list.
SQL>

SQL> -- You must place an =, <>, <, >, <=, or >= operator before ANY.

SQL> SELECT *
  2  FROM employee
  3  WHERE salary > ANY (2000, 3000, 4000);

For In Operator

SQL> -- Use the IN operator in a WHERE clause to compare a value with any of the values in a list.
SQL> SELECT *
  2  FROM employee
  3  WHERE salary IN (2000, 3000, 4000);

But with the IN operator you cannot use =, <>, <, >, <=, or >=

Can I use where clause instead of AND in given SQL query? If yes, whats the difference in both?

They come to the same thing.

However, it is generally considered good practice to use join clauses only for specifying joins, and keep filter predicates in the where clause, hence:

select first_name, incentive_amount
from   employee e
       join incentives i
            on  i.employee_ref_id = e.employee_id
where  i.incentive_amount > 3000

(Note that the inner and outer keywords are optional and in my view redundant clutter, so I never use them.)

In the case of outer joins (left join, or if you absolutely must, left outer join), the whole idea of separating the filter predicates and placing them in the where clause goes out of the window, because (for example) if there is no incentive row then i.incentive_amount will be null and so any predicate whatsoever will exclude the row. Some say this is why ANSI join syntax is rubbish and the distinction between join and filter predicates is artificial and pointless, while others see it as a quirk of an otherwise helpful syntax.

select first_name, incentive_amount
from   employee e
       left join incentives i
            on  i.employee_ref_id = e.employee_id
            and i.incentive_amount > 3000

You could still follow the convention for inner joins in the same query, e.g:

select first_name, incentive_amount
from   employee e
       join departments d
            on d.department_id = e.department_id
       left join incentives i
            on  i.employee_ref_id = e.employee_id
            and i.incentive_amount > 3000
where  d.name = 'Philology'

Just to add, I agree with Jonathan Lewis that a and b are terrible aliases for employee and incentives. (By the way, why not employees or incentive?) In my version I have used the surely more readable e and i.

ALL operator in WHERE clause in Rails

That's a case of relational-division.

Actual table definitions (standard 1:n relationship, hidden by the Ruby ORM) will be something like this:

CREATE TABLE instructor_student (
   id serial PRIMARY KEY
   name ...
);

CREATE TABLE fees (
   id serial PRIMARY KEY
 , instructor_student_id integer NOT NULL REFERENCES instructor_student
 , course_type ...
 , monthly_detail date
 , UNIQUE (instructor_student_id, course_type, monthly_detail)
);

Your attempt at a query effectively tries to test each single row in fees against multiple values in the given array, which always fails while elements of the array are not identical. One value cannot be the same as multiple other values. You need a different approach:

SELECT instructor_student_id
FROM   fees
WHERE  course_type = ?
AND    monthly_detail = ANY(ARRAY[?]::date[])  -- ANY, not ALL!
GROUP  BY instructor_student_id
HAVING count(*) = cardinality(ARRAY[?]::date[]);

This is assuming distinct values in your array and unique entries in your table fees like enforced by the UNIQUE constraint I added above. Else, counts are not reliable and you have to use a more sophisticated query. Here is an arsenal of options:

• How to filter SQL results in a has-many-through relation

As you can see, I did not involve the table instructor_student at all. While referential integrity is enforced with a FK constraint (like it typically is), we can work with fees alone to determine qualifying instructor_student_id. If you need to fetch more attributes from the main table, do that in a 2nd step, like:

SELECT i.*  -- or whatever you need
FROM   instructor_student i
JOIN  (
   SELECT ...  -- query from above
   ) f ON f.instructor_student_id = i.id
;

PostgreSQL =ANY and IN

That's because IN (unlike ANY) does not accept an array as input. Only a set (from a subquery) or a list of values. Detailed explanation:

• How to use ANY instead of IN in a WHERE clause with Rails?

---

Related Topics