Re: ANSI Semantics with NULL and NOT IN
Date: Wed, 16 Sep 1992 20:36:34 GMT
Message-ID: <1992Sep16.203634.7411_at_tamsun.tamu.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 tried it on ORACLE and it gives "no rows sselected" in the 2nd case
Nakkala
-- ----------------------------------------------------------------------------- Venkatesh Nakkala nakkala_at_neuron.tamu.edu Off : (409)845-5481 Res : (409)268-7404 Dept. Of Comp Sci.,Texas A & M Univ. Fax : (409)847-8578Received on Wed Sep 16 1992 - 22:36:34 CEST