NULL

NULL is a special database concept to represent the absence of value. It is neither a zero nor an empty string, but a special character that can be substituted for any data type. Nulls are usually used when the value is unknown or meaningless. NULL columns can later be updated with some real data.

For example, when a new employee is hired, he/she might neither yet have the phone number nor be assigned to a department. In such situation the NULL values are appropriate for PHONE and DEPARTMENT columns.

Another situation is when a value is nonapplicable; like STATE field in a European address. It can also be set to NULL.

The NULL value requires special handling and has to be dealt with carefully. In fact, any operator that involves NULL as an operand also returns NULL.

Caution

NULL can cause you serious troubles if not used properly. For example, imagine you have two columns in your table and you want to calculate the difference between them. If one of your columns has NULL values, the result of your calculation is undefined (NULL), i.e., 100 – 0 = 100, but 100 – NULL = NULL. That means you have to use special mechanisms (discussed in Chapters 10 and 11) to handle this and similar situations.