![]() |
![]() |
|
![]() |
The Meaning of Nothing: NULL © 2002 Lawrence G. DiGiovanni Digico, Inc. www.digicoinc.com Preface The concept of NULL as a data value has significant implications that are sometimes overlooked in database development. This article seeks to describe the meaning of the NULL data value and how it affects data validation and computations. Introduction NULL literally means the absence of a value. There are several reasons why this might be the case in a database table field: The value is unknowable The value is unknown The value is non-applicable Unknowable values are those values for which the answer does not exist. In a data entry system, this commonly occurs after a revision to a system that produces new table fields that are relevant from a certain point in time onward. Unknown values are those values that have not been provided. For instance, in a system enrollment form, optional information may be solicited but not provided by the registered user, like home phone number. Non-applicable values are those attributes of an entity that are not present because the entity is of a certain type. For instance, in a table of employees that has a column for the name of the employee’s manager, that field would be NULL for the president of the company. Handling NULL As a result of the above three cases, it is generally inappropriate to make global assumptions about a NULL value in a field. Database systems generally treat operations involving NULL values in special ways that are a consequence of this ambiguity. These special ways are sometimes not intuitive, particularly to novice users. Generally speaking, the following rules apply: For simple arithmetic expressions, any arithmetic expression involving NULL always produces a NULL result. In the below examples, a represents a non-NULL numeric quantity that is otherwise appropriate for the operation in question: NULL + a produces NULL NULL - a produces NULL NULL * a produces NULL NULL / a produces NULL a / NULL produces NULL For comparisons, any comparison between a NULL value and any other value (including another NULL value) always produces FALSE. In the below examples, a represents a non-NULL value that is otherwise appropriate for the comparison in question: NULL = a returns FALSE NULL < a returns FALSE NULL > a returns FALSE NULL = NULL returns FALSE As a result of the above, NULLs also do not satisfy any join condition, unless the join condition explicitly accommodates for NULL. For aggregation operations, NULLs are not counted and are not weighed into Averages, Maximums, or Minimums. For example, given a table of scores: ID Score 1 100 2 50 3 <NULL>The average of the above scores is 100+50/2 = 75, not 100+50+0/3 = 50. For counting values, even string values, NULLs do not contribute to the count. For example, given a table of addresses: ID Street Suite State 1 123 Main St 102 MD 2 345 Elm St <NULL> VA 3 16 Plum St A DEA count of values in the Suite field, as in: SELECT count(suite) FROM addressesWould produce 2, not 3, since the one row has a NULL values for Suite. Paradox and NULL The above rules are generally true of most RDBMS platforms, though there are variations. Also, on some platforms, there are exceptions to these rules or ways to override them. Paradox, for example, provides the user and developer with a setting that determines how NULLs are to be handled - BlankAsZero. With BlankAsZero=False, calculations involving NULL will behave as shown above. With BlankAsZero=True, NULL in a numeric type of variable or column will be interpreted as the quantity 0. Query join, summary, and selection criteria are unaffected by this setting and will always behave according to the rules of NULLS shown above. Both QBE and SQL have special operators for testing for NULL values. Summary NULL is a special data value that signifies the absence of an actual value, either because the actual value is not knowable, unknown, or not applicable. This ambiguity requires that NULLs be treated differently than actual data values. Paradox has a special setting, BlankAsZero, that affects how some operations treat NULL values. Discussion of this article |
![]() Feedback | Paradox Day | Who Uses Paradox | I Use Paradox | Downloads ![]() |
|
![]() The information provided on this Web site is not in any way sponsored or endorsed by Corel Corporation. Paradox is a registered trademark of Corel Corporation. ![]() |
|
![]() Modified: 15 May 2003 Terms of Use / Legal Disclaimer ![]() |
![]() Copyright © 2001- 2003 Paradox Community. All rights reserved. Company and product names are trademarks or registered trademarks of their respective companies. Authors hold the copyrights to their own works. Please contact the author of any article for details. ![]() |
![]() |
|