From a068247e92e620455a925a0ae746adc225ae1339 Mon Sep 17 00:00:00 2001 From: "David G. Johnston" Date: Wed, 1 May 2024 07:45:48 -0700 Subject: [PATCH] Document NULL --- doc/src/sgml/filelist.sgml | 1 + doc/src/sgml/null.sgml | 79 ++++++++++++++++++++++++++++++++++++++ doc/src/sgml/query.sgml | 2 + 3 files changed, 82 insertions(+) create mode 100644 doc/src/sgml/null.sgml diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index 38ec362d8f..ac4fd52978 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -10,6 +10,7 @@ + diff --git a/doc/src/sgml/null.sgml b/doc/src/sgml/null.sgml new file mode 100644 index 0000000000..5f95b2494e --- /dev/null +++ b/doc/src/sgml/null.sgml @@ -0,0 +1,79 @@ + + Handling Unkowns (NULL) + + + NULL + + + + Looking again at our example weather data you will note that we do not know + the amount of precipitation Hayward. We communicated that implicitly by + not including the prcp column in the insert. Explicitly, we can communicate + this fact by writing. [example using null]. When a column is not specified + in an insert the default value for that column is recorded and the default + default value is NULL. + + + + As a value NULL crops up all throughout the database and interacts with many + features. The main portion of this book will detail the interactions specific + features have with NULL but it is helpful to have a reference page where one + can get an overview. + + + + First, like all values, NULLs are typed. But since any value can be unknown + NULL is a valid value for all data types. + + + + Second, when speaking generally NULL is assumed to mean unknown. However, + in practice meaning comes from context and so a model design may state that + NULL is to be used to represent "not applicable" - i.e., that a value is not + even possible. SQL has only the single value NULL while there are multiple + concepts that people have chosen to apply it to. In any case the behavior + of the system when dealing with NULL is the same regardless of the meaning + the given to it in the surrounding context. + + + + The cardinal rule, NULL is never equal or unequal to any non-null + value; and when asked to be combined with a known value in an operation the + result of the operation becomes unknown. e.g., both 1 = NULL and 1 + NULL + result in NULL. Exceptions to this are documented. See [chapter] for + details on how to test for null. Specifically, note that concept of + distinctness is introduced to allow for true/false equality tests. + + + + Extending from the previous point, function calls are truly a mixed bag. + Aggregate functions in particular will usually just ignore NULL inputs + instead of forcing the entire aggregate result to NULL. Function + specifications has a "strictness" attribute that, when set to "strict" + (a.k.a. "null on null input") will tell the executor to return NULL for any + function call having at least one NULL input value, without executing the + function. + + + + A WHERE clause that evaluates to NULL for a given row will exclude that row. + This was demonstrated in the tutorial query where cities with prcp > 0 were + requested and Hayward was not returned due to this and the cardinal rule. + + + + While not yet discussed, it is possible to define validation expressions on + tables that ensure only values passing those expressions are inserted. While + this seems like it would behave as a WHERE clause on a table, the choice here + when an expression evaulates to NULL is allow the row to be inserted. See + [check constraints] in create table for details. + + + + In the context of both DISTINCT and GROUP BY it is necessary that all inputs + resolve to being either equal to or not equal to all other values. These + features use distinctness instead of simple equality in order to handle + NULL like a definite value equal to itself and unequal to all other values. + + + diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 59962d6e85..f9a8686365 100644 --- a/doc/src/sgml/query.sgml +++ b/doc/src/sgml/query.sgml @@ -907,4 +907,6 @@ DELETE FROM tablename; +&null; + -- 2.34.1