question about NOT IN
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: question about NOT IN

  1. #1
    Join Date
    Jan 2002
    Posts
    474

    question about NOT IN

    Let say I have two table named ONE and TWO:

    tables one one column TEST with 4 rows: A,B,C,null
    table two had one column TEST with 4 rows: A,B,C,D

    If I query :

    select test from TWO
    where test IN (select test from ONE)

    which returns A,B,C

    If I query

    select test from TWO
    where test NOT IN (select test from ONE)

    and I got the null value, why ???? I thought I will get the D value instead

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Expected. It's because of NULL.

    Code:
    sanjay@ORAM> select table_name from user_tables;
    
    TABLE_NAME
    ------------------------------
    PLAN_TABLE
    T1
    T2
    TEST
    TEST1
    
    sanjay@ORAM> select table_name from user_tables where table_name not in (null);
    
    no rows selected
    
    sanjay@ORAM>
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Sep 2000
    Posts
    305
    You can use NVL function or minus operator to get the correct result


    Shailendra

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    ....or "IS NOT NULL" clause
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Jan 2002
    Posts
    474
    I agree that I can use some other function to get the result I want, I was curious how does SQL work and the logic if someone can give the explanation.

    thanks

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I agree it's far from intuitive - I suppose the test for the row with D beomes:

    'D'<>'A' AND 'D'<>'B' AND 'D'<>'C' AND 'D'<>NULL

    'D'<>NULL is false (so is 'D'=NULL, and so is NOT 'D'=NULL etc etc) . . . .


    I always start by coding these as NOT EXISTS, which behaves as I expect - 9 times out of 10 it's faster too.
    Last edited by DaPi; 07-24-2003 at 10:26 AM.

  7. #7
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Originally posted by DaPi
    I always start by coding these as NOT EXISTS, which behaves as I expect - 9 times out of 10 it's faster too.
    Yep, me too!
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width