15.00 - SQL Support for a Consistent Trivalent Logic - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

SQL Support for a Consistent Trivalent Logic

The treatment of nulls by SQL is not only inconsistent, but it is also sometimes incorrect from a real world perspective (see Date, 2005 or Date, 2007, for thorough reviews).

Rubinson (2007) argues that Date misinterprets the semantics of the example he uses to establish that SQL can return erroneous results from databases that contain nulls. After making the case that the example query Date uses incorrectly assumes 2VL, but that SQL actually uses 3VL and does return the correct, albeit confusing, result, Rubinson goes on to argue that the important point the error of interpretation Date makes demonstrates is that “SQL’s use of nulls and three‑valued logic introduces a startling amount of complexity into seemingly straightforward queries” and “since the presence of a single null value (sic) taints the entire database, one must generally assume three‑valued logic. Consequently, the burden is on us to carefully review our queries to ensure that they mean what we intend.” His overriding argument is that “[p]roper design techniques, then, naturally minimize the number of nulls in the database.” Both Date (2007b) and Grant (2007) respond that it is Rubinson, not Date, who misinterprets SQL semantics, while otherwise agreeing that Rubinson is correct in his argument that the use of 3VL in SQL is an error.

The purpose of this description of the problems inherent in SQL missing values is to help you to understand what the most important inconsistencies are and how you can avoid some of them through careful database design.