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]
-----------------------------------------------------------------------------
