Re: ANSI Semantics with NULL and NOT IN

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") : : 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@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@ingres.com Instructor | Phone: (510)748-3261 - my desk Ingres, an Ask Company | : (510)769-1400 - main line