Source: https://modern-sql.com/concept/three-valued-logic
Timestamp: 2019-04-26 06:28:31+00:00

Document:
SQL uses a three-valued logic: besides true and false, the result of logical expressions can also be unknown. SQL’s three valued logic is a consequence of supporting null to mark absent data. If a null value affects the result of a logical expression, the result is neither true nor false but unknown.
The three-valued logic is an integral part of Core SQL and it is followed by pretty much every SQL database.
General Rule: where, having, when, etc.
The SQL null value basically means “could be anything”. It is therefore impossible to tell whether a comparison to null is true or false. That’s where the third logical value, unknown, comes in. Unknown means “true or false, depending on the null values”.
Nothing equals null. Not even null equals null because each null could be different.
For comparisons every null is a different null. This is different in group by, partition by and related operations.
That’s why SQL has the is null predicate to test whether a value is null or not and the is not distinct from predicate to compare two values while treating two null values as the same.
In logical connections (and, or), unknown behaves like the null value in comparisons: The result is unknown if it depends on an operand that is unknown. In contrast to comparisons, this principle leads to cases in which the result of a logical connection is not unknown even though one operand is unknown. The reason is that the result of a logical connection is only unknown if it actually depends on an operand that is unknown.
Although the comparison to null makes the first operand of the or operation unknown, the total result is still true because or operations are true as soon as any operand is true.
Another way to look at it is to mentally replace each null with a call to a random() function. If the overall result of the expression is inevitably the same, no matter which value random() returns, the result obviously does not depend on the null value and it is therefore not unknown.
In the example above you can assume the values 0 and 1 instead of null to make the result of the first operand false and true respectively. But the result of the complete expression is true in both cases—it does not depend on the value you assume for null.
The logical value unknown indicates that a result actually depends on a null value.
The where, having, and when clauses (e.g. in case expressions) require true conditions.2 It is not enough that a condition is not false.
The result of the equals comparison to null is always unknown. The where clause thus rejects all rows.
As the name “three-valued logic” suggests, there are three values to consider in logical expressions. At first sight the following where clause looks like a tautology—i.e. a condition that is always true. Nonetheless, it behaves entirely differently as it considers only two out of the three possible values—namely that the condition col = NULL is true or false.
To understand this example, read null as “could be anything” or random() if you prefer. Then try to find two values for null that make the expression true and false respectively. Let’s take 0 and 1. For 0, the expressions becomes 1 NOT IN (0), which is true. For 1, the expression becomes 1 NOT IN (1), which is clearly false. The result of the original expression is therefore unknown, because it changes if null is replaced by different values.
Don’t allow null in not in lists.
When using a subquery, consider using not exists instead of not in5 or add a where condition to the subquery that removes possible null values.
Check constraints follow the reverse logic: they reject false, rather than accepting true as the other clauses do.6 Consequently, check constraints accept true and unknown.
“Compatibility” below describes which databases support this feature.
Use (<expr>) is not false instead of (<expr>) or (<expr>) is null. See also “Binary Decisions Based on Three-Valued Results” below.
The barely supported optional feature T031, “BOOLEAN data type”, introduces the keywords true, false and unknown outside of the is predicate.
Note that the truth value unknown is indistinguishable from the null for the Boolean type.9 Otherwise, the Boolean type would have four logical values.
The difference between the literals null and unknown is that unknown is of type Boolean while null can take any type. Putting a not null constraint on a column of the SQL type Boolean makes it a classical two-valued Boolean.
As explained above, the SQL standard generally treats unknown like false when it eventually has to make a binary decision (exception: check constraints). Think of it like an implied is true test on every where, having, and so on.
Treating unknown like false is not always the right choice. If you need another behavior, just use an explicit is [not] (true|false|unknown) test.
Consider the following example that uses nullif to prevent a potential division by zero error. Consequently, the where condition becomes unknown for rows where d is zero (0) and those rows are rejected by the where clause.
If you need to return the rows with d = 0 as well, you can add OR d = 0 to the where clause. Of course this is a correct solution, but it requires an understanding of the condition. A more generic approach is to repeat the entire null-able expression in order to explicitly include the null case: OR (n/NULLIF(d,0)) IS NULL. Still, that is not exactly elegant.
This accepts both results—true and unknown—and is logically equivalent to the solutions that use an or connection. The benefit is that it does not require any repetition or semantic understanding of the condition.
This expression explicitly tests for the false case (when not (…)) and uses the else clause to catch the two other cases (true and unknown). This allows for the required mapping without repeating any part of the condition. The numeric literals were arbitrarily chosen to represent “false” (0) and “true or unknown” (1). The concluding comparison (= 1) is always true or false because neither operand can ever become null.
The workaround with case can map unknown to either true or false. This covers four out of the six possible cases: is [not] (true|false). The two remaining cases, is unknown and is not unknown, cannot be implemented using case without repeating some parts of the logical expression.
To emulate the is [not] unknown test, you can exploit the fact that unknown is the same as null for Boolean values. In principle, it is possible to use is [not] null to test for unknown. In practice, this is hardly useful because most databases that don’t support is [not] unknown don’t support the Boolean type either.
That means that you must test the operands of the comparison for null and combine the result logically.
Click on the truth values in the picture to get the SQL expressions that map those value(s) to true but the other(s) to false.
Three-valued logic has been in the SQL standard from the beginning. It is an integral and widely supported aspect of SQL.
F571, “Truth value tests”: extends the is operator for all three logical values.
T031, “BOOLEAN data type”: defines the Boolean data type, the aggregate functions every, any, and some, as well as the literals true, false, and unknown (other than following is [not]).
For the where clause in select: SQL:2016-2: §7.12, General Rule 2; in update SQL:2016-2: §14.14, General Rule 5; in delete SQL:2016-2: §14.9, General Rule 6; in filter SQL:2016-2: §10.9, General Rule 4a and SQL:2016-2: §10.11, General Rule 3a (JSON).
For the having clause: SQL:2016-2: §7.14, General Rule 1.
For the when clause: in case SQL:2016-2: §6.12, General Rule 2a; in triggers SQL:2016-2: §15.19, General Rule 4bi2 and 3.
It breaks the law of excluded middle.
Using is null instead of = null would, of course, return all rows of t. You can also understand this as follows: is null returns a two-valued result (never unknown) so all possible values are covered.
An = ANY predicate is only false (so that the negation becomes true) if all comparisons are false SQL:2016-2: §8.9 General Rule 2d. This is however, not possible if there is a null comparison, which inevitably yields unknown. The result of x NOT IN (NULL, …) is either false (SQL:2016-2: §8.9 General Rule 2c) or unknown (SQL:2016-2: §8.9 General Rule 2e).
SQL:2016-2: §6.39, General Rule 3. SQL:2016 added a new is predicate (is json) that can return unknown (SQL:2016-2: §8.22, General Rule 2a).
The argument that there is always a two-valued decision does not hold true if the Boolean data type is used: in that case a three-valued Boolean column can be selected and further processed outside the database.
The actual result of the comparison—true or false—doesn’t matter here: the comparison operator disappears.

References: §7
 §14
 §14
 §10
 §10
 §7
 §6
 §15
 §8
 §8
 §8
 §6
 §8