Paradox Community
Search:

 Welcome |  What is Paradox |  Paradox Folk |  Paradox Solutions |
 Interactive Paradox |  Paradox Programming |  Internet/Intranet Development |
 Support Options |  Classified Ads |  Wish List |  Submissions 


Interactive Paradox Articles  |  Beyond Help Articles  |  Tips & Tricks Articles  


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       DE
A count of values in the Suite field, as in:
SELECT count(suite)
  FROM addresses
Would 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.