Content deleted Content added
m →When two nulls are equal: grouping, sorting, and some set operations: Minor corrections in English |
Moist hammer (talk | contribs) Punctuation, template & link corrections |
||
| (2 intermediate revisions by 2 users not shown) | |||
Line 2:
[[File:Db null.png|300px|thumb|right|The Greek lowercase [[Omega|omega (ω)]] character is used to represent Null in [[database theory]].]]
In [[SQL]], '''null''' or '''NULL''' is a special marker used to indicate that a data value does not exist in the [[database]]. Introduced by the creator of the [[Relational model|relational]] database model, [[E. F. Codd]], SQL null serves to fulfill the requirement that all ''true relational database management systems ([[Relational database#RDBMS|RDBMS]])'' support a representation of "missing information and inapplicable information".
A null should not be confused with a value of [[0]]. A null indicates a lack of a value, which is not the same as a zero value. For example, consider the question "How many books does Adam own?" The answer may be "zero" (we ''know'' that he owns ''none'') or "null" (we ''do not know'' how many he owns). In a database table, the [[Column (database)|column]] reporting this answer would start with no value (marked by null), and it would not be updated with the value zero until it is ascertained that Adam owns no books.
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 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 ''[[Computerworld]]'' magazine.<ref>{{cite journal |last=Codd |first=E.F. |author-link=Edgar F. Codd |date=October 14, 1985 |title=Is Your Database Really Relational? |journal=[[Computerworld]]
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, 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 that 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 |
Codd indicated in his 1990 book ''The Relational Model for Database Management, Version 2'' that the single Null mandated by the SQL standard was inadequate, and should be replaced by two separate Null-type markers to indicate why data is missing. In Codd's book, these two Null-type markers are referred to as 'A-Values' and 'I-Values', representing 'Missing But Applicable' and 'Missing But Inapplicable', respectively.<ref name="isbn0201141922"/>
=== Challenges ===
Line 27:
| pages =Section 6.2.6: ''numeric value expressions''
| no-pp =true
}}.</ref>
<syntaxhighlight lang="SQL">
Line 33:
</syntaxhighlight>
This can lead to unanticipated results.
<syntaxhighlight lang="SQL">
Line 41:
=== String concatenation ===
String [[concatenation]] operations, which are common in SQL, also result in Null when one of the operands is Null.<ref name="SQL2003-Part2-Sec628">
{{cite book |last=ISO/IEC |title=ISO/IEC 9075-2:2003, "SQL/Foundation" |publisher=ISO/IEC |year=2003 |pages=Section 6.2.8: ''string value expression'' |no-pp=true}}</ref> The following example demonstrates the Null result returned by using Null with the SQL <code>||</code> string concatenation operator.
<syntaxhighlight lang="sql">
Line 58 ⟶ 51:
{{Further|Three-valued logic}}
Since Null is not a member of any [[data ___domain]], it is not considered a "value", but rather a marker (or placeholder) indicating the [[undefined value]]. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.<ref name="SQL2003-Part1-Sec442">
{{cite book |last=ISO/IEC |url=http://www.iso.org |title=ISO/IEC 9075-1:2003, "SQL/Framework" |publisher=ISO/IEC |year=2003 |pages=Section 4.4.2: ''The null value'' |no-pp=true}}</ref> The logical result of the expression below, which compares the value 10 to Null, is Unknown:
<syntaxhighlight lang="SQL">
Line 80 ⟶ 65:
In this case, the fact that the value on the left of OR is unknowable is irrelevant, because the outcome of the OR operation would be True regardless of the value on the left.
SQL implements three logical results, so SQL implementations must provide for a specialized [[Ternary logic|three-valued logic (3VL)]].
{| class="wikitable"
Line 115 ⟶ 100:
=== Effect of Unknown in WHERE clauses ===
SQL three-valued logic is encountered in [[Data Manipulation Language]] (DML) in comparison predicates of DML statements and queries. The <code>
<syntaxhighlight lang="SQL">
Line 123 ⟶ 108:
</syntaxhighlight>
The example query above logically always returns zero rows because the comparison of the ''i'' column with Null always returns Unknown, even for those rows where ''i'' is Null.
=== Null-specific and 3VL-specific comparison predicates ===
Basic SQL comparison operators always return Unknown when comparing anything with Null, so the SQL standard provides for two special Null-specific comparison predicates. The <code>IS NULL</code> and <code>IS NOT NULL</code> predicates (which use a [[Reverse Polish notation|postfix]] syntax) test whether data is, or is not, Null.<ref name="SQL2003-Part2-Sec87">
{{cite book |last=ISO/IEC |title=ISO/IEC 9075-2:2003, "SQL/Foundation" |publisher=ISO/IEC |year=2003 |pages=Section 8.7: ''null predicate'' |no-pp=true}}</ref>
The SQL standard contains the optional feature F571 "Truth value tests" that introduces three additional logical unary operators (six in fact, if we count their negation, which is part of their syntax), also using postfix notation. They have the following truth tables:<ref>[[C.J. Date]] (2004), ''An introduction to database systems'', 8th ed., Pearson Education, p. 594</ref>
{| class="wikitable"
Line 149 ⟶ 127:
|}
The F571 feature is orthogonal to the presence of the [[#BOOLEAN data type|Boolean datatype]] in SQL (discussed later in this article) and, despite syntactic similarities, F571 does not introduce Boolean or three-valued [[Literal (computer programming)|literals]] in the language. The F571 feature was actually present in [[SQL92]],<ref name="MeltonSimon1993">{{cite book
The addition of IS UNKNOWN to the other operators of SQL's three-valued logic makes the SQL three-valued logic [[functionally complete]],<ref>C. J. Date, ''Relational database writings, 1991-1994'', Addison-Wesley, 1995, p. 371</ref> meaning its logical operators can express (in combination) any conceivable three-valued logical function.
On systems that
=== Law of the excluded fourth (in WHERE clauses) ===
Line 243 ⟶ 221:
====IF statements in procedural extensions====
[[SQL/PSM]] (SQL Persistent Stored Modules) defines [[Procedural programming|procedural]] extensions for SQL, such as the <code>[[Conditional (programming)|IF]]</code> statement.
<syntaxhighlight lang="plpgsql">
Line 254 ⟶ 232:
</syntaxhighlight>
The <code>IF</code> statement performs actions only for those comparisons that evaluate to True.
== {{anchor|missing-value semantics}} Analysis of SQL Null missing-value semantics ==
The groundbreaking work of [[Tomasz Imieliński|T. Imieliński]] and [[Witold Lipski|W. Lipski Jr.]] (1984)<ref name="JACM 1984">{{cite journal
=== In selections and projections: weak representation ===
Line 324 ⟶ 286:
</syntaxhighlight>
should include the possibility that a relation like EmpH22 may exist. However, Codd tables cannot represent the disjunction "result with possibly 0 or 1 rows". A device, mostly of theoretical interest, called [[conditional table]] (or c-table) can, however, represent such an answer:
{| class="wikitable"
Line 335 ⟶ 297:
|}
where the condition column is interpreted as the row
A weaker notion of representation is therefore desirable. Imielinski and Lipski introduced the notion of ''weak representation'', which essentially allows (lifted) queries over a construct to return a representation only for ''sure'' information, i.e. if it
: <math> \bigcap\mathop{\mathrm{Models}}(\bar{q}(T)) = \bigcap \{ q(R)\,| R \in \mathop{\mathrm{Models}}(T) \}</math>
The right-hand side of the above equation is the ''sure'' information, i.e. information which can be certainly extracted from the database regardless of what values are used to replace Nulls in the database. In the example we considered above, it
=== If joins or unions are considered: not even weak representation ===
Line 379 ⟶ 341:
|}
Thus when unions are added to the query language, Codd tables are not even a weak representation system of missing information, meaning that queries over them
For [[natural join]]s, the example needed to show that sure information may be unreported by some query is slightly more complicated. Consider the table
Line 431 ⟶ 393:
==Check constraints and foreign keys==
The primary place in which SQL three-valued logic intersects with SQL [[Data Definition Language]] (DDL) is in the form of [[check constraint]]s. A check constraint placed on a column operates under a slightly different set of rules than those for the DML <code>WHERE</code> clause. While a DML <code>WHERE</code> clause must evaluate to True for a row, a check constraint must not evaluate to False. (From a logic perspective, the [[designated value]]s are True and Unknown.) This means that a check constraint will succeed if the result of the check is either True or Unknown.
<syntaxhighlight lang="sql">
Line 461 ⟶ 423:
==Outer joins==
[[File:Sql query1.png|270px|thumb|right|Example [[SQL]] [[Join (SQL)|outer join]] query with Null placeholders in the result set.
SQL [[Join (SQL)|outer joins]], including left outer joins, right outer joins, and full outer joins, automatically produce Nulls as placeholders for missing values in related tables.
The first table ('''Employee''') contains employee ID numbers and names, while the second table ('''PhoneNumber''') contains related employee ID numbers and [[Telephone number|phone numbers]], as shown below.
Line 554 ⟶ 516:
==Aggregate functions==
SQL defines [[aggregate function]]s to simplify server-side aggregate calculations on data. Except for the <code>COUNT(*)</code> function, all aggregate functions perform a Null-elimination step, so that Nulls are not included in the final result of the calculation.<ref name="SQL2003-Part2-Sec4154">
{{cite book |last=ISO/IEC |title=ISO/IEC 9075-2:2003, "SQL/Foundation" |publisher=ISO/IEC |year=2003 |pages=Section 4.15.4: ''Aggregate functions'' |no-pp=true}}</ref>
Note that the elimination of Null is not equivalent to replacing Null with zero. For example, in the following table, <code>AVG(i)</code> (the average of the values of <code>i</code>) will give a different result from that of <code>AVG(j)</code>:
Line 583 ⟶ 538:
|}
Here <code>AVG(i)</code> is 200 (the average of 150, 200, and 250), while <code>AVG(j)</code> is 150 (the average of 150, 200, 250, and 0).
The output of an aggregate function can also be Null. Here is an example:
Line 606 ⟶ 561:
==When two nulls are equal: grouping, sorting, and some set operations==
Because [[SQL:2003]] defines all Null markers as being unequal to one another, a special definition was required in order to group Nulls together when performing certain operations.
Other SQL operations, clauses, and keywords using the "not distinct" definition in their treatment of Nulls include:
Line 623 ⟶ 571:
The principle that Nulls are not equal to each other (but rather that the result is Unknown) is effectively violated in the SQL specification for the <code>UNION</code> operator, which does identify nulls with each other.<ref name="Meyden"/> Consequently, some set operations in SQL, such as union and difference, may produce results not representing sure information, unlike operations involving explicit comparisons with NULL (e.g. those in a <code>WHERE</code> clause discussed above). In Codd's 1979 proposal (which was adopted by SQL92) this semantic inconsistency is rationalized by arguing that removal of duplicates in set operations happens "at a lower level of detail than equality testing in the evaluation of retrieval operations."<ref name="Klein"/>
The SQL standard does not explicitly define a default sorting order for Nulls.
==Effect on index operation==
Some SQL products do not index keys containing NULLs. For instance, [[PostgreSQL]]
{{quote|
B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these operators: < ≤ {{=}} ≥ >
Line 633 ⟶ 581:
}}
In cases where the index enforces uniqueness, NULLs are excluded from the index and uniqueness is not enforced between NULLs. Again, quoting from the
{{quote|
When an index is declared unique, multiple table rows with equal indexed values will not be allowed. Nulls are not considered equal. A multicolumn unique index will only reject cases where all of the indexed columns are equal in two rows.
Line 649 ⟶ 597:
==Null-handling functions==
SQL defines two functions to explicitly handle Nulls: <code>NULLIF</code> and <code>COALESCE</code>. Both functions are abbreviations for [[Case (SQL)|searched <code>CASE</code> expressions]].<ref name="SQL2003-Part2-Sec611">
{{cite book |last=ISO/IEC |title=ISO/IEC 9075-2:2003, "SQL/Foundation" |publisher=ISO/IEC |year=2003 |pages=Section 6.11: ''case expression'' |no-pp=true}}</ref>
=== NULLIF ===
The <code>NULLIF</code> function accepts two parameters.
<syntaxhighlight lang="sql">
Line 688 ⟶ 629:
</syntaxhighlight>
Some SQL DBMSs implement vendor-specific functions similar to <code>COALESCE</code>.
=== NVL ===
{{Redirect|NVL}}
The Oracle <code>NVL</code> function accepts two parameters.
A <code>COALESCE</code> expression can be converted into an equivalent <code>NVL</code> expression thus:
Line 705 ⟶ 646:
</syntaxhighlight>
A use case of this function is to replace in an expression a NULL by a value like in
There is, however, one notable exception. In most implementations, <code>COALESCE</code> evaluates its parameters until it reaches the first non-NULL one, while <code>NVL</code> evaluates all of its parameters. This is important for several reasons. A parameter ''after'' the first non-NULL parameter could be a function, which could either be computationally expensive, invalid, or could create unexpected side effects.
Line 711 ⟶ 652:
== Data typing of Null and Unknown ==
{{See also|Option type|Nullable type}}
The <code>NULL</code> [[Literal (computer programming)|literal]] is untyped in SQL, meaning that it is not designated as an integer, character, or any other specific [[data type]].<ref name="Understanding1999">{{cite book |
Conversion from the <code>NULL</code> literal to a Null of a specific type is possible using the <code>CAST</code> introduced in [[SQL-92]]. For example:
Line 730 ⟶ 671:
== BOOLEAN data type ==
The ISO [[SQL:1999]] standard introduced the BOOLEAN data type to SQL
When restricted by a <code>NOT NULL</code> constraint, the SQL BOOLEAN works like the [[Boolean type]] from other languages. Unrestricted, however, the BOOLEAN datatype, despite its name, can hold the truth values TRUE, FALSE, and UNKNOWN, all of which are defined as Boolean literals according to the standard. The standard also asserts that NULL and UNKNOWN "may be used interchangeably to mean exactly the same thing".<ref name="Date2011">{{cite book |first=C. |last=Date |author-link=Christopher J. Date |url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83 |title=SQL and Relational Theory: How to Write Accurate SQL Code |publisher=O'Reilly Media, Inc. |year=2011 |isbn=978-1-4493-1640-2 |page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>
The Boolean type has been subject of criticism, particularly because of the mandated behavior of the UNKNOWN literal, which is never equal to itself because of the identification with NULL.<ref name="Prigmore2007">{{cite book |
As discussed above, in the [[PostgreSQL]] implementation of [[SQL]], Null is used to represent all UNKNOWN results, including the UNKNOWN BOOLEAN. PostgreSQL does not implement the UNKNOWN literal (although it does implement the IS UNKNOWN operator, which is an orthogonal feature.) Most other major vendors do not support the Boolean type (as defined in T031) as of 2012.<ref>Troels Arvin, [http://troels.arvin.dk/db/rdbms/#data_types-boolean Survey of BOOLEAN data type implementation]</ref> The procedural part of Oracle's [[PL/SQL]], however, supports BOOLEAN variables; these can also be assigned NULL and the value is considered the same as UNKNOWN.<ref name="FeuersteinPribyl2009">{{cite book |
==Controversy==
===Common mistakes===
Misunderstanding of how Null works is the cause of a great number of errors in SQL code, both in ISO standard SQL statements and in the specific SQL dialects supported by real-world database management systems. These mistakes are usually the result of confusion between Null and either 0 (zero) or an empty string (a string value with a length of zero, represented in SQL as <code><nowiki>''</nowiki></code>).
A classic error is the attempt to use the equals operator <code>=</code> in combination with the keyword <code>NULL</code> to find rows with Nulls. According to the SQL standard this is an invalid syntax and shall lead to an error message or an exception. But most implementations accept the syntax and evaluate such expressions to <code>UNKNOWN</code>. The consequence is that no rows are
<syntaxhighlight lang="sql">
Line 751 ⟶ 691:
</syntaxhighlight>
In a related, but more subtle example, a <code>WHERE</code> clause or conditional statement might compare a column's value with a constant.
<syntaxhighlight lang="sql">
Line 762 ⟶ 702:
These confusions arise because the [[Law of Identity]] is restricted in SQL's logic. When dealing with equality comparisons using the <code>NULL</code> literal or the <code>UNKNOWN</code> truth-value, SQL will always return <code>UNKNOWN</code> as the result of the expression. This is a [[partial equivalence relation]] and makes SQL an example of a ''Non-Reflexive logic''.<ref>{{citation|title=Classical Logic or Non-Reflexive Logic? A case of Semantic Underdetermination|journal=Revista Portuguesa de Filosofia|volume=68|issue=1/2|last=Arenhart, Krause|year=2012|pages=73–86|jstor=41955624|doi=10.17990/RPF/2012_68_1_0073}}.</ref>
Similarly, Nulls are often confused with empty strings.
<syntaxhighlight lang="sql">
Line 773 ⟶ 713:
===Criticisms===
The ISO SQL implementation of Null is the subject of criticism, debate and calls for change.
[[Chris Date]] and [[Hugh Darwen]], authors of ''The Third Manifesto'', have suggested that the SQL Null implementation is inherently flawed and should be eliminated altogether,<ref name="3rdmanifesto">
{{cite web |last=Darwen |first=Hugh |author-link=Hugh Darwen |last2=Date |first2=Chris |author-link2=Christopher J. Date |title=The Third Manifesto |url=http://www.thethirdmanifesto.com/ |access-date=May 29, 2007 |website=thethirdmanifesto.com}}</ref> pointing to inconsistencies and flaws in the implementation of SQL Null-handling (particularly in aggregate functions) as proof that the entire concept of Null is flawed and should be removed from the relational model.<ref name="askew-wall">
{{cite web |last=Darwen |first=Hugh |author-link=Hugh Darwen |title=The Askew Wall |url=http://www.dcs.warwick.ac.uk/~hugh/TTM/TTM-TheAskewWall-printable.pdf |access-date=May 29, 2007 |website=dcs.warwick.ac.uk}}</ref> Others, like author [[Fabian Pascal]], have stated a belief that "how the function calculation should treat missing values is not governed by the relational model."{{citation needed|date=November 2012}}
===Closed-world assumption===
Another point of conflict concerning Nulls is that they violate the [[closed-world assumption]] model of relational databases by introducing an [[open-world assumption]] into it.<ref name="isbn0596100124">{{cite book |last=Date |first=Chris |author-link=Christopher J. Date |title=Database in Depth: Relational Theory for Practitioners |date=May 2005 |publisher=O'Reilly Media, Inc. |isbn=978-0-596-10012-4 |page=73}}</ref> The closed world assumption, as it pertains to databases, states that "Everything stated by the database, either explicitly or implicitly, is true; everything else is false."<ref name="cwa">{{cite web |last=Date |first=Chris |author-link=Christopher J. Date |title=Abstract: The Closed World Assumption |url=http://www.sfdama.org/ChrisDate_20070110.htm |archive-url=https://web.archive.org/web/20070519134146/http://www.sfdama.org/ChrisDate_20070110.htm |archive-date=2007-05-19 |access-date=May 29, 2007 |publisher=[[Data Management Association]], San Francisco Bay Area Chapter}}</ref> This view assumes that the knowledge of the world stored within a database is complete. Nulls, however, operate under the open world assumption, in which some items stored in the database are considered unknown, making the database's stored knowledge of the world incomplete.
==See also==
Line 821 ⟶ 731:
* [[Check constraint]]
* [[Relational Model/Tasmania]]
* [[
* [[Join (SQL)]]
Line 829 ⟶ 739:
==Further reading==
* E. F. Codd. Understanding relations (installment #7). FDT Bulletin of ACM-SIGMOD, 7(3-4):23–28, 1975.
* {{Cite journal |
* {{cite book |author-link=
* {{cite journal |
* Claude Rubinson, [http://www.u.arizona.edu/~rubinson/scrawl/Rubinson.2007.Nulls_Three-Valued_Logic_and_Ambiguity_in_SQL.pdf Nulls, Three-Valued Logic, and Ambiguity in SQL: Critiquing Date's Critique] {{Webarchive|url=https://web.archive.org/web/20160305071445/http://www.u.arizona.edu/~rubinson/scrawl/Rubinson.2007.Nulls_Three-Valued_Logic_and_Ambiguity_in_SQL.pdf |date=2016-03-05 }}, SIGMOD Record, December 2007 (Vol. 36, No. 4)
* John Grant, [http://www09.sigmod.org/sigmod/record/issues/0809/p23.grant.pdf Null Values in SQL]. SIGMOD Record, September 2008 (Vol. 37, No. 3)
* Waraporn, Narongrit, and Kriengkrai Porkaew. "[http://www.iaeng.org/IJCS/issues_v35/issue_3/IJCS_35_3_08.pdf Null semantics for subqueries and atomic predicates]". [[IAENG]] International Journal of Computer Science 35.3 (2008): 305-313.
* {{cite journal |
* Enrico Franconi and Sergio Tessaris, [http://ceur-ws.org/Vol-866/paper8.pdf On the Logic of SQL Nulls],
==External links==
| |||