where c not in (:b1, :b2, … , :b9999)

I do not like this kind of dynamic NOT IN clauses. It is better to have a temporary table, a bit like in

create global temporary table t(x number not null);
insert into t(x) values (:b1);
insert into t(x) values (:b2);
...
insert into t(x) values (:b9999);
select foo from bar where c not in (select x from t);

If you want to however do this in one query you can still use AND


SQL> select foo from bar where c not in (
:b1,
:b2,
...
:b9999);
*
ERROR at line 1002:
ORA-01795: maximum number of expressions in a list is 1000

SQL> select foo from bar where
c!=:b1 and
c!=:b2 and
...
c!=:b9999;

FOO
---
foo

Translate c NOT IN (exprlist) into c!=expr1 and c!=expr2…
Translate c IN (exprlist) into c=expr1 or c=expr2…

If you use dynamic expression list, this will bypass the ORA-01795 error

6 thoughts on “where c not in (:b1, :b2, … , :b9999)”

  1. Martin Preiss

    A fine solution. I think, that’s one of De Morgan’s laws: NOT (P OR Q) = (NOT P) AND (NOT Q)?

    But having written queries with IN-Lists of more than 1000 elements in the past, I think there are good reasons for ORA-01795 – because it’s a pain to read these queries …

    And I think there are also good reasons to be cautious with NOT IN at all because NOT IN (NULL) may lead to unpleasant effects …; and if my memory serves me well the CBO has it’s problems to compute the fitting prohability for IN-(or NOT IN-)lists.

    Regards
    MP

  2. John Flack

    There is a better way than using a temporary table, but it takes a little doing to set up. First, create a nested table type:
    CREATE TYPE vc_arraytype AS TABLE OF varchar2(4000);
    Now you can load a variable of that type with values – here’s a PL/SQL example, but you can also do this with an array in an embedded SQL environment – C, Java …
    DECLARE
    my_list VC_ARRAYTYPE;
    BEGIN
    FOR i IN 65..74 LOOP my_list(i):=CHR(i);END LOOP;
    FOR my_rec IN (SELECT * FROM my_table WHERE my_column IN my_list) LOOP
    — do stuff with my_rec
    NULL;
    END LOOP;
    END;
    If you are using C or some language other than PL/SQL, you may need to use the CAST function to cast your array as a VC_ARRAYTYPE in the WHERE clause.

  3. Laurent Schneider Post author

    yes, go for types if you use some advanced language like C, Java or PLSQL.

    the X NOT IN (1,NULL) does not differ from X!=1 AND X!=NULL (both conditions are NULL). I suppose the performance may be slighty better with NOT IN, at least the parsing will take less time if the code is shorter.

Comments are closed.