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.

 

Next chapter: Default values >>

Previous chapter: Range matching

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar