Monday, August 07, 2006

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:

14 comments:

Sheeri said...

I don't know about the underlying calls, but you can also use:

WHERE ISNULL(mycolumn)

For all I know, under the hood it's the same thing.

Anonymous said...

Thank you for this comment. It is really usefull. It was also the second hit by google for 'mysql select null records'.
:-)

Eadz said...

Thanks for the write up, now it is #1 on google. Some more crazy ways to find null :

select * from table where column < 1

Anonymous said...

Good job people

Tobbi said...

Thank you, this post was very useful.

javs! said...

What if you had a column of type "text" (and not "varchar" like in your example)? Then apparently you cannot use IS NULL to test the column for a null value.
(I get an error message: Error: com.sybase.jdbc3.jdbc.SybSQLException: TEXT and IMAGE datatypes may not be used in a WHERE clause, except with the LIKE expression.
, SQL State: ZZZZZ, Error Code: 306)

Additionally, it seems that depending on the implementation a null value in a text column may be stored by the "null" string, which is evidently not null!

From what I gather, Sybase does not support the IS NULL operator for text fields. Using char_lenght() could work for me, as I know that when my field is not null it is a long string, but what if I didn't know this? Is there a way of effectively searching for null values in text fields?

Thanks in advance,

Javier Masides

Will said...

Thanks for the tip.

Ali said...

Thanks a lot, I was really struggling with this!

lone said...

Thanks alot for posting. I was freaked out for an hour trying = NULL and = null .. Man i tried every thing in my kitty. Thanks alot you saved you my life

lone said...

Thanks alot for the post. I was freaked out for an hour before fining you helping post. I tried NULL , null ,'' etc but still wasnt able to get the result. Thank you so much

Dave Smith said...

I am really curious why the following was never implemented:

SELECT * FROM myTable WHERE myColumn = NULL;

...just glad that someone posted the function to access null values though - even if it seems like a retarded hack.

Deepthi said...

thank you.This was a useful post

Anonymous said...

Another common problem with null values is selecting fields from the database and expecting null values to be returned with false ones. This blog explains the problem and proper solution:
http://www.tjbourke.com/4

Anonymous said...

Enuff with the thanks...