185
Chapter 8: Data Types, Expressions, and Scalar Functions
8
If you supply an integer value outside the bounds of the arry, NULL value is returned.Working with Nulls
The relational database model represents missing data using null. Technically, null means
“value absent,” and it’s commonly understood to mean “unknown.” In practice, null can
indicate that the data has not yet been entered into the database or that the column does
not apply to the particular row.Because null values are unknown, the result of any expression that includes null will also
have a value that is unknown. If the contents of a bank account are unknown, and its
funds are included in a portfolio, then the total value of the portfolio is also unknown. The
same concept is true in SQL, as the following code demonstrates.SELECT 1 + NULL;
Result:
NULLBecause nulls have such a devastating effect on expressions, some developers detest the
use of nulls. They develop their databases so that nulls are never permitted, and column
defaults supply surrogate nulls (blank, 0, or ‘n/a’) instead.Other database developers argue that an unknown value should be represented by a
zero or a blank just to make coding easier. Nulls are valuable in a database because
they provide a consistent method to identify missing data. And regardless of how miss-
ing data is represented in the database, certain types of queries often produce nulls
in the results, so it’s worthwhile to write code that checks for nulls and handles them
appropriately.An advantage to using nulls is that SQL Server’s AVG() and COUNT(column) aggregate functions automatically
exclude nulls from the calculation. If you use a surrogate null (for example, some IT shops use 0 or −999 to represent
missing numeric data), then every aggregate query must fi lter out the surrogate null or the results will be less than
accurate. In addition to the aforementioned advantages, you must also consider the fact that NULL does not con-
sume any space. As a result, if you have a very large table it would be more benefi cial to store all NULLS instead of
inserting a default value.Testing for Null
Because null represents a missing value, there is no way to know whether a null is equal
or unequal to a given value, or even to another null. Returning to the bank account
example, if the balance of account 123 is missing and the balance of account 234 is
missing, then it’s logically impossible to say whether the two accounts have an equal or
unequal balance.Consider this simple test that proves that null does not equal null:c08.indd 185c08.indd 185 7/30/2012 4:21:13 PM7/30/2012 4:21:13 PM
http://www.it-ebooks.info