Null (SQL)

300px|thumb|right|The Greek lowercase [[Omega|omega (ω) character is used to represent Null in database theory.]]

In the SQL database query language, or is a special marker used to indicate that a data value does not exist in the database. Introduced by the creator of the relational database model, E. F. Codd, SQL null serves to fulfill the requirement that all true relational database management systems (RDBMS) support a representation of "missing information and inapplicable information". Codd also introduced the use of the lowercase Greek omega (ω) symbol to represent null in database theory. In SQL, <code>NULL</code> is a reserved word used to identify this marker.

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, in the question "How many books does Adam own?", the answer may be "zero" (the number of books is known to be zero) or "null" (the number of books is not known). In a database table, the 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.

In SQL, null is a marker, not a value. This usage differs from most programming languages, where a null value of a reference means it is not pointing to any object.

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 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 (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".

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 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 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.

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. Although various proposals have been made for resolving these issues, the complexity of the alternatives has prevented their widespread adoption.

Null propagation

Arithmetic operations

Because Null is not a data value, but a marker for an absent value, using mathematical operators on Null gives an unknown result, which is represented by Null. In the following example, multiplying 10 by Null results in Null:

10 * NULL -- Result is NULL

</syntaxhighlight>

This can lead to unanticipated results. For instance, when an attempt is made to divide Null by zero, platforms may return Null instead of throwing an expected "data exception division by zero". The following example demonstrates the Null result returned by using Null with the SQL <code>||</code> string concatenation operator.

'Fish ' || NULL || 'Chips' -- Result is NULL

</syntaxhighlight>

This is not true for all database implementations. In an Oracle RDBMS, for example, NULL and the empty string are considered the same thing and therefore 'Fish ' || NULL || 'Chips' results in 'Fish Chips'.

Comparisons with NULL and the three-valued logic (3VL)

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. The logical result of the expression below, which compares the value 10 to Null, is Unknown:

SELECT 10 = NULL -- Results in Unknown

</syntaxhighlight>

However, certain operations on Null can return values if the absent value is not relevant to the outcome of the operation. Consider the following example:

SELECT NULL OR TRUE -- Results in True

</syntaxhighlight>

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 three-valued logic (3VL). The rules governing SQL three-valued logic are shown in the tables below (p and q represent logical states)" The truth tables SQL uses for AND, OR, and NOT correspond to a common fragment of the Kleene and Łukasiewicz three-valued logic (which differ in their definition of implication; however, SQL defines no such operation).

{| class="wikitable"

! p !! q !! p OR q !! p AND q !! p = q

| || || || ||

{| class="wikitable"

! p !! NOT p

| ||

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>WHERE</code> clause causes the DML statement to act on only those rows for which the predicate evaluates to True. Rows for which the predicate evaluates to either False or Unknown are not acted on by <code>INSERT</code>, <code>UPDATE</code>, or <code>DELETE</code> DML statements, and are discarded by <code>SELECT</code> queries. Interpreting Unknown and False as the same logical result is a common error encountered while dealing with Nulls.

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:

{| class="wikitable"

! p !! p IS TRUE !! p IS NOT TRUE !! p IS FALSE !! p IS NOT FALSE !! p IS UNKNOWN || p IS NOT UNKNOWN

| || || || || || ||

The F571 feature is orthogonal to the presence of the Boolean datatype in SQL (discussed later in this article) and, despite syntactic similarities, F571 does not introduce Boolean or three-valued literals in the language. The F571 feature was actually present in SQL92, well before the Boolean datatype was introduced to the standard in 1999. The F571 feature is implemented by few systems, however; PostgreSQL is one of those implementing it.

The addition of IS UNKNOWN to the other operators of SQL's three-valued logic makes the SQL three-valued logic functionally complete, meaning its logical operators can express (in combination) any conceivable three-valued logical function.

On systems that do not support the F571 feature, it is possible to emulate IS UNKNOWN p by going over every argument that could make the expression p Unknown and test those arguments with IS NULL or other NULL-specific functions, although this may be more cumbersome.

Law of the excluded fourth (in WHERE clauses)

In SQL's three-valued logic the law of the excluded middle, p OR NOT p, no longer evaluates to true for all p. More precisely, in SQL's three-valued logic p OR NOT p is unknown precisely when p is unknown and true otherwise. Because direct comparisons with Null result in the unknown logical value, the following query

SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 );

</syntaxhighlight>

is not equivalent in SQL with

SELECT * FROM stuff;

</syntaxhighlight>

if the column x contains any Nulls; in that case, the second query would return some rows the first one does not return, namely all those in which x is Null. In classical two-valued logic, the law of the excluded middle would allow the simplification of the WHERE clause predicate, in fact its elimination. Attempting to apply the law of the excluded middle to SQL's 3VL is effectively a false dichotomy. The second query is actually equivalent with:

SELECT * FROM stuff;

-- is (because of 3VL) equivalent to:

SELECT * FROM stuff WHERE ( x = 10 ) OR NOT ( x = 10 ) OR x IS NULL;

</syntaxhighlight>

Thus, to correctly simplify the first statement in SQL requires that we return all rows in which x is not null.

SELECT * FROM stuff WHERE x IS NOT NULL;

</syntaxhighlight>

In view of the above, observe that for SQL's WHERE clause a tautology similar to the law of excluded middle can be written. Assuming the IS UNKNOWN operator is present, p OR (NOT p) OR (p IS UNKNOWN) is true for every predicate p. Among logicians, this is called law of excluded fourth.

There are some SQL expressions in which it is less obvious where the false dilemma occurs, for example:

SELECT 'ok' WHERE 1 NOT IN (SELECT CAST (NULL AS INTEGER))

UNION

SELECT 'ok' WHERE 1 IN (SELECT CAST (NULL AS INTEGER));

</syntaxhighlight>

produces no rows because <code>IN</code> translates to an iterated version of equality over the argument set and 1<>NULL is Unknown, just as a 1=NULL is Unknown. (The CAST in this example is needed only in some SQL implementations like PostgreSQL, which would reject it with a type checking error otherwise. In many systems plain SELECT NULL works in the subquery.) The missing case above is of course:

SELECT 'ok' WHERE (1 IN (SELECT CAST (NULL AS INTEGER))) IS UNKNOWN;

</syntaxhighlight>

Effect of Null and Unknown in other constructs

Joins

Joins evaluate using the same comparison rules as for WHERE clauses. Therefore, care must be taken when using nullable columns in SQL join criteria. In particular a table containing any nulls is not equal with a natural self-join of itself, meaning that whereas <math>R \bowtie R = R</math> is true for any relation R in relational algebra, a SQL self-join will exclude all rows having a Null anywhere. An example of this behavior is given in the section analyzing the missing-value semantics of Nulls.

The SQL <code>COALESCE</code> function or <code>CASE</code> expressions can be used to "simulate" Null equality in join criteria, and the <code>IS NULL</code> and <code>IS NOT NULL</code> predicates can be used in the join criteria as well. The following predicate tests for equality of the values A and B and treats Nulls as being equal.

(A = B) OR (A IS NULL AND B IS NULL)

</syntaxhighlight>

CASE expressions

SQL provides two flavours of conditional expressions. One is called "simple CASE" and operates like a switch statement. The other is called a "searched CASE" in the standard, and operates like an if...elseif.

The simple <code>CASE</code> expressions use implicit equality comparisons which operate under the same rules as the DML <code>WHERE</code> clause rules for Null. Thus, a simple <code>CASE</code> expression cannot check for the existence of Null directly. A check for Null in a simple <code>CASE</code> expression always results in Unknown, as in the following:

SELECT CASE i WHEN NULL THEN 'Is Null' -- This will never be returned

WHEN 0 THEN 'Is Zero' -- This will be returned when i = 0

WHEN 1 THEN 'Is One' -- This will be returned when i = 1

END

FROM t;

</syntaxhighlight>

Because the expression <code>i = NULL</code> evaluates to Unknown no matter what value column i contains (even if it contains Null), the string <code>'Is Null'</code> will never be returned.

On the other hand, a "searched" <code>CASE</code> expression can use predicates like <code>IS NULL</code> and <code>IS NOT NULL</code> in its conditions. The following example shows how to use a searched <code>CASE</code> expression to properly check for Null:

SELECT CASE WHEN i IS NULL THEN 'Null Result' -- This will be returned when i is NULL

WHEN i = 0 THEN 'Zero' -- This will be returned when i = 0

WHEN i = 1 THEN 'One' -- This will be returned when i = 1

END

FROM t;

</syntaxhighlight>

In the searched <code>CASE</code> expression, the string <code>'Null Result'</code> is returned for all rows in which i is Null.

Oracle's dialect of SQL provides a built-in function <code>DECODE</code> which can be used instead of the simple CASE expressions and considers two nulls equal.

SELECT DECODE(i, NULL, 'Null Result', 0, 'Zero', 1, 'One') FROM t;

</syntaxhighlight>

Finally, all these constructs return a NULL if no match is found; they have a default <code>ELSE NULL</code> clause.

IF statements in procedural extensions

SQL/PSM (SQL Persistent Stored Modules) defines procedural extensions for SQL, such as the <code>IF</code> statement. However, the major SQL vendors have historically included their own proprietary procedural extensions. Procedural extensions for looping and comparisons operate under Null comparison rules similar to those for DML statements and queries. The following code fragment, in ISO SQL standard format, demonstrates the use of Null 3VL in an <code>IF</code> statement.

IF i = NULL THEN

SELECT 'Result is True'

ELSEIF NOT(i = NULL) THEN

SELECT 'Result is False'

ELSE

SELECT 'Result is Unknown';

</syntaxhighlight>

The <code>IF</code> statement performs actions only for those comparisons that evaluate to True. For statements that evaluate to False or Unknown, the <code>IF</code> statement passes control to the <code>ELSEIF</code> clause, and finally to the <code>ELSE</code> clause. The result of the code above will always be the message <code>'Result is Unknown'</code> since the comparisons with Null always evaluate to Unknown.

Analysis of SQL Null missing-value semantics

The groundbreaking work of T. Imieliński and W. Lipski Jr. (1984) provided a framework in which to evaluate the intended semantics of various proposals to implement missing-value semantics, that is referred to as Imieliński-Lipski Algebras. This section roughly follows chapter 19 of the "Alice" textbook. A similar presentation appears in the review of Ron van der Meyden, §10.4.

CREATE TABLE t (

i INTEGER,

CONSTRAINT ck_i CHECK ( i < 0 AND i = 0 AND i > 0 ) );

</syntaxhighlight>

Because of the change in designated values relative to the clause, from a logic perspective the law of excluded middle is a tautology for constraints, meaning <code>CHECK (p OR NOT p)</code> always succeeds. Furthermore, assuming Nulls are to be interpreted as existing but unknown values, some pathological CHECKs like the one above allow insertion of Nulls that could never be replaced by any non-null value.

In order to constrain a column to reject Nulls, the <code>NOT NULL</code> constraint can be applied, as shown in the example below. The <code>NOT NULL</code> constraint is semantically equivalent to a check constraint with an <code>IS NOT NULL</code> predicate.

CREATE TABLE t ( i INTEGER NOT NULL );

</syntaxhighlight>

By default check constraints against foreign keys succeed if any of the fields in such keys are Null. For example, the table

CREATE TABLE Books

( title VARCHAR(100),

author_last VARCHAR(20),

author_first VARCHAR(20),

FOREIGN KEY (author_last, author_first)

REFERENCES Authors(last_name, first_name));

</syntaxhighlight>

would allow insertion of rows where author_last or author_first are irrespective of how the table Authors is defined or what it contains. More precisely, a null in any of these fields would allow any value in the other one, even on that is not found in Authors table. For example, if Authors contained only , then would satisfy the foreign key constraint. SQL-92 added two extra options for narrowing down the matches in such cases. If <code>MATCH PARTIAL</code> is added after the <code>REFERENCES</code> declaration then any non-null must match the foreign key, e.g. would still match, but would not. Finally, if <code>MATCH FULL</code> is added then would not match the constraint either, but would still match it.

Outer joins

270px|thumb|right|Example [[SQL outer join query with Null placeholders in the result set. The Null markers are represented by the word <code>NULL</code> in place of data in the results. Results are from Microsoft SQL Server, as shown in SQL Server Management Studio.]]

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. For left outer joins, for instance, Nulls are produced in place of rows missing from the table appearing on the right-hand side of the <code>LEFT OUTER JOIN</code> operator. The following simple example uses two tables to demonstrate Null placeholder production in a left outer join.

The first table (Employee) contains employee ID numbers and names, while the second table (PhoneNumber) contains related employee ID numbers and phone numbers, as shown below.

| valign="top" |

{| class="wikitable"

|+ Employee

! ID

! LastName

! FirstName

| 1

| Johnson

| Joe

| 2

| Lewis

| Larry

| 3

| Thompson

| Thomas

| 4

| Patterson

| Patricia

| valign="top" |

{| class="wikitable"

|+ PhoneNumber

! ID

! Number

| 1

| 555-2323

| 3

| 555-9876

The following sample SQL query performs a left outer join on these two tables.

SELECT e.ID, e.LastName, e.FirstName, pn.Number

FROM Employee e

LEFT OUTER JOIN PhoneNumber pn

ON e.ID = pn.ID;

</syntaxhighlight>

The result set generated by this query demonstrates how SQL uses Null as a placeholder for values missing from the right-hand (PhoneNumber) table, as shown below.

{| class="wikitable"

|+ Query result

! ID

! LastName

! FirstName

! Number

| 1

| Johnson

| Joe

| 555-2323

| 2

| Lewis

| Larry

| 3

| Thompson

| Thomas

| 555-9876

| 4

| Patterson

| Patricia

Aggregate functions

SQL defines aggregate functions 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.

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>:

{| class="wikitable" style="font-family:monospace"

! i

! j

| 150

| 200

| 250

| 0

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). A well-known side effect of this is that in SQL, <code>AVG(z)</code> is not equivalent with <code>SUM(z)/COUNT(*)</code> but with <code>SUM(z)/COUNT(z)</code>. This definition of not distinct allows SQL to group and sort Nulls when the <code>GROUP BY</code> clause (or another SQL language feature that performs grouping) is used.

Other SQL operations, clauses, and keywords using the "not distinct" definition in their treatment of Nulls include:

The <code>PARTITION BY</code> clause of the ranking and windowing functions such as <code>ROW_NUMBER</code>
The <code>UNION</code>, <code>INTERSECT</code>, and <code>EXCEPT</code> operators, which treat NULLs as the same for row comparison/elimination purposes
The <code>DISTINCT</code> keyword used in <code>SELECT</code> queries

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.

In cases where the index enforces uniqueness, NULLs are excluded from the index and uniqueness is not enforced between NULLs. Again, quoting from the PostgreSQL documentation:

This is consistent with the SQL:2003-defined behavior of scalar Null comparisons.

Another method of indexing Nulls involves handling them as not distinct in accordance with the SQL:2003-defined behavior. For example, Microsoft SQL Server documentation states the following:

Both of these indexing strategies are consistent with the SQL:2003-defined behavior of Nulls. Because indexing methodologies are not explicitly defined by the SQL:2003 standard, indexing strategies for Nulls are left entirely to the vendors to design and implement.

Null-handling functions

SQL defines two functions to explicitly handle Nulls: <code>NULLIF</code> and <code>COALESCE</code>. Both functions are abbreviations for searched <code>CASE</code> expressions.

NULLIF

The <code>NULLIF</code> function accepts two parameters. If the first parameter is equal to the second parameter, <code>NULLIF</code> returns Null. Otherwise, the value of the first parameter is returned.

NULLIF(value1, value2)

</syntaxhighlight>

Thus, <code>NULLIF</code> is an abbreviation for the following <code>CASE</code> expression:

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

</syntaxhighlight>

COALESCE

The <code>COALESCE</code> function accepts a list of parameters, returning the first non-Null value from the list:

COALESCE(value1, value2, value3, ...)

</syntaxhighlight>

<code>COALESCE</code> is defined as shorthand for the following SQL <code>CASE</code> expression:

CASE WHEN value1 IS NOT NULL THEN value1

WHEN value2 IS NOT NULL THEN value2

WHEN value3 IS NOT NULL THEN value3

...

END

</syntaxhighlight>

Some SQL DBMSs implement vendor-specific functions similar to <code>COALESCE</code>. Some systems (e.g. Transact-SQL) implement an <code>ISNULL</code> function, or other similar functions that are functionally similar to <code>COALESCE</code>. (See <code>Is</code> functions for more on the <code>IS</code> functions in Transact-SQL.)

NVL

The Oracle <code>NVL</code> function accepts two parameters. It returns the first non-NULL parameter or NULL if all parameters are NULL.

A <code>COALESCE</code> expression can be converted into an equivalent <code>NVL</code> expression thus:

COALESCE ( val1, ... , val{n} )

</syntaxhighlight>

turns into:

NVL( val1 , NVL( val2 , NVL( val3 , … , NVL ( val{n-1} , val{n} ) … )))

</syntaxhighlight>

A use case of this function is to replace in an expression a NULL by a value like in <code>NVL(SALARY, 0)</code> which says, 'if <code>SALARY</code> is NULL, replace it with the value 0'.

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.

Data typing of Null and Unknown

The <code>NULL</code> literal is untyped in SQL, meaning that it is not designated as an integer, character, or any other specific data type. Because of this, it is sometimes mandatory (or desirable) to explicitly convert Nulls to a specific data type. For instance, if overloaded functions are supported by the RDBMS, SQL might not be able to automatically resolve to the correct function without knowing the data types of all parameters, including those for which Null is passed.

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:

CAST (NULL AS INTEGER)

</syntaxhighlight>

represents an absent value of type INTEGER.

The actual typing of Unknown (distinct or not from NULL itself) varies between SQL implementations. For example, the following

SELECT 'ok' WHERE (NULL <> 1) IS NULL;

</syntaxhighlight>

parses and executes successfully in some environments (e.g. SQLite or PostgreSQL) which unify a NULL Boolean with Unknown but fails to parse in others (e.g. in SQL Server Compact). MySQL behaves similarly to PostgreSQL in this regard (with the minor exception that MySQL regards TRUE and FALSE as no different from the ordinary integers 1 and 0). PostgreSQL additionally implements a <code>IS UNKNOWN</code> predicate, which can be used to test whether a three-value logical outcome is Unknown, although this is merely syntactic sugar.

BOOLEAN data type

The ISO SQL:1999 standard introduced the BOOLEAN data type to SQL; however, it is still just an optional, non-core feature, coded T031.

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".

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.

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. 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.

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>). Null is defined by the SQL standard as different from both an empty string and the numerical value <code>0</code>, however. While Null indicates the absence of any value, the empty string and numerical zero both represent actual values.

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 found—regardless of whether rows with Nulls exist or not. The proposed way to retrieve rows with Nulls is the use of the predicate <code>IS NULL</code> instead of <code>= NULL</code>.

SELECT *

FROM sometable

WHERE num = NULL; -- Should be "WHERE num IS NULL"

</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. It is often incorrectly assumed that a missing value would be "less than" or "not equal to" a constant if that field contains Null, but, in fact, such expressions return Unknown. An example is below:

SELECT *

FROM sometable

WHERE num <> 1; -- Rows where num is NULL will not be returned,

-- contrary to many users' expectations.

</syntaxhighlight>

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.

Similarly, Nulls are often confused with empty strings. Consider the <code>LENGTH</code> function, which returns the number of characters in a string. When a Null is passed into this function, the function returns Null. This can lead to unexpected results, if users are not well versed in 3-value logic. An example is below:

SELECT *

FROM sometable

WHERE LENGTH(string) < 20; -- Rows where string is NULL will not be returned.

</syntaxhighlight>

This is complicated by the fact that in some database interface programs (or even database implementations like Oracle's), NULL is reported as an empty string, and empty strings may be incorrectly stored as NULL.

Criticisms

The ISO SQL implementation of Null is the subject of criticism, debate and calls for change. In The Relational Model for Database Management: Version 2, Codd suggested that the SQL implementation of Null was flawed and should be replaced by two distinct Null-type markers. The markers he proposed were to stand for "Missing but Applicable" and "Missing but Inapplicable", known as A-values and I-values, respectively. Codd's recommendation, if accepted, would have required the implementation of a four-valued logic in SQL. Others have suggested adding additional Null-type markers to Codd's recommendation to indicate even more reasons that a data value might be "Missing", increasing the complexity of SQL's logic system. At various times, proposals have also been put forth to implement multiple user-defined Null markers in SQL. Because of the complexity of the Null-handling and logic systems required to support multiple Null markers, none of these proposals have gained widespread acceptance.

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, 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. 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."

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. 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." 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.

References

External links

Oracle NULLs
The Third Manifesto
Implications of NULLs in sequencing of data
Java bug report about jdbc not distinguishing null and empty string, which Sun closed as "not a bug"

History

Null propagation

Arithmetic operations

Comparisons with NULL and the three-valued logic (3VL)

Effect of Unknown in WHERE clauses

Law of the excluded fourth (in WHERE clauses)

Effect of Null and Unknown in other constructs

Joins

CASE expressions

IF statements in procedural extensions

Analysis of SQL Null missing-value semantics

Outer joins

Aggregate functions

Null-handling functions

NULLIF

COALESCE

NVL

Data typing of Null and Unknown

BOOLEAN data type

Controversy

Common mistakes

Criticisms

Closed-world assumption

See also

References

Further reading

External links