NOT IN and NULL

I posted about Unexpected results in June 2005. Here are more results from NOT IN and NULL

select * from dual
WHERE (1) NOT IN (SELECT NULL FROM DUAL);
no rows selected

select * from dual
WHERE (1,1) NOT IN (SELECT NULL,1 FROM DUAL);
no rows selected

However, and this surprised me,
select * from dual
WHERE (1,1) NOT IN (SELECT NULL,2 FROM DUAL);

D
-
X

:roll:

Probably the expression
where (a,b) not in (select c,d from t)
is translated into
where (a!=c or b!=d) — first row of t
and (a!=c or b!=d) — second row of t
— and …

Published by

Laurent Schneider

Oracle Certified Master

9 thoughts on “NOT IN and NULL”

  1. Any comparison between null and another value will return false, unless the operators are used is null or is not null

  2. the point is :

    (1,1) NOT IN (SELECT NULL,2 FROM DUAL);

    returns TRUE

    also note that Any comparison between null and another value will return false is inaccurate.

    
    begin
      case (1=null) 
        when TRUE then 
          dbms_output.put_line('true');
        when FALSE then 
          dbms_output.put_line('false'); 
        else 
          dbms_output.put_line('Not TRUE and not FALSE'); 
      end case;
    end;
    /
    
    Not TRUE and not FALSE
    

  3. My 2-cent is that any comparison with NULL actually does not yield anything simply because it could not be done. A NULL (not defined) cannot be compared or calculated. I see NULL as a state. That’s why it can be only be used with IS or IS NOT.

    Laurent, I agree with your assumption. Since the first comparison a!=c is not comparable due to value NULL is involved, so, I assume, it is ignored. However, the second comparison b!=d return TRUE, and that counts.

    Thanks for provoking our thinking. :-)

  4. Running it through explain plan comes up with interesting filter predicates (10.2.0.3):

    1 – filter( NOT EXISTS (SELECT /*+ */ 0 FROM “DUAL” “DUAL” WHERE
    NULL IS NOT NULL))
    3 – filter(NULL IS NOT NULL)

  5. Thanks for the hint of looking at the explain plan filter.

    If you try with something else than litteral, you would get a LNNVL

    
    SQL> select /*+ RULE */ * from t1 where (a,b) not in (select c,d from t2);
    
    no rows selected
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3597920939
    
    -----------------------------------
    | Id  | Operation          | Name |
    -----------------------------------
    |   0 | SELECT STATEMENT   |      |
    |*  1 |  FILTER            |      |
    |   2 |   TABLE ACCESS FULL| T1   |
    |*  3 |   TABLE ACCESS FULL| T2   |
    -----------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter( NOT EXISTS (SELECT 0 FROM "T2" "T2" WHERE
                  LNNVL("D"<>:B1) AND LNNVL("C"<>:B2)))
       3 - filter(LNNVL("D"<>:B1) AND LNNVL("C"<>:B2))
    
    Note
    -----
       - rule based optimizer used (consider using cbo)
    
    

  6. one more plan

    
    var a number
    var b number
    var w number
    var x number
    var y number
    var z number
    select * from dual where (:a,:b) not in ((:w,:x),(:y,:z));
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter((:Z<>B OR :Y<>A) AND (:X<>:B OR :W<>:A))
    

  7. select * from dual
    WHERE (1) NOT IN (SELECT NULL FROM DUAL);

    (1) NOT IN (SELECT NULL FROM DUAL)
    NOT ( 1 IN (SELECT NULL FROM DUAL))
    NOT (1=NULL)
    NOT (NULL)
    NULL
    So return now rows as only True returning rows.

    select * from dual
    WHERE (1,1) NOT IN (SELECT NULL,1 FROM DUAL);

    (1,1) NOT IN (SELECT NULL,1 FROM DUAL)
    NOT ((1,1) IN (SELECT NULL,1 FROM DUAL))
    NOT (1=NULL AND 1=1)
    NOT (NULL AND TRUE)
    NOT (NULL)
    NULL
    So return now rows as only True returning rows.

    select * from dual
    WHERE (1,1) NOT IN (SELECT NULL,2 FROM DUAL);

    (1,1) NOT IN (SELECT NULL,2 FROM DUAL)
    NOT ((1,1) IN (SELECT NULL,2 FROM DUAL))
    NOT (1=NULL AND 1=2)
    NOT (NULL AND FALSE)
    NOT (FALSE)
    TRUE
    So return rows.

    The above analysis is based on the fact that:
    NULL AND TRUE = NULL (1)
    NULL AND FALSE=FALSE (2)
    NULL OR TRUE=TRUE (3)
    NULL OR FALSE=NULL (4)

    Why is that?
    For ‘AND’ condition, the whole expression is TRUE only if all of them are TRUE, otherwise if any of them is FALSE, the whole expression will be FALSE, otherwise is NULL.
    So, TRUE AND TRUE AND TRUE = TRUE
    TRUE AND FALSE AND NULL = FALSE
    TRUE AND NULL AND FALSE = FALSE
    TRUE AND NULL AND NULL = NULL
    Examples:
    /*TRUE AND TRUE AND TRUE = TRUE*/
    SELECT * FROM DUAL
    WHERE (1=1) AND (1=1) AND (1=1);
    –return 1 row
    /*TRUE AND FALSE AND NULL = FALSE*/
    SELECT * FROM DUAL
    WHERE (1=1) AND (1=2) AND (1=NULL);
    –return no row

    /*Not(TRUE AND FALSE AND NULL) = NOT (FALSE)=TRUE*/
    SELECT * FROM DUAL
    WHERE NOT((1=1) AND (1=2) AND (1=NULL));
    –return 1 row

    /*TRUE AND NULL AND FALSE = FALSE*/
    SELECT * FROM DUAL
    WHERE (1=1) AND (1=null) AND (1=2);
    –return no row

    /*NOT(TRUE AND NULL AND FALSE) = NOT(FALSE)=TRUE*/
    SELECT * FROM DUAL
    WHERE NOT((1=1) AND (1=null) AND (1=2));
    –return 1 row

    /*NOT(TRUE AND NULL AND NULL) = NOT(NULL)=NULL*/
    SELECT * FROM DUAL
    WHERE NOT((1=1) AND (1=null) AND (1=null));
    –return no row

    For ‘OR’ condition, the whole expression is FALSE if any of them is FALSE, otherwise if all of them is TRUE, the whole expression is TRUE, otherwise is NULL.
    So, TRUE OR FALSE OR NULL =FALSE
    FALSE OR TRUE OR NULL = FALSE
    TRUE OR NULL OR TRUE = NULL
    TRUE OR TRUE OR TRUE = TRUE

    For examples:
    /*FALSE OR FALSE OR FALSE = FALSE*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=2) OR (1=2);
    –return no row

    /*NOT(FALSE OR FALSE OR FALSE) = NOT(FALSE)=TRUE*/
    SELECT * FROM DUAL
    WHERE NOT((1=2) OR (1=2) OR (1=2));
    –return 1 row

    /*FALSE OR TRUE OR NULL = TRUE*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=1) OR (1=NULL);
    –return 1 row

    /*FALSE OR NULL OR TRUE = TRUE*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=NULL) OR (1=1);
    –return 1 row

    /*FALSE OR NULL OR NULL = NULL*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=null) OR (1=null);
    –return no row

    /*NOT(FALSE OR NULL OR NULL) = NOT(NULL)=NULL*/
    SELECT * FROM DUAL
    WHERE NOT((1=2) OR (1=null) OR (1=null));
    –return no row

    Why is that? Because

  8. Please ignore previous post, this one is correct

    ———————————————–
    select * from dual
    WHERE (1) NOT IN (SELECT NULL FROM DUAL);

    /*(1) NOT IN (SELECT NULL FROM DUAL)
    NOT ( 1 IN (SELECT NULL FROM DUAL))
    NOT (1=NULL)
    NOT (NULL)
    NULL
    So return now rows as only True returning rows.*/

    select * from dual
    WHERE (1,1) NOT IN (SELECT NULL,1 FROM DUAL);

    /*(1,1) NOT IN (SELECT NULL,1 FROM DUAL)
    NOT ((1,1) IN (SELECT NULL,1 FROM DUAL))
    NOT (1=NULL AND 1=1)
    NOT (NULL AND TRUE)
    NOT (NULL)
    NULL
    So return now rows as only True returning rows.*/

    select * from dual
    WHERE (1,1) NOT IN (SELECT NULL,2 FROM DUAL);

    /*(1,1) NOT IN (SELECT NULL,2 FROM DUAL)
    NOT ((1,1) IN (SELECT NULL,2 FROM DUAL))
    NOT (1=NULL AND 1=2)
    NOT (NULL AND FALSE)
    NOT (FALSE)
    TRUE
    So return rows.*/

    /*The above analysis is based on the fact that:
    NULL AND TRUE = NULL (1)
    NULL AND FALSE=FALSE (2)
    NULL OR TRUE=TRUE (3)
    NULL OR FALSE=NULL (4)*/

    /*Why is that? Becuase:
    For ‘AND’ condition, the whole expression is TRUE only if all of them are TRUE,
    otherwise if any of them is FALSE, the whole expression will be FALSE, otherwise is NULL.

    TRUE AND TRUE AND TRUE = TRUE
    TRUE AND FALSE AND NULL = FALSE
    TRUE AND NULL AND FALSE = FALSE
    TRUE AND NULL AND NULL = NULL*/

    –Examples:

    /*TRUE AND TRUE AND TRUE = TRUE*/
    SELECT * FROM DUAL
    WHERE (1=1) AND (1=1) AND (1=1);
    –return 1 row
    /*TRUE AND FALSE AND NULL = FALSE*/
    SELECT * FROM DUAL
    WHERE (1=1) AND (1=2) AND (1=NULL);
    –return no row

    /*Not(TRUE AND FALSE AND NULL) = NOT (FALSE)=TRUE*/
    SELECT * FROM DUAL
    WHERE NOT((1=1) AND (1=2) AND (1=NULL));
    –return 1 row

    /*TRUE AND NULL AND FALSE = FALSE*/
    SELECT * FROM DUAL
    WHERE (1=1) AND (1=null) AND (1=2);
    –return no row

    /*NOT(TRUE AND NULL AND FALSE) = NOT(FALSE)=TRUE*/
    SELECT * FROM DUAL
    WHERE NOT((1=1) AND (1=null) AND (1=2));
    –return 1 row

    /*NOT(TRUE AND NULL AND NULL) = NOT(NULL)=NULL*/
    SELECT * FROM DUAL
    WHERE NOT((1=1) AND (1=null) AND (1=null));
    –return no row

    /*For ‘OR’ condition, the whole expression is FALSE if all of them are FALSE,
    otherwise if any of them is TRUE, the whole expression is TRUE, otherwise is NULL.

    FALSE OR FALSE OR FALSE = FALSE
    FALSE OR TRUE OR NULL = TRUE
    FALSE OR NULL OR TRUE = TRUE
    FALSE OR NULL OR NULL = NULL*/

    –For examples:
    /*FALSE OR FALSE OR FALSE = FALSE*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=2) OR (1=2);
    –return no row

    /*NOT(FALSE OR FALSE OR FALSE) = NOT(FALSE)=TRUE*/
    SELECT * FROM DUAL
    WHERE NOT((1=2) OR (1=2) OR (1=2));
    –return 1 row

    /*FALSE OR TRUE OR NULL = TRUE*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=1) OR (1=NULL);
    –return 1 row

    /*FALSE OR NULL OR TRUE = TRUE*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=NULL) OR (1=1);
    –return 1 row

    /*FALSE OR NULL OR NULL = NULL*/
    SELECT * FROM DUAL
    WHERE (1=2) OR (1=null) OR (1=null);
    –return no row

    /*NOT(FALSE OR NULL OR NULL) = NOT(NULL)=NULL*/
    SELECT * FROM DUAL
    WHERE NOT((1=2) OR (1=null) OR (1=null));
    –return no row

  9. Hi,
    Here is how I understand the result:
    NULL means that the value is unknown, so it can be any value, it is just that we don’t know it.
    We cannot say that (1) is not equal to a value we don’t know, because that unknown value could be 1
    We cannot say that (1,1) is not equal to (unknown,1) because that unknown value could be 1
    But we are sure that (1,1) is not equal to (unknown,2), whatever the unknown value is.
    Regards,
    Franck.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>