All About NULL Values

Users new to the world of databases are often confused by a special value particular to our field – the NULL value. This value can be found in a field containing any type of data and has a very special meaning within the context of a relational database. It’s probably best to begin our discussion of NULL with a few words about what NULL is not:

• NULL is not the number zero.
• NULL is not the empty string (“”) value.

Rather, NULL is the value used to represent an unknown piece of data. Let’s take a look at a simple example: a table containing the inventory for a fruit stand. Suppose that our inventory contains 10 apples, 3 oranges. We also stock plums, but our inventory information is incomplete and we don’t know how many (if any) plums are in stock. Using the NULL value, we would have the inventory table shown at the bottom of this page.

It would clearly be incorrect to include a quantity of 0 for the plums record, because that would imply that we had no plums in inventory. On the contrary, we might have some plums, we’re just not sure.

Databases treat NULL values in a special way, depending upon the type of operation that it is used in. When a NULL value appears as an operand to an AND operation, the operation’s value is FALSE if the other operand is FALSE (there is no way the expression could be TRUE with one FALSE operand). On the other hand, the result is NULL (unknown) if the other operand is either TRUE or NULL (because we can’t tell what the result would be.)

The OR operand treats NULL values in a similar fashion. If the other operand is TRUE, the result of the OR is TRUE (because the real value of the NULL operand doesn’t matter.) On the other hand, if the other operand is either FALSE or NULL, the result of the OR operation is NULL.

There are two special operands used to test for the presence of the NULL value. ISNULL returns TRUE only when the supplied operand has a NULL value. Conversely, ISNOTNULL returns TRUE when the supplied operand does not have a NULL value. These are quite important functions. Avoid one of the most common database mistakes: testing an operand for a NULL value by comparing it to the empty string or zero is not correct!

That’s NULL in a nutshell! Take a few moments to review these concepts and get them straight in your head. You’ll be glad you did down the road!

Item	Quantity
Apples	10
Oranges	3
Plums	NULL