Re: ANSI Semantics with NULL and NOT IN

>From: uunet!binky.Binky.COM!irwin (Irwin Schafer) >Subject: ANSI Semantics with NULL and NOT IN >Date: 16 Sep 92 02:40:53 GMT >X-Informix-List-Id: <news.1810> > >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") Well, under Informix OnLine 4.10, the second select gives B and C as you expected. That was on both a database which was MODE ANSI and on a database which was not MODE ANSI. I would expect the same result under different versions of OnLine, and under Standard Engine. 27: create table r1 (a1 char(1) not null, a2 char(1) not null, a3 char(1)); 28: insert into r1 values ('1', 'A', null); 29: insert into r1 values ('2', 'B', 1); 30: insert into r1 values ('3', 'C', 1); 31: select a2 from r1 where a1 in (select distinct a3 from r1); A 32: select a2 from r1 where a1 not in (select distinct a3 from r1); B C 33: exit; >The Ingres explanation is that "this is ANSI semantics when NULL is >involved in the subselect, NOT IN is equivalent to !=ALL which >means the values must satisfy "!=" for all values in the subselect, >since, for example, 3 != NULL is FALSE given ANSI semantics, the qualification >is FALSE for all rows so zero rows are returned." > >Is this true? There is some logic behind the Ingres explanation, though it does not seem to be very helpful in practice. Nulls are nasty, and full of surprises. If you haven't already read any of C J Date's diatribes on the nastiness of nulls, especially as defined (or not) in the ANSI standard, then you should probably do so, and then avoid them like the plague. And you'll have to modify your select statement to read: select a2 from r1 where a1 not in (select distinct a3 from r1 where a3 is not null); This will work as required under both Informix and Ingres, and I assume under Oracle and Sybase. It is a nasty system dependency you have discovered; thanks for letting us know about it. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>