From: Michael Widenius
Date: March 30 1999 3:22pm
Subject: x NOT IN (y)  <=>  x != y
List-Archive: http://lists.mysql.com/mysql/1189
Message-Id: <14080.60165.453785.38870@monty.pp.sci.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

>>>>> "Corin" == Corin Hartland-Swann <cdhsm@stripped> writes:

Corin> Hi there,

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

Corin>    CREATE TABLE foo (bar int);

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

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

Corin> returns NULL and 2

Corin>    SELECT * FROM foo WHERE bar != 1;

Corin> returns 2

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

Corin> I'd always worked under the assumption that

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

Corin> Can anyone explain this?

Hi!

The rule is:  Any boolean expression with NULL will result in NULL:

In other words:

mysql> select NULL=NULL, NULL=1, NULL != 1;
+-----------+--------+-----------+
| NULL=NULL | NULL=1 | NULL != 1 |
+-----------+--------+-----------+
|      NULL |   NULL |      NULL |
+-----------+--------+-----------+
1 row in set (0.00 sec)

and:

mysql> select NOT NULL;
+----------+
| NOT NULL |
+----------+
|     NULL |
+----------+

The ONLY way you can test for a NULL value is:

mysql> select NULL IS NULL;
+--------------+
| NULL IS NULL |
+--------------+
|            1 |
+--------------+

I must agree that I don't know what

SELECT NULL IN (1)

should return;  I assume it should return NULL;  MySQL 3.22 returns 0
here which is probably wrong;  I will fix this in MySQL 3.23 !.

Regards,
Monty