sqloracle-database

SQL IN Clause 1000 item limit

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?

Solution

  • 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