Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    1

    Unanswered: Problems with "SELECT..WHERE NOT IN" [Oracle 9i]

    Hi,

    I'm just wondering if anyone has noticed strange behaviour when performing a SELECT against items not matched by a subquery?

    I've just come across what I think is a bug with my version of Oracle 9i (9.2.0). When I run the following query...

    select distinct companyid from v_company
    where companyid not in
    (select distinct companyid from location);

    ... I get no results, despite the fact that the subquery (select distinct companyid from location) returns 19 rows, while the outer query (select distinct companyid from v_company) returns 44 rows.

    Now, the weird part: when I replace the subquery with a comma-delimited set of the 19 ids returned by "select distinct companyid from location", like so ...

    select distinct companyid from v_company
    where companyid not in ('CD01','DS01','DS02','DS03','DSM1','PC01','RB01', 'RB02','RB03','RI03','RIAA','RIHA','RILD','RISI',' RITD','RT01','RT02','_SCO');

    ... I get the 44 - 19 = 26 rows I was expecting.

    I've also noticed that the following syntax seems to work as expected:

    select distinct companyid from v_company
    minus
    select distinct companyid from location;

    ... so this is what I've decided to go with in my application.

    Though it's not critical for me, I'd really like to know what's going on here. I'd be very curious to hear if anyone else has had a similar problem.

    Many thanks,
    Warren

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    (select distinct companyid from location) does not return a comma separated list.

  3. #3
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    You have probably a row with a null in companyid in table location.

    SQL> select * from dual where 99 not in (1);

    DUM
    ---
    X

    SQL> select * from dual where 99 not in (99);

    no rows selected

    SQL> select * from dual where 99 not in (null,2);

    no rows selected

    This is a feature and not a bug. Reason is that "x NOT IN (a,b)" is equivalent to

    x != a and x != b

    If e.g. a is null, you have

    x != null and x != 2

    unknown and x != 2

    unknown

    Since "where unknown" is the same (i suppose by convention) as "where false", the row is not selected.
    I.e.

    SQL> select * from dual where 1 != null;

    no rows selected


    It's the way NULLs are supposed to work, they evaluate to UNKNOWN when compared with anything else:

    a = null -> unknown
    a != null -> unknown
    a < null -> unknown

    (whatever) and unknown -> unknown
    (whatever) or unknown -> (whatever)

    But
    true or unknown -> true
    false and unknown -> false

    I would suggest to play around with
    select * from dual where ...
    and so master the NULL - that way you will get Guru status with a little effort ;-)

    HTH
    AL

Posting Permissions

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