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