home *** CD-ROM | disk | FTP | other *** search
- Xref: sparky comp.databases:6755 comp.databases.ingres:1374 comp.databases.oracle:1590 comp.databases.informix:1943 comp.databases.sybase:19
- Newsgroups: comp.databases,comp.databases.ingres,comp.databases.oracle,comp.databases.informix,comp.databases.sybase
- Path: sparky!uunet!elroy.jpl.nasa.gov!ames!pacbell.com!rtech!binky!irwin
- From: irwin@binky.Binky.COM (Irwin Schafer)
- Subject: ANSI Semantics with NULL and NOT IN
- Message-ID: <1992Sep16.024053.1744@binky.Binky.COM>
- Reply-To: irwin@binky.Binky.COM (Irwin Schafer)
- Organization: Binky, Inc.
- Date: Wed, 16 Sep 92 02:40:53 GMT
- Lines: 27
-
- 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
-