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:

- Comparisons to
`null`

- Logical Operations Involving
*Unknown* - General Rule:
`where`

,`having`

,`when`

, etc. - Exception: Check Constraints
- Related Features
- Binary Decisions Based on Three-Valued Results
- Interactive
`is [not] (true|false|unknown)`

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

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 `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 in`

5 or add a `where`

condition to the subquery that removes possible `null`

values.

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

## Related Features

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

#### Support My Work

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

## Interactive `is [not] (true|false|unknown)`

Emulator

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]`

).

- No
`is unknown`

.`Is null`

can be used for this purpose - Only
`true`

and`false`

, not`unknown`