SQL дерекқорындағы NULL мәнінің мағынасы

Marker used in SQL databases to indicate a value does not exist

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 database model, E. F. Codd, SQL null serves to fulfil 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, NULL 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 thing 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 reporting this answer would start out 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 is quite different from most programming languages, where a null value of a reference means it is not pointing to any object.

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 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 (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, in order 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 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 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 the reason 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.

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

The ISO SQL:1999 standard introduced the BOOLEAN data type to SQL, however it's still just an optional, non core feature, coded T031. When restricted by a NOT NULL 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.

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

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.