Working with NULL

When MySQL wants to tell you that a field contains an unknown value, it uses the special value NULL. NULL does not mean "nothing" or "empty" - those are values in their own right. NULL simply means that MySQL does not know what value a field should be set to, and is used if you do not supply field values.

Take a look at this SQL query and result:

mysql> SELECT 1 < NULL, 1 > NULL, NULL = NULL, NULL != NULL;

+----------+----------+-------------+--------------+
| 1 < NULL | 1 > NULL | NULL = NULL | NULL != NULL |
+----------+----------+-------------+--------------+
|     NULL |     NULL |        NULL |         NULL |
+----------+----------+-------------+--------------+

As you can see, you cannot meaningfully use normal comparison operators with NULL values, because they will all return NULL. The reason for this is because NULL means "unknown", and MySQL does not know whether 1 is greater than an unknown value, whether one unknown value is equal to another unknown value, etc.

To solve this problem, MySQL has two special operators, IS NULL and IS NOT NULL that allow you to compare against the NULL value. Here is an example of them in use:

SELECT ID FROM usertable WHERE FirstName IS NULL;

NULL values can be very tricky to use, and you should try to avoid them if possible. Provide values for all your fields, even if they are empty values.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Default values >>

Previous chapter: Range matching

Jump to:

Home: Table of Contents