From: Corin Hartland-Swann
Date: March 30 1999 2:45pm
Subject: x NOT IN (y)  <=>  x != y
List-Archive: http://lists.mysql.com/mysql/1187
Message-Id: <Pine.LNX.4.05_heb2.07.9903301539080.591-100000@ebola.cthulhu.co.uk>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


Hi there,

While trying to get my head around NULL values, I stumbled on the
following:

   CREATE TABLE foo (bar int);

   INSERT INTO foo VALUES (NULL),(1),(2);

   SELECT * FROM foo WHERE bar NOT IN (1);

returns NULL and 2

   SELECT * FROM foo WHERE bar != 1;

returns 2

NULL is supposed to mean "unknown value", so surely there is no way of
knowing whether the row where bar = NULL is 1 or not 1.

I'd always worked under the assumption that

   x NOT IN (y)        <=>       x != y

Can anyone explain this?

Thanks in advance,

Corin

/---------------------------------------------\
|                                             |
|      Corin Hartland-Swann - Webmaster       |
|                                             |
+---------------------------------------------+
|                                             |
|      The International Lyrics Network       |
|                                             |
|           http://www.lyrics.net/            |
|                                             |
| mysql 3.22.20 - apache 1.3.4 - perl 5.00505 |
|                                             |
\---------------------------------------------/