Oracle IN Clause Loop Join to Work Around 1000 Limit

Question

So I looked at this post and this post and couldn't find a workaround for my situation. My conditions are as follows:

I have 7000+ conditions (all unique values) for the IN clause
I only have read-only priveleges for the corporate DB. I'm hesitant to try the temporary table solution from this post.

Is it possible to loop through a transaction to query all records that meet the 7000 IN conditions? My company uses Oracle.

The question you have linked has several alternative answers besides creating a temp table-- (or clause, union) you should use one of those — antlersoft, Oct 01 '14 at 19:40
You will need to create a temporary table, there is no work around. You could ask for permissions to create a table. — ZeroBased_IX, Oct 01 '14 at 19:43
Where do you store 7000+ values? Where are they coming from? — Rusty, Oct 01 '14 at 20:34
7000 values need to (ideally) go in the IN clause but obviously can't — , Oct 01 '14 at 21:18

score 0 · Accepted Answer · edited May 23 '17 at 10:26

There is a limit on an IN statement, you can deal with it in a number of ways. You will have to create a temporary table. This is a duplicate of: SQL IN Clause 1000 item limit

You can do a join on the data:

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

You can do an EXISTS

 SELECT  a.id 
 FROM asdf
 WHERE
  a.id EXISTS (
     SELECT b.id from bnml b 
     where b.id = a.id
 )

Oracle IN Clause Loop Join to Work Around 1000 Limit

1 Answers1