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 ...
Get SQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.