The Meaning of Null in Databases and Programming Languages

Kenneth Baclawski
College of Computer and Information Science
Northeastern University
May 1, 2016

The meaning of null in relational databases is a major source of confusion not only among database users but also among database textbook writers. The main distinction between the relational null and the programming language null is the following: The relational null represents the absence of a value in a field of a record; whereas the programming language null represents one of the possible values of a variable. Succinctly,

The programming language null is a value but database null is not a value.

Consequently, the phrase "null value" is an oxymoron for databases. Unfortunately, a great many textbooks use this phrase. For example, Sciore [4] has a section entitled "Null Values", and he uses this phrase throughout the textbook. He also makes statements such as "Nulls behave strangely in queries." The strangeness is entirely due to mistakenly believing that database nulls are values. When one properly defines database null as the absence of a value, then the strangeness disappears. Another example is his statement, "A weird aspect of nulls is that they are neither equal nor unequal to each other." Once again, the weirdness is due to his misunderstanding, not anything intrinsic. When one understands that a database null is the absence of a value, then it is obvious that database nulls cannot be compared with one another. Only values can be compared with one another. Finally, he claims that "A null value denotes a value that does not exist or is unknown." The purpose of the rest of this article is to examine what database nulls could mean, and the conclusion is that any definition such as the one attempted by Sciore is inadequate and misleading.

For the rest of this article, I will use the convention that "NULL" (all uppercase letters) means "database null" and "null" (all lowercase letters) means "programming language null".

One consequence of defining NULL differently from null is that one cannot use ordinary Boolean logic. The result of a logical expression now has three possible values rather than just two. In addition to TRUE and FALSE, one must now admit the possibility of NULL. More precisely, there are two values, but there is also the possibility that an operand has no value. The resulting logic is called three-valued logic, while ordinary Boolean logic is two-valued logic. However, there are other reasons why one needs to use three-valued logic for databases so this is not an adequate argument against using NULL rather than null.

It might be helpful to mention some of the history that resulted in the database concept of NULL. The notion of NULL that is part of modern relational databases was introduced by Codd [1]. Ever since it was introduced, it was controversial. The main problem is that the concept is underspecified, resulting in inconsistent implementations. Even to the extent that it is specified, it is inconsistent, and explanations for the inconsistencies are not convincing. As a result of the difficulties with NULL, Date and Darwen have advocated in their Third Manifesto[3] that the concept be eliminated entirely from relational database systems, replacing it with the programming language concept.

Even Codd himself admitted that his concept of NULL was flawed, and he attempted to fix it by introducing two different concepts of NULL[2]. However, this suggestion was never implemented, largely because of the enormous increase in complexity that it would entail. For example, logic would have to increase from being three-valued to being four-valued. Furthermore, Codd's suggestion would not have fixed the various inconsistencies involving NULL. As we discuss below, it has the further disadvantage that there are many more than just two NULL concepts, and even the two NULL concepts suggested by Codd have their own subclassifications. Attempting to fix the problem by making more distinctions is clearly not a solution.

Unfortunately, NULL can arise for a great variety of reasons, and attempting to capture even a few of these in a language would be very difficult if not impossible. Here is a partial list of the reasons why NULL can occur.

  1. The field has a value but it isn't known. There are a number of variations on this. Here are a few of them:

    1. The value does exist somewhere, but the database does not have it.
    2. The value has not yet been determined, but it presumably will be determined at some point. For example, a customer has yet to make a decision about a feature.
    3. The value does exist in principle, and it might someday be determined, but there is no guarantee that it will ever be determined. For example, a field might have value 1 if P=NP and 0 if P≠NP.
    All of these may be regarded as examples of open world assumptions in the midst of a theory that is otherwise closed. They have the common feature that the field could, in principle, be specified with a value at some time in the future.

  2. The field has no value because it isn't applicable. Unlike #1 above, there is no possibility that such a field could ever have a value. This can happen for a variety of reasons.

    1. The field is not allowed to have a value. For example, the root node of a tree has no parent node. The fact that the field is NULL in this case is not due to missing information.
    2. The record was produced during an outer join operation. This is another example of a field that is required to be NULL. This idea is discussed in more detail in the next case below.

  3. A field value could have been inferred but was not inferred, because of an overriding requirement. This can happen in the case of an outer join. The outer join mandates that the additional (padded) columns of an unmatched record be NULL even when one could infer values for the columns. Consider the following example (in MySQL): 

    create table A(id int primary key, foo int default 0);
create table B(id int primary key, bar int default 0);
insert into A values(1, 10);
select * from A left join B on (A.id = B.id);
    The result is: 
    +----+-----+------+------+
| id | foo | id   | bar  |
+----+-----+------+------+
|  1 |  10 | NULL | NULL |
+----+-----+------+------+
    1. The second id field in the result must be NULL because the join condition did not hold in this case. It means that the A record was not matched with any B record. This is reasonable and represents the situation where a field cannot have a value.
    2. The bar field in the result could be inferred to be 0 because of the default value for this column. In spite of this, the bar field in the result is NULL. One could argue that this is also the situation where a field cannot have a value. In this case, there is no B record being joined, so it is not meaningful for it to have any values, even default ones. On the other hand, the usual meaning of a default value is that it is the value one should use in the absence of any other value being available. That certainly is true in this case, so there is a good argument for using the default value.

  4. The field has a value but it is not within the domain. For example, a form requesting an ethnic group does not include the one to which a person most closely identifies. The NULL in this case represents "none of the above" or "other". On such a form, NULL could also mean that one does not wish to answer the question, one does not know the answer, one has not yet answered the question, or one neglected to answer the question.

  5. The field value cannot be determined due to an exception. For example, dividing NULL by 0 is clearly an exception since division by zero is never defined for any number. However, because of the NULL propagation rules, the result of dividing NULL by 0 is NULL rather than a division by zero exception.

There are many other possibilities for the meaning of NULL. During query processing, computations use a three-valued logic which will introduce NULLs that have meanings that are subtly different from the meanings of the NULLs that occurred in the expressions being computed. Join processing is another example where the meaning of NULL ought to affect the processing. For example, if NULL means that the value has not yet been specified, then the result of join processing should be that there could be a match but it is not yet known. By contrast, if NULL means "none of the above", then one could argue that two NULLs should match one another.

One reason why the database notion of NULL is still useful in spite of its semantic shortcomings has to do with query optimization. Unlike programming languages which have relatively limited opportunities for optimization, there are substantial opportunities for optimizing queries. For example, a query that filters the results as the last step could be optimized so that the filter operation is performed much earlier in query processing, well before operations such as joins are performed. While this has important consequences for performance, it also means that standard programming language features like throwing and catching exceptions are no longer possible. By rearranging the processing steps the way databases do, one could be in a situation where the exception is caught at a different level than one would expect. In other words, the throw-catch notion is only meaningful for a specific execution plan, so throwing and catching exceptions within the processing of a query would mandate a specific execution plan and eliminate many possibilities for query optimization. Still another kind of optimization is parallel processing (via multiple threads or GPUs) to obtain query results. When an exception is thrown in a parallel context, it is unclear how the exception should be handled. Aside from the issue of the level at which the exception is caught, there is also the issue of whether other threads should be affected by the exception. Programming languages do not have this problem because they generally assume serial processing. The use of NULL and NULL propagation rules allows query processing to be optimized and parallelized, but at a cost:

  1. One must deal with a notion of NULL that differs from the one for programming languages.
  2. The logic is three-valued rather than two-valued.
  3. Exception handling is not supported in the same way as it is done in programming languages.

Hopefully, this short note will give one some idea of the difficulties faced by anyone attempting to specify the semantics of NULL in a way that satisfies all of the possible meanings and use cases, while being compatible with query optimization and parallelization opportunities.

Given that it is neither possible nor desirable to change NULL to be null, the question becomes what to do about it. The main difficulty with NULL is the confusion that results from the use of the same word for two entirely different concepts. The best way to improve the situation would be to use a different word. In UML, multiplicities are used to specify the number of values that an attribute (or association end) can have [5]. This is a very compact notation that specifies both whether the attribute is multivalued or single valued and whether the attribute is required or optional. Unfortunately, the UML standard does use the word "NULL" in its methods so UML does not completely eliminate the confusion.

A better approach is to use "NOT SET" instead of NULL as is done in CMIS [6]. This makes it very clear that the attribute has no value. It would also help prevent the confusion among textbook writers who would be unlikely to use the ungrammatical phrase "NOT SET value". In queries, the condition "is null" could be replaced with "is not set" and the condition "is not null" could be replaced with "is set". In create table statements, specifying that an attribute does not allow NULL could be specified by using the word "required", while an attribute that allows null could be specifed by the word "optional".

References

[1] E.F. Codd. "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM 13 (6): 377-387. doi:10.1145/362384.362685. (1970).

[2] E.F. Codd, The Relational Model for Database Management Version 2, Addison-Wesley, 1990 (ISBN 0201141922).

[3] H. Darwen and C.J. Date, Databases, Types, and The Relational Model: The Third Manifesto, 3rd edition, Addison-Wesley, 2006 (ISBN: 0-321-39942-0).

[4] E. Sciore, Database Design and Implementation, Wiley, 2008 (ISBN: 978-0-471-75716-0).

[5] UML Standard, http://www.omg.org/spec/UML/.

[6] CMIS Version 1.1, http://docs.oasis-open.org/cmis/CMIS/v1.1/CMIS-v1.1.html.