Firebird Documentation Index → Firebird Null Guide → NULL in operations

## `NULL` in operations

Mathematical and string operations
Boolean operations
More logic (or not)

As many of us have found out to our chagrin, `NULL` is contagious: use it in a numerical, string or date/time operation, and the result will invariably be `NULL`. With boolean operators, the outcome depends on the type of operation and the value of the other operand.

Please remember that in Firebird versions prior to 2.0 it is mostly illegal to use the constant `NULL` directly in operations or comparisons. Wherever you see `NULL` in the expressions below, read it as “a field, variable or other expression that resolves to `NULL`”. In Firebird 2 and above this expression may also be a `NULL` literal.

### Mathematical and string operations

The operations in this list always return `NULL`:

• `1 + 2 + 3 + NULL`

• `5 * NULL - 7`

• `'Home ' || 'sweet ' || NULL`

• `MyField = NULL`

• `MyField <> NULL`

• `NULL = NULL`

If you have difficulty understanding why, remember that `NULL` means “unknown”. You can also look at the following table where per-case explanations are provided. In the table we don't write `NULL` in the expressions (as said, this is often illegal); instead, we use two entities A and B that are both `NULL`. A and B may be fields, variables, or even composite subexpressions – as long as they're `NULL`, they'll all behave the same in the enclosing expressions.

Table 1. Operations on null entities A and B

If A and B are NULL, then: Is: Because:
`1 + 2 + 3 + A` `NULL` If A is unknown, then 6 + A is also unknown.
`5 * A - 7` `NULL` If A is unknown, then 5 * A is also unknown. Subtract 7 and you end up with another unknown.
`'Home ' || 'sweet ' || A ` `NULL` If A is unknown, 'Home sweet ' || A is unknown.
`MyField = A` `NULL` If A is unknown, you can't tell if MyField has the same value...
`MyField <> A` `NULL` ...but you also can't tell if MyField has a different value!
`A = B` `NULL` With A and B unknown, it's impossible to know if they are equal.

Here is the complete list of math and string operators that return `NULL` if at least one operand is `NULL`:

• `+`, ` ``-`, ``` *```, ` `and ` ``/`

• `!=`, ` ``~=`, ``` ```and ` ^= `(synonyms of` <>`)

• `<`, ` ``<=`, ``` ````>`, ` `and ``` ````>=`

• `!<`, ` ``~<`, ``` ```and ` ^< `(low-precedence synonyms of``` >=```)

• `!>`, ` ``~>`, ``` ```and ` ^> `(low-precedence synonyms of``` <=```)

• `||`

• [NOT] BETWEEN

• [NOT] STARTING WITH

• [NOT] LIKE

• [NOT] CONTAINING

The explanations all follow the same pattern: if A is unknown, you can't tell if it's greater than B; if string S1 is unknown, you can't tell if it contains S2; etcetera.

Using LIKE with a `NULL` escape character would crash the server in Firebird versions up to and including 1.5. This bug was fixed in v. 1.5.1. From that version onward, such a statement will yield an empty result set.

### Boolean operations

All the operators examined so far return `NULL` if any operand is `NULL`. With boolean operators, things are a bit more complex:

• `not NULL = NULL`

• ```NULL or false = NULL```

• ```NULL or true = true```

• ```NULL or NULL = NULL```

• ```NULL and false = false```

• ```NULL and true = NULL```

• ```NULL and NULL = NULL```

In reality, Firebird SQL doesn't have a boolean data type; nor are `true` and `false` existing constants. In the leftmost column of the explanatory table below, “`true`” and “`false`” represent expressions (fields, variables, composites...) that evaluate to `true`/`false`.

Table 2. Boolean operations on null entity A

If A is `NULL`, then: Is: Because:
`not A` `NULL` If A is unknown, its inverse is also unknown.
`A or false` `NULL` `A or false`” always has the same value as A – which is unknown.
`A or true` `true` `A or true`” is always `true` – A's value doesn't matter.
`A or A` `NULL` `A or A`” always equals A – which is `NULL`.
`A and false` `false` `A and false`” is always `false` – A's value doesn't matter.
`A and true` `NULL` `A and true`” always has the same value as A – which is unknown.
`A and A` `NULL` `A and A`” always equals A – which is `NULL`.

All these results are in accordance with boolean logic. The fact that you don't need to know X's value to compute “X or `true`” and “X and `false`” is also the basis of a feature found in various programming languages: short-circuit boolean evaluation.

The above results can be generalised as follows for expressions with one type of binary boolean operator (`and` | `or`) and any number of operands:

Disjunctions (“A or B or C or D or ...”)
1. If at least one operand is `true`, the result is `true`.

2. Else, if at least one operand is `NULL`, the result is `NULL`.

3. Else (i.e. if all operands are `false`) the result is `false`.

Conjunctions (“A and B and C and D and ...”)
1. If at least one operand is `false`, the result is `false`.

2. Else, if at least one operand is `NULL`, the result is `NULL`.

3. Else (i.e. if all operands are `true`) the result is `true`.

Or, shorter:

• `TRUE` beats `NULL` in a disjunction (OR-operation);

• `FALSE` beats `NULL` in a conjunction (AND-operation);

• In all other cases, `NULL` wins.

If you have trouble remembering which constant rules which operation, look at the second letter: tRue prevails with oR — fAlse with And.

### More logic (or not)

The short-circuit results obtained above may lead you to the following ideas:

• 0 times `x` equals 0 for every `x`. Hence, even if `x`'s value is unknown, `0 * x` is 0. (Note: this only holds if `x`'s datatype only contains numbers, not `NaN` or infinities.)

• The empty string is ordered lexicographically before every other string. Therefore, `S >= ''` is true whatever the value of S.

• Every value equals itself, whether it's unknown or not. So, although ```A = B``` justifiably returns `NULL` if A and B are different `NULL` entities, `A = A` should always return `true`, even if A is `NULL`. The same goes for `A <= A` and `A >= A`.

By analogous logic, `A <> A` should always be `false`, as well as `A < A` and ```A > A```.

• Every string contains itself, starts with itself and is like itself. So, “```S CONTAINING S```”, “`S STARTING WITH S`” and “`S LIKE S`” should always return `true`.

How is this reflected in Firebird SQL? Well, I'm sorry I have to inform you that despite this compelling logic – and the analogy with the boolean results discussed above – the following expressions all resolve to `NULL`:

• `0 * NULL`

• `NULL >= ''`` ` and ` ``'' <= NULL`

• `A = A`, ` ``A <= A```` ``` and ` ``A >= A`

• `A <> A`, ` ````A < A```` ` and ` ``A > A`

• `S CONTAINING S`, ` ````S STARTING WITH S```` ` and ` ``S LIKE S`

So much for consistency.

 Firebird Documentation Index → Firebird Null Guide → NULL in operations