EXISTS and NULLs

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

EXISTS and NULLs

D. Richard Hipp
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 <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: EXISTS and NULLs

Kees Nuyt
On Wed, 02 Jan 2008 16:44:12 +0000, [hidden email] 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 [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: EXISTS and NULLs

Eugene Wee
In reply to this post by D. Richard Hipp
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

[hidden email] 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 <[hidden email]>
>



-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

RE: EXISTS and NULLs

Samuel Neff
In reply to this post by D. Richard Hipp

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 [hidden email]
 
-----Original Message-----
From: [hidden email] [mailto:[hidden email]]
Sent: Wednesday, January 02, 2008 11:44 AM
To: [hidden email]
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 <[hidden email]>


-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------

Reply | Threaded
Open this post in threaded view
|

Re: EXISTS and NULLs

ken-33
In reply to this post by D. Richard Hipp
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


[hidden email] 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 [hidden email]
-----------------------------------------------------------------------------




[hidden email] 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 [hidden email]
-----------------------------------------------------------------------------



[hidden email] 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 [hidden email]
-----------------------------------------------------------------------------


Reply | Threaded
Open this post in threaded view
|

Re: EXISTS and NULLs

Nemanja Corlija
In reply to this post by D. Richard Hipp
On Jan 2, 2008 5:44 PM,  <[hidden email]> 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 <[hidden email]>
>
>

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 <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: EXISTS and NULLs

Aristotle Pagaltzis
In reply to this post by D. Richard Hipp
* [hidden email] <[hidden email]> [2008-01-02 17:50]:
> If you wanted to know if there were non-null entries you would
> say:
>
>    SELECT EXISTS(SELECT x FROM t1 WHERE x NOT NULL);

In fact I usually say

    EXISTS ( SELECT NULL FROM ... )

in order to emphasize that the row data is of no interest in the
subquery in question.

> Can somebody please confirm that this is the correct behavior
> and that EXISTS does not do any special treatment of NULL
> values?

I have seen the above EXISTS SELECT NULL in several books, with
the collective implication that this construct must work in
MySQL, Postgres, Oracle, DB2, SQL Server and Sybase.

It’s a safe bet that SQLite works as expected.

Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>

-----------------------------------------------------------------------------
To unsubscribe, send email to [hidden email]
-----------------------------------------------------------------------------