SQL in a Nutshell

Processing NULLS

Most databases allow any of their supported datatypes to store NULL values. Inexperienced SQL programmers and developers tend to think of NULL as zero or blank. In fact, NULL is neither of these. In the relational database world, NULL literally means that the value is unknown or indeterminate. (This question alone — whether NULL should be considered unknown or indeterminate — is the subject of academic debate.) This differentiation enables a database designer to distinguish between those entries that represent a deliberately placed zero and those where either the data is not recorded in the system or where a NULL has been explicitly entered. For an example of this semantic difference, consider a system that tracks payments. A payment with a NULL amount does not mean that the product is free; instead, a NULL payment indicates that the amount is not known or perhaps not yet determined.

One side effect of the indeterminate nature of a NULL value is it cannot be used in a calculation or a comparison. Here are a few brief, but very important rules to remember about the behavior of NULL values:

• A NULL value cannot be inserted into a column defined as NOT NULL.

• NULL values are not equal to each other. It is a frequent mistake to compare two columns that contain NULL and expect the NULL values to match. (A NULL value can be identified in a WHERE clause or in a Boolean expression using phrases such as `value IS NULL’ and `value IS NOT NULL’.)

• A column containing a NULL value ...