sqloracle11g

Limitation on IN()

What is the limitation of number of values that are passed inside IN() in SQL query? I have been looking around about this online but not found the answer I was looking for?

Solution

  • When explicitly stated the limit is 1,000, i.e.:

    select * from the_table where id in (1, 2, ..., 1000)

    This is in the documentation on the IN conditon:

    You can specify up to 1000 expressions in expression_list.

    When not explicitly stated there is no limit:

    select * from table1 where id in ( select id from table2 )

    Though useful there are often better ways of passing this many or more values to a SELECT. It might be worth considering a reference table of some description or JOIN.

    See also: