Types of Missing Values - Teradata Database

Teradata Database Design

Teradata Database
Release Number
English (United States)
Last Update
Product Category

Types of Missing Values

The following list touches on most of the common uses of SQL nulls:

  • Value is unknown
  • Value is not applicable
  • Value does not exist
  • Value is not defined
  • Value is not valid
  • Value is not supplied
  • Value is the empty set
  • The semantics, properties, and behavior of each of these null types are different, but SQL treats them identically, including all 14 of the ANSI/X3/SPARC “null manifestations” and their 8 submanifestations. Codd (1990) has proposed a revision of SQL logic that includes a second type of null: value not applicable, which would mean extending the current 3-valued first-order predicate logic of SQL (3VL, having the possible predicate evaluations TRUE, FALSE, and UNKNOWN, the latter called MAYBE by Codd) to a 4‑valued first‑order predicate logic (4VL, having the possible predicate evaluations TRUE, FALSE, UNKNOWN, and NOT APPLICABLE, the latter two called MAYBE BUT APPLICABLE (designated with an A‑mark) and MAYBE BUT NOT APPLICABLE (designated with an I‑mark), respectively).

    The problems with the 3VL now supported by SQL are well documented (see Date, 1990ab, 1992abc; McGoveran, 1993-94; Pascal, 2000), and the formal arguments used to justify the deprecation of 3VLs are not repeated in any detail here. Arguments supporting 3VL have also been made (see E.F. Codd in Codd and Date, 1993; Fesperman, 1998; Fesperman, 1998-2003; Johnston, 1995ab), and detailed rejoinders from 2VL (no nulls allowed) supporters have been published in response (see C.J. Date in Codd and Date, 1993; Date, Darwen, and McGoveran, 1995, 1997). It is perhaps worth noting that trivalent logics are categorized as deviant logics, meaning that they capture alternative forms of reasoning (see, for example, Haack, 1975; 1996).