Inconsistencies in How SQL Treats Nulls
Recall that the defined semantics for SQL nulls is as the simple representation for missing information (see “Semantics of SQL Nulls” on page 673). A null is to be interpreted as missing information and nothing more. There are a number of areas in which the SQL treatment of nulls is inconsistent with this definition, and the purpose of this section is to summarize those inconsistencies.
The following summary of the inconsistencies in the treatment of nulls by SQL is intended to assist you with the interpretation of results that might otherwise be misleading:
Furthermore, in set theory, ∅ = ∅, while in SQL, NULL ≠ NULL.
For example, the evaluation of an empty character string in SQL returns null rather than the mathematically correct empty set. Similarly, aggregation over empty sets report null, which is meant to mean that the value is missing, but there are known results having real values for such operations on empty sets.
The problem with empty sets is also true for the outer join, where the extended columns in the join result are denoted by nulls, but are actually empty sets. As a result, nulls are used to represent both empty sets and missing information in the same report.
There can be only one null UPI per table, and if a table defined with a NUPI has very many null primary indexes, the distribution of those rows across the AMPs will be very skewed.
COUNT(*), SQL aggregate operations ignore nulls, while SQL arithmetic operations do not.
You must explicitly place a null marker into the UDT column for the column “value” to be considered null.
The semantics of a null data type, whether partially or wholly null, are difficult to grasp. It can be said that a null attribute represents a missing type definition, but nulls are defined in ANSI/ISO SQL to represent missing values, not missing type definitions. The semantics of a UDT are what its designer defines them to be, of course, but from a logical perspective, it would seem that the best semantic definition of a null UDT would be undefined.
Using technology borrowed from object‑oriented programming languages, it might be said that nulls superficially appear to be overloaded in SQL because multiple markers having different semantics are all subsumed under the same name: null. However, unlike the case for overloaded functions in object‑oriented languages, it is not possible to discriminate among the various semantic possibilities a given null marker presents to a user or routine that must distinguish its intended semantics from among the myriad possible interpretations.