Null (SQL): Difference between revisions

Revision as of 13:07, 8 January 2024

Revision as of 02:41, 24 July 2024

Line 149:

|}

The F571 feature is orthogonal to the presence of the [[#BOOLEAN data type|booleanBoolean datatype]] in SQL (discussed later in this article) and, despite syntactic similarities, F571 does not introduce booleanBoolean or three-valued [[Literal (computer programming)|literals]] in the language. The F571 feature was actually present in [[SQL92]],<ref name="MeltonSimon1993">{{cite book|author1=Jim Melton|author2=Jim Melton Alan R. Simon|title=Understanding The New SQL: A Complete Guide|url=https://books.google.com/books?id=ZOOMSTZ4T_QC&pg=PA145|year=1993|publisher=Morgan Kaufmann|isbn=978-1-55860-245-8|pages=145–147}}</ref> well before the booleanBoolean 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]],<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.

Line 727:

</syntaxhighlight>

parses and executes successfully in some environments (e.g. [[SQLite]] or [[PostgreSQL]]) which unify a NULL booleanBoolean 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's still just an optional, non-core feature, coded T031.<ref name="ISO-9075-1">{{cite web |title=ISO/IEC 9075-1:1999 SQL Standard |year=1999 |publisher=ISO}}</ref>

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 booleanBoolean 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|author=C. Date|title=SQL and Relational Theory: How to Write Accurate SQL Code|url=https://books.google.com/books?id=Ew06OZtjuJEC&pg=PA83|year=2011|publisher=O'Reilly Media, Inc.|isbn=978-1-4493-1640-2|page=83}}</ref><ref>ISO/IEC 9075-2:2011 §4.5</ref>