This behavior is consistent with MSSQL. EXISTS returns true for NULL fields in MSSQL 2005.
Sam ------------------------------------------- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 02, 2008 11:44 AM To: sqlite-users@sqlite.org Subject: [sqlite] EXISTS and NULLs The current behavior of SQLite is to not do anything special with NULLs in an EXISTS operator. For example: CREATE TABLE t1(x); INSERT INTO t1 VALUES(NULL); SELECT EXISTS(SELECT x FROM t1); The final SELECT above returns 1 (true) because an entry exists in t1, even though that entry is NULL. This makes logical sense because if you wanted to know if there were non-null entries you would say: SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL); But I have long ago learned that NULL values in SQL rarely make logical sense, so I figure I better check. Can somebody please confirm that this is the correct behavior and that EXISTS does not do any special treatment of NULL values? Can somebody tell me what MySQL, PostgreSQL, Oracle, and Firebird do in this case? -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
