SQL IN Clause 1000 item limit

Question

It is possible to put more than 1000 items in the SQL IN clause? We have been getting issues with our Oracle database not being able to handle it.

IF yes, how do we put more than 1000 items in the SQL IN clause?

IF not, what else can I do?

Answer 1

There's another workaround for this that isn't mentioned in any of the other answers (or other answered questions):

Any in statement like x in (1,2,3) can be rewritten as (1,x) in ((1,1), (1,2), (1,3)) and the 1000 element limit will no longer apply. I've tested with an index on x and explain plan still reports that Oracle is using an access predicate and range scan.

Answer 2

We can have more than one "IN" statement for the same variable.

For ex:

select val
 from table
where val in (1,2,3,...)
or
val in (7,8,9,....)

Answer 3

If you don't have the luxury of creating a temp table, you can simulate it using the WITH clause

with t as (
  select 1 val from dual 
  union all select 2 from dual
  union all select 3 from dual
    ...
  union all select 5001 from dual
  union all select 5002 from dual
)
select * 
  from mytable
 where col1 in (select val from t)

Obviously, you could also join mytable to t

I like Gordy's answer best, just showing another way.

Answer 4

Another way:

SELECT COL1, COL2, COL3 FROM YOUR_TABLE
WHERE 1=1
AND COL2 IN (
SELECT VAL1 as FAKE FROM DUAL
UNION
SELECT VAL2 as FAKE FROM DUAL
UNION
SELECT VAL3 as FAKE FROM DUAL
--...
)