Database Reference
In-Depth Information
Figure 3.4. The nulls in this table will have an effect on mathematical operations in-
volving the table's fields.
Figure 3.5
helps to illustrate the effect nulls have on aggregate functions that incorporate
the values of a given field in a table. The result of an aggregate function, such as
C
OUNT
(<
fieldname
>), will be null if it is based on a field that contains null values. The
tablein
Figure3.5
showstheresultsofasummaryquerythatcountsthetotalnumberofoc-
currences of each category in the PRODUCTS table in
Figure 3.4
. The value of the T
OTAL
O
CCURRENCES
fieldistheresultofthefunctionexpressionC
OUNT
([C
ATEGORY
]).Noticethat
the summary query shows “0” occurrences of an unspecified category, implying that each
product has been assigned a category. This information is clearly inaccurate because there
are two products in the PRODUCTS table that have not been assigned a category.
Figure 3.5. Nulls affect the results of an aggregate function.
The issues of missing values, unknown values, and whether a value will be used in a math-
ematical expression or aggregate function are all taken into consideration in the database
design process, and we will revisit and discuss these issues further in later chapters.
