The Three-Valued Logic of SQL


trueunknownfalse

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:

  1. Comparisons to null
  2. Logical Operations Involving Unknown
  3. General Rule: where, having, when, etc.
  4. Exception: Check Constraints
  5. Related Features
  6. Binary Decisions Based on Three-Valued Results
  7. Interactive is [not] (true|false|unknown) Emulator
  8. Compatibility

Comparisons to null

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.

Mnemonic

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.

Logical Operations Involving Unknown

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.

Important

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

General Rule: where, having, when, etc.

unknownfalsetrue

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

Odd Consequence: P or not P is not always true

unknownfalsetrue

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.

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

Odd Consequence: not in (null, …) is never true

Consider this example:

WHERE 1 NOT IN (NULL)
trueunknownfalse

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

Tip

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.

Exception: Check Constraints

falsetrueunknown

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 )
)

Truth Value Tests: 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.

Tip

Use (<expr>) is not false instead of (<expr>) or (<expr>) is null. See also Binary Decisions Based on Three-Valued Results below.

Boolean Data Type: Literals 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.

Binary Decisions Based on Three-Valued Results

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.

Tip

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.

On my Own Behalf

I make my living from training, other SQL related services and selling my book. Learn more at https://winand.at/.

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

Interactive is [not] (true|false|unknown) Emulator

trueunknownfalse

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.

Truth Value Test

… IS TRUE
(<expr1> <op> <expr2>) IS UNKNOWN
… IS FALSE
… IS NOT TRUE
(<expr1> <op> <expr2>) IS NOT UNKNOWN
… IS NOT FALSE

Tautology

Contradiction

SQL-92 Alternative

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

Compatibility

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]).

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

Markus offers SQL training and consulting for developers working at companies of any size.
Learn more »

Footnotes

0

SQL:2016-2: §8.2, General Rule 1a

1

SQL:2016-2: §6.39, General Rule 2 and 3

2

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 )

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.

4

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

5

Exists never returns unknown: SQL:2016-2: §8.10 General Rule 2

6

SQL:2016-2: §4.23.3.4

7

F571, “Truth value tests”

8

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

9

SQL:2016-2: §4.5.1.

10

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.

11

The actual result of the comparison—true or false—doesn’t matter here: the comparison operator disappears.

“modern SQL” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy and GDPR | CC-BY-NC-ND 3.0 license