ANSI Semantics with NULL and NOT IN

From: Irwin Schafer <irwin_at_binky.Binky.COM>
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);
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")

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?

Irwin irwin_at_Binky.COM Received on Wed Sep 16 1992 - 04:40:53 CEST

Original text of this message