SQL IN Clause 1000 item limit

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.

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

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.

Collections can have more than 1000 items.

Create a collection data type:

CREATE TYPE number_list IS TABLE OF NUMBER; 

Then you can use the MEMBER OF operator:

SELECT * FROM table_name WHERE id MEMBER OF number_list(1,2,3,4 /*,...*/ ,7999,8000) 

or IN with a table collection expression:

SELECT * FROM table_name WHERE id IN ( SELECT COLUMN_VALUE FROM TABLE(number_list(1,2,3,4 /*,...*/ ,7999,8000)) ) 

or, some languages (such as Java - example) can pass arrays as SQL collections using bind variables:

SELECT * FROM table_name WHERE id MEMBER OF :your_array 

Oracle Collections

Create in database:

create or replace TYPE ID_ARR_TY IS TABLE OF NUMBER(19) 

On client (e.g. Java)

Long[] myArrayOfNumbers = {a quarter million number values}; OracleArray oracleArray = ((OracleConnection) myDbConn).createARRAY("ID_ARR_TY", myArrayOfNumbers); 

SQL prepared statement:

SELECT column_X, ... FROM my_table WHERE column_Y IN ( SELECT COLUMN_VALUE FROM TABLE ( ? ) ) stmt.setArray(1, oracleArray); ResultSet results = stmt.executeQuery(); 

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