Re: ANSI Semantics with NULL and NOT IN

In article <1992Sep16.135608.13489@almserv.uucp> gaujxl@mercury.fnma.com (Jacob Lifshitz) writes: >In article <1992Sep16.024053.1744@binky.Binky.COM> irwin@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@Binky.COM >Kobi > I tried it on ORACLE and it gives "no rows sselected" in the 2nd case Nakkala -- ----------------------------------------------------------------------------- Venkatesh Nakkala nakkala@neuron.tamu.edu Off : (409)845-5481 Res : (409)268-7404 Dept. Of Comp Sci.,Texas A & M Univ. Fax : (409)847-8578