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?

Perfectionism

possible duplicate of Oracle SQL: How to use more than 1000 items inside an IN clause – Gardia

Apart from being duplicate, I wanted to let you know that the in clause limit is there for a reason. Its a extremely resource intensive query. You should do what @Jonathan has mentioned. – Beeswing

possible duplicate of How to put more than 1000 values into an Oracle IN clause – Testee

Answer 1

You should transform the IN clauses to INNER JOIN clauses.

You can transform a query like this one

SELECT  foo   
FROM    bar   
WHERE bar.stuff IN  
       (SELECT  stuff FROM asdf)

in a query like this other one.

SELECT  b.foo 
FROM    ( 
        SELECT  DISTINCT stuff 
        FROM    asdf ) a 
JOIN    bar b 
ON      b.stuff = a.stuff

You will also gain a lot of performance

Pricillaprick

Answer 2

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.

Chauffeur

+1 cool trick :) - tested with 10,001 elements - performance seems to suffer though – Lacerated

@Nitish my guess is that support for tuples came later and by then the developers were enlightened with the ZOI principle – Chauffeur

Anyone know how this can be implemented using JPA Query Language? – Surmise

@JeffreyKemp Oracle seems to limit to 70,000 in a tuple style in clause, still better than the regular in clause limitation. – Succoth

Maybe you mean 65535, @JeffreyKemp. asktom.oracle.com/pls/apex/…, last comment. "I tested and the limit for in-list of tuples is 65,535 - as you seem to suggest with your example. Same Oracle version as yours, 12.2.0.1..... Just making atomic elements into 1-tuples by wrapping them in parentheses doesn't work though; the tuples must be n-tuples with n >= 2. ....And Oracle has a hard limit of 2^16 - 1 = 65,535 component conditions for such compound conditions - regardless of what those conditions are" – Imprecise

Hi @WesternGun, not sure what you mean, commenting on this post from over 9 years ago... – Lacerated

Answer 3

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,....)

Alan

Answer 4

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.

Darcee

Answer 5

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
--...
)

Andriette

You should use UNION ALL instead of UNION and VALUES constructor instead of both of them. – Oesophagus

This answer is quite old -- use the VALUES table constructor instead – Oesophagus

@Oesophagus Oracle didn't list values as table constructor until 21c. Can you please share the link to the documentation? – Panicle

@Panicle -- you have to use UNION ALL in oracle – Oesophagus

@hogan Yes, this answer is about this, but your comment suggested to use values 5 years ago and half a year ago again. – Panicle

@Panicle -- answer is still bad -- UNION ALL should be used over UNION – Oesophagus