Selecting NULL

So I got a question from a developer today who was trying to SELECT NULL values from a table. As I have been asked about this many times in the past, I decided to write a little post about it.

mysql> SHOW CREATE TABLE testing_null \G
*************************** 1. row ***************************
Table: testing_null
Create Table: CREATE TABLE `testing_null` (
`id` int(11) unsigned NOT NULL auto_increment,
`mycolumn` varchar(12) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO testing_null (mycolumn) VALUES('ho'), (NULL), (NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM testing_null;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
| 2 | NULL |
| 3 | NULL |
+----+----------+
3 rows in set (0.00 sec)

Now the wrong way to query the table to find the NULL values (Some examples are really ridiculous to test for NULL but presented here any way):

mysql> SELECT * FROM testing_null WHERE mycolumn='NULL';
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn=NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn=\N;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE !mycolumn;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE !mycolumn != NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn != NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM testing_null WHERE mycolumn != '' ;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM testing_null WHERE mycolumn =0 ;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)

The problem is that while NULL represents a value that is unknown or missing, we cannot use the comparison operators to determine whether a value is NULL. The correct way to test for NULL or to query based on NULL is to use the "IS NULL" and "IS NOT NULL" operators:

mysql> SELECT * FROM testing_null WHERE mycolumn IS NULL ;
+----+----------+
| id | mycolumn |
+----+----------+
| 2 | NULL |
| 3 | NULL |
+----+----------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM testing_null WHERE mycolumn IS NOT NULL ;
+----+----------+
| id | mycolumn |
+----+----------+
| 1 | ho |
+----+----------+
1 row in set (0.00 sec)

Also see:

• Working with NULL in MySQL