0

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?

user2824874
  • 199
  • 2
  • 5
  • 13
  • How hard did you look? It took me less than a minute with a google search string of "oracle list item limit". – Dan Bracuk Oct 10 '13 at 19:36

1 Answers1

2

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:

Community
  • 1
  • 1
Ben
  • 51,770
  • 36
  • 127
  • 149
  • So what would be best alternative if IN() is not sufficient? suppose if we have big query where one column ColumnA will have to consider many values (>1000) while during run time (I am talking about dynamically considering values). I dont know if I putting it correct, Example, – user2824874 Oct 11 '13 at 19:00