MySQL Select NULL

Written by on October 23, 2008 in MySQL - 4 Comments

How to select rows from MySQL table where certain field is empty is a problem i stuggled against for a while like … 10 minutes or so, while solution is quite simple. Remember that it will work with MySQL this solution was not tested with other databases and as far as i am concerned it does not work with Sybase.

SELECT * FROM `table` WHERE `field1` IS NULL

There is also variations for this:

SELECT * FROM `table` WHERE ISNULL(`field1`)

While browsing MySQL documentation i found one more interesting function IFNULL(expr1, expr2). What it basically do is: if expr1 is NULL then it returns expr2 else it returns expr1. Here is a sample usage:

mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'

That’s it for now, i hope you will find it helpful.

About the Author

Greg Winiarski is a freelance PHP and JavaScript programmer. He specializes in web applications and WordPress development.

4 Comments on "MySQL Select NULL"

  1. blestab May 6, 2009 at 3:15 pm ·

    very useful, thank you so much

  2. Grunties May 22, 2009 at 1:28 pm ·

    MySQL people: So, what’s wrong with a good ol’ equals sign – you know, the little double-line thing we use for every other is-equal-to comparison we want to make in MySQL? If I can’t have an explanation of a damned good reason, can I have the last ten minutes of my life back please? and my serenity?

  3. mike June 3, 2009 at 12:18 pm ·

    very good and usefull article .Learning alot

  4. Marshi March 10, 2011 at 12:06 pm ·

    Thank you so much, I fuck with it around 1 hour :D

Leave a Comment