Re: ANSI Semantics with NULL and NOT IN

irwin_at_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")
:
: 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

If anyone has a copy of ANSI X3.135-1989, take a look at the following sections:

           5.11  General rule # 2
           5.13  General rule # 2
           5.16  General rule # 2
           5.18  General rule # 2
           5.21  General rule # 2

Basically, 3 != NULL is UNKOWN (5.11) not false and you can take it from their

--
The last thing the bungee jumper heard: Gee, I thought I was next?
------------------------------------------------------------------------
Chris Amidei		| E-mail: camidei_at_ingres.com
Instructor        	|  Phone: (510)748-3261 - my desk
Ingres, an Ask Company	|       : (510)769-1400 - main line