Modern SQL: Three-Valued Logic (3VL) — Purpose, Benefits and Special Cases (2024)

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 = 1NULL <> 1NULL > 1NULL = NULL

Nothing equals null. Not even null equals null because each null could be different.

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.

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.

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

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)

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 1NOTIN(0), which is true. For 1, the expression becomes 1NOTIN(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

Exception: Check Constraints

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.

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 ORd=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))ISNULL. 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.

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

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

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.

… IS TRUE

(<expr1> <op> <expr2>) IS UNKNOWN

… IS FALSE

… IS NOT TRUE

(<expr1> <op> <expr2>) IS NOT UNKNOWN

… IS NOT FALSE

CASE WHEN … THEN 0 ELSE 1 END = 0

 <expr1> IS NULLOR <expr2> IS NULL

CASE WHEN NOT(…) THEN 0 ELSE 1 END = 0

CASE WHEN … THEN 0 ELSE 1 END = 1

 <expr1> IS NOT NULLAND <expr2> IS NOT NULL

CASE WHEN NOT(…) THEN 0 ELSE 1 END = 1

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

1. No is unknown. Is null can be used for this purpose
2. Only true and false, not unknown