Re: ANSI Semantics with NULL and NOT IN

In article <16SEP199213450843@stars.gsfc.nasa.gov> thompson@stars.gsfc.nasa.gov (William Thompson, code 682.1, x2040) writes: >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'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". Ingres and Oracle are "doing it right", Sybase is "doing it wrong". The behavior of Nulls is VERY clearly defined in the ANSI standard but Ingres' explanation quoted in another post is not exactly correct. The ANSI standard 1989 (section 5.13, page 37) states that "x IN s has the same result as x = ANY s and x NOT IN s has the same result as NOT(x IN s)". The issue here is that if you do SELECT DISTINCT blivet and blivet contains at least one row that has a NULL in it, comparing any value to the selected list will either return TRUE (a match is indeed found) or UNKNOWN (because comparing any value to NULL is UNKNOWN, not FALSE). Now, the semantics of boolean NULLs (section 5.18, page 43) state that NOT(UNKNOWN) is UNKNOWN. The essence of this is that you can't tell whether that NULL is B or C so therefore, it is incorrect to say that those values are NOT in A3. You just can't tell. This is one of the enigmas of NULLs: many people try to think of them as "no value" but what they really represent is "missing value". Red Brick Warehouse is consistent (like Oracle and Ingres) with ANSI SQL in this case. Sybase (not surprisingly), is not. Jon Rosen