ANSI Semantics with NULL and NOT IN
Date: Wed, 16 Sep 92 02:40:53 GMT
Message-ID: <1992Sep16.024053.1744_at_binky.Binky.COM>
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);
(select distinct a3 from r1);
However, this query returns no rows
select a2 from r1 where a1 not in
Where one may expect two rows ("B" and "C")
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? What do Sybase, Oracle, Informix do?
Thanks,
Irwin irwin_at_Binky.COM
Received on Wed Sep 16 1992 - 04:40:53 CEST