Null (SQL): Difference between revisions

Revision as of 06:58, 12 August 2022

Revision as of 02:44, 27 September 2022

Line 9:

== History ==

E. F. Codd mentioned nulls as a method of representing missing data in the [[relational model]] in a 1975 paper in the ''FDT Bulletin of [[Association for Computing Machinery|ACM]]-[[SIGMOD]]''. Codd's paper that is most commonly cited in relation with the semantics of Null (as adopted in SQL) is his 1979 paper in the ''[[ACM Transactions on Database Systems]]'', in which he also introduced his [[Relational Model/Tasmania]], although much of the other proposals from the latter paper have remained obscure. Section 2.3 of his 1979 paper details the semantics of Null propagation in arithmetic operations as well as comparisons employing a [[Ternary logic|ternary (three-valued)]] logic when comparing to nulls; it also details the treatment of Nulls on other set operations (the latter issue still controversial today). In [[database theory]] circles, the original proposal of Codd (1975, 1979) is now referred to as "Codd tables".<ref name="Meyden"/> Codd later reinforced his requirement that all RDBMSs support Null to indicate missing data in a 1985 two-part article published in ''[[ComputerWorldComputerworld]]'' magazine.<ref>{{cite journal|last=Codd|first=E.F.|title=Is Your Database Really Relational?|journal=[[ComputerWorldComputerworld]]|date=October 14, 1985}}</ref><ref>{{cite journal|last=Codd|first=E.F.|title=Does Your DBMS Run By The Rules?|journal=[[ComputerWorldComputerworld]]|date=October 21, 1985}}</ref>

The 1986 SQL standard basically adopted Codd's proposal after an implementation prototype in [[IBM System R]]. Although [[Don Chamberlin]] recognized nulls (alongside duplicate rows) as one of the most controversial features of SQL, he defended the design of Nulls in SQL invoking the pragmatic arguments that it was the least expensive form of system support for missing information, saving the programmer from many duplicative application-level checks (see [[semipredicate problem]]) while at the same time providing the database designer with the option not to use Nulls if they so desire; for example, in order to avoid well known anomalies (discussed in the [[#missing-value semantics|semantics section]] of this article). Chamberlin also argued that besides providing some missing-value functionality, practical experience with Nulls also led to other language features which rely on Nulls, like certain grouping constructs and outer joins. Finally, he argued that in practice Nulls also end up being used as a quick way to patch an existing [[database schema|schema]] when it needs to evolve beyond its original intent, coding not for missing but rather for inapplicable information; for example, a database that quickly needs to support electric cars while having a miles-per-gallon column.<ref name="Chamberlin1998">{{cite book|author1=Don Chamberlin|title=A Complete Guide to DB2 Universal Database|url=https://books.google.com/books?id=hb4zskzHrWYC&pg=PA28|year=1998|publisher=Morgan Kaufmann|isbn=978-1-55860-482-7|pages=28–32}}</ref>