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 1:

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:

• IN vs. JOIN with large rowsets
• How does the IN predicate work in SQL?