Database Reference
In-Depth Information
4.9.1 The Value of Nulls
Missing values in a database are commonly the result of human error. Unknown values ap-
pear in a table for a variety of reasons. One reason may be that a specific value needed for a
field is yet not defined. Each null in the Sate Code field shows a missing or unknown Sate
Code for the record in which it appears.
4.9.2 The Problem with Nulls
Nulls adversely effect mathematical operations. An operation involving a null evaluates to
null. If a number is unknown then the result of the operation is obviously unknown. Fol-
lowing examples demon sate outcome of the operation with nulls :
(20 x 3) + 7 = 67
(Null x 7) + 8 = Null
(20 x Null) + 14 = Null
(23x 4) + Null = Null
Example 7
:
The Product table in following figure illustrates the effects of null values on mathematical
expressions. The value for the Total Value field is derived from the mathematical expres-
sion “[Price] x [Qty on Hand]”. Value for the Total Value field is missing where the Qty on
Hand value is null, resulting in a null value for the Total Value field as well. This leads to
a serious undetected error that occurs when all the values in the Total Value field are added
together: an inaccurate total. The only way to avoid this problem is to ensure that the values
for the Qty on Hand field cannot be null.
