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.
Contents:
null
where
, having
, when
, etc.is [not] (true|false|unknown)
Emulatornull
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”.
The result of each of the following comparisons is therefore unknown:0
NULL = 1
NULL <> 1
NULL > 1
NULL = NULL
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.
Consider the following example:
(NULL = 1) OR (1 = 1)
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.
A similar case applies to the and
operator: and
connections are false as soon as any operand is false. The result of the following expression is therefore false:
(NULL = 1) AND (0 = 1)
In all other cases, any unknown operand for not
, and
, and or
causes the logical operation to return unknown.1
where
, having
, when
, etc.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 following query is therefore always the empty set:
SELECT col
FROM t
WHERE col = NULL
The result of the equals comparison to null
is always unknown. The where
clause thus rejects all rows.
Use the is null
predicate to search for null
values:
WHERE col IS NULL
P or not P
is not always trueAs 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.
SELECT col
FROM t
WHERE col = NULL
OR NOT (col = NULL)
(1) The result of the comparison col = null
is unknown in both cases; (2) not(unknown)
is also unknown; (3) or
only evaluates as true if one operand is true. Consequently, the condition is always unknown so that the where
clause rejects all rows. This is clearly quite the opposite of what two-valued logic would suggest.3
not in (null, …)
is never trueConsider this example:
WHERE 1 NOT IN (NULL)
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.
If we extend this example we will quickly see that the result of not in
predicates that contain a null
value is never true:
WHERE 1 NOT IN (NULL, 2)
This expression is again unknown because substituting different values for null
(e.g. 0
and 1
) still influences the result. It is nevertheless easy to show that not in
predicates that contain a null
value can be false:
WHERE 1 NOT IN (NULL, 1)
No matter which value you substitute for the null
(0
, 1
or any other value) the result is always false.4
Don’t allow null
in not in
lists.
When using a subquery, consider using not exists
instead of not in
5 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.
In the following example, the column a
or b
can have a value greater 10 if the other column is null
:
CREATE TABLE t (
a NUMERIC CHECK (a >= 0),
b NUMERIC CHECK (b >= 0),
CHECK ( a + b <= 10 )
)
is [not] (true|false|unknown)
Similar to is null
, the SQL standard defines an optional feature to directly test for all three truth values:7
is [not] (true|false|unknown)
Note that this is
test never returns unknown (very much like is [not] null
).8
“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.
true
, false
and unknown
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.
The three-valued logic of SQL postpones a binary decision if a logical expression cannot be said to be unconditionally true or false. However, in the end there is always a truly binary decision, such as taking or rejecting a row due to a where
clause.10
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.
SELECT n, d
FROM t
WHERE n/NULLIF(d,0) > 1
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.
The idiomatic way to reduce a three-valued result to a two-valued one is the truth value test is [not] (true|false|unknown)
:
WHERE (n/NULLIF(d,0) > 1) IS NOT FALSE
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.
Put the condition in parenthesis to avoid ambiguity:
(…) IS NOT FALSE
The is not false
predicate belongs to the optional feature F571, “Truth value tests”, which is still not generally supported. It is nevertheless possible to implement the same logic, without repeating parts of the expression, in practically all SQL databases with a case
expression:
WHERE CASE WHEN NOT(n/NULLIF(d,0) > 1)
THEN 0
ELSE 1
END = 1
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.
I offer SQL training, tuning and consulting. Buying my book “SQL Performance Explained” (from €9.95) also supports my work on this website.
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.
The following example demonstrates this:
(<expr1> <op> <expr2>) IS UNKNOWN
This is logically equivalent to:
<expr1> IS NULL
OR <expr2> IS NULL
For an is not unknown
test you have to use is not null
tests and combine them with a logical and
operation.11
is [not] (true|false|unknown)
EmulatorClick 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.
… IS TRUE
(<expr1> <op> <expr2>) IS UNKNOWN
… IS FALSE
… IS NOT TRUE
(<expr1> <op> <expr2>) IS NOT UNKNOWN
… IS NOT FALSE
Tautology
Contradiction
CASE WHEN …
THEN 0
ELSE 1
END = 0
<expr1> IS NULL
OR <expr2> IS NULL
CASE WHEN NOT(…)
THEN 0
ELSE 1
END = 0
CASE WHEN …
THEN 0
ELSE 1
END = 1
<expr1> IS NOT NULL
AND <expr2> IS NOT NULL
CASE WHEN NOT(…)
THEN 0
ELSE 1
END = 1
Tautology
Contradiction
Three-valued logic has been in the SQL standard from the beginning. It is an integral and widely supported aspect of SQL.
SQL:1999 added two related optional features:
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]
).
is unknown
. Is null
can be used for this purposetrue
and false
, not unknown
You can’t catch up on 20 years of SQL evolution in one day. Subscribe the newsletter via E-Mail, Twitter or RSS to gradually catch up and to keep modern-sql.com on your radar.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »
SQL:2016-2: §8.2, General Rule 1a
SQL:2016-2: §6.39, General Rule 2 and 3
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.
In match_recongize(…define…)
SQL:2016-2: §10.9, General Rule 4a.
Many other features are defined in terms of where
—e.g. the join’s on
and using
clauses (SQL:2016-2: §7.10, General Rule 1b and c respectively )
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.
x NOT IN (NULL, …)
⇔ NOT(x IN(NULL, …))
⇔ NOT(x = ANY(NULL, …))
(SQL:2016-2: §8.4 Syntax Rules 4 and 5).
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).
Exists
never returns unknown: SQL:2016-2: §8.10 General Rule 2
SQL:2016-2: §4.23.3.4
F571, “Truth value tests”
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).
SQL:2016-2: §4.5.1.
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.