Re: [sqlite] EXISTS and NULLs
On Jan 2, 2008 5:44 PM, <[EMAIL PROTECTED]> wrote: > 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]> > > Firebird 2.0: SQL> CREATE TABLE t1(x INTEGER); SQL> INSERT INTO t1 VALUES(NULL); SQL> SELECT EXISTS(SELECT x FROM t1); Statement failed, SQLCODE = -104 Dynamic SQL Error -SQL error code = -104 -Token unknown - line 1, column 8 -EXISTS SQL> select count(*) from t1 where exists (select x from t1); COUNT 1 -- Nemanja Čorlija <[EMAIL PROTECTED]>
Re: [sqlite] EXISTS and NULLs
Oracle behavoir: SQL> create table t1 (x number); Table created. SQL> insert into t1 values (NULL); 1 row created. SQL> commit; Commit complete. SQL> SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL); SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL) * ERROR at line 1: ORA-00936: missing expression --- Revised Syntax --- The following returns -1 for the row when x is NULL SQL> select nvl(x,-1) from t1 where exists (select x from t1 where x is null); NVL(X,-1) -- -1 The is more equivalent meaning. SQL> select count(*) from t1 where exists (select x from t1 where x is not null); COUNT(*) -- 0 Hope that helps. Ken [EMAIL PROTECTED] wrote: 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 - To unsubscribe, send email to [EMAIL PROTECTED] - [EMAIL PROTECTED] wrote: 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 - To unsubscribe, send email to [EMAIL PROTECTED] - [EMAIL PROTECTED] wrote: 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 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] EXISTS and NULLs
Hi, MySQL 5.0.41 and Postgresql 8.2.5 work as you described in their treatment of NULL. There were some minor syntax tweaks for CREATE TABLE and the second SELECT EXISTS, but other than that it was true for the first SELECT EXISTS and false for the second SELECT EXISTS. Regards, Eugene Wee [EMAIL PROTECTED] wrote: 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] -
RE: [sqlite] EXISTS and NULLs
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] -
Re: [sqlite] EXISTS and NULLs
On Wed, 02 Jan 2008 16:44:12 +, [EMAIL PROTECTED] wrote: >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); > >Can somebody tell me what MySQL, PostgreSQL, Oracle, >and Firebird do in this case? Server version: 5.0.41-community-nt-log MySQL Community Edition (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE t1(x); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1 mysql> CREATE TABLE t1(x INTEGER); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO t1 VALUES(NULL); Query OK, 1 row affected (0.00 sec) mysql> SELECT EXISTS(SELECT x FROM t1); +--+ | EXISTS(SELECT x FROM t1) | +--+ |1 | +--+ 1 row in set (0.02 sec) -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
[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] -
