how to add more than 1000 values with NOT IN clause
oracle, oracle11g, sql
Solution
You said you don't want to, but: use a temporary table. That's the correct solution here.
Query parsing is expensive in Oracle, and that's what you'll get when you put thousands of identifiers into a giant blob of SQL. Also, there are ill-defined limits on query length that you're going to hit. Doing an anti-JOIN against a table, on the other hand... Oracle is good at that. Bulk loading data into a table, Oracle is good at that too. Use a temp table.
Limiting `IN` to a thousand entries is a sanity check. The fact that you're hitting it means you're trying to do something insane.
Problem
I have comma delimited id's that I want to use in NOT IN clause.. I'm using oracle 11g. ``` select * from table where ID NOT IN (1,2,3,4,...,1001,1002,...) ``` results in ``` ORA-01795: maximum number of expressions in a list is 1000 ``` I don't want to use temp table. am trying considering doing this ``` select * from table1 where ID NOT IN (1,2,3,4,…,1000) AND ID NOT IN (1001,1002,…,2000) ``` Is there any other better workaround to this issue?