Re: ANSI Semantics with NULL and NOT IN
From: K.X. Saunders <kxs5829_at_ultb.isc.rit.edu>
Date: Fri, 18 Sep 1992 17:58:09 GMT
Message-ID: <1992Sep18.175809.2406_at_ultb.isc.rit.edu>
>In article <1992Sep16.135608.13489_at_almserv.uucp>, gaujxl_at_mercury.fnma.com (Jacob Lifshitz) writes...
>>In article <1992Sep16.024053.1744_at_binky.Binky.COM> irwin_at_binky.Binky.COM (Irwin Schafer) writes:
>>>Given table "r1":
>>>
>>> a1 a2 a3
>>> 1 A null
>>> 2 B 1
>>> 3 C 1
>>>
>>>On Ingres, this query returns the value "A"
>>> select a2 from r1 where a1 in
>>> (select distinct a3 from r1);
>>>
>>>However, this query returns no rows
>>> select a2 from r1 where a1 not in
>>> (select distinct a3 from r1);
>>>
>>>Where one may expect two rows ("B" and "C")
>>
>>I tried it on Sybase and it does return "B" and "C"
>>
>>>Irwin irwin_at_Binky.COM
>>Kobi
>>
>
>I've heard that the behavior of nulls is one of the things that can differ from
>on implementation of SQL to another, but I don't know who's doing it "right"
>and who's doing it "wrong".
Date: Fri, 18 Sep 1992 17:58:09 GMT
Message-ID: <1992Sep18.175809.2406_at_ultb.isc.rit.edu>
In article <16SEP199213450843_at_stars.gsfc.nasa.gov> thompson_at_stars.gsfc.nasa.gov (William Thompson, code 682.1, x2040) writes:
>In article <1992Sep16.135608.13489_at_almserv.uucp>, gaujxl_at_mercury.fnma.com (Jacob Lifshitz) writes...
>>In article <1992Sep16.024053.1744_at_binky.Binky.COM> irwin_at_binky.Binky.COM (Irwin Schafer) writes:
>>>Given table "r1":
>>>
>>> a1 a2 a3
>>> 1 A null
>>> 2 B 1
>>> 3 C 1
>>>
>>>On Ingres, this query returns the value "A"
>>> select a2 from r1 where a1 in
>>> (select distinct a3 from r1);
>>>
>>>However, this query returns no rows
>>> select a2 from r1 where a1 not in
>>> (select distinct a3 from r1);
>>>
>>>Where one may expect two rows ("B" and "C")
>>
>>I tried it on Sybase and it does return "B" and "C"
>>
>>>Irwin irwin_at_Binky.COM
>>Kobi
>>
>
>I've heard that the behavior of nulls is one of the things that can differ from
>on implementation of SQL to another, but I don't know who's doing it "right"
>and who's doing it "wrong".
Well, my SQL rdbms returns 'B' and 'C', which I think is the more intuitive answer.
However, the standard says that:
x not in (subquery) is equivalent to x <> all (subquery)
A comparison with a NULL expression always results in a false value, so the correct answer according to the standard is to return no rows.
- Kyle
-- +-"Remember, [MS]DOS is sort of a UNIX deviant already" - dmoscny_at_piglet.-----+ | // Only /\ kxs5829_at_ultb.isc.rit.edu cincinatti. | | \ // /--\MIGA kyle_at_nick.csh.rit.edu oh.us | | \X/ SQLdb - $40 rdbms Rochester Institute of Technology |Received on Fri Sep 18 1992 - 19:58:09 CEST