| Firebird Docset → Firebird Database Docs → Firebird Null Guide → NULL in expressions |
|
|
|
As many of us have found out to our chagrin, NULL is contagious: use it in a numerical, string or date/time expression, and the result will always be NULL. Use it in a boolean expression, and the outcome depends on the type of operation and the other values involved.
Please note, by the way, 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”.
The expressions in this list always return NULL:
1 + 2 + 3 + NULL
'Home ' || 'sweet ' || NULL
MyField = NULL
MyField <> NULL
NULL = NULL
not (NULL)
If you have difficulty understanding why, remember that NULL means “unknown”. Also have a 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 entire subexpressions in their own right - 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. |
| '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. |
| not (A) | NULL | If A is unknown, its inverse is also unknown. |
We've already seen that not(NULL) yields NULL. For the and and or operators, things are a bit more complicated:
NULL or false = NULL
NULL or true = true
NULL or NULL = NULL
NULL and false = false
NULL and true = NULL
NULL and NULL = NULL
Firebird SQL doesn't have a boolean data type; nor are true and false existing constants. In the left column of the explanatory table below, (true) and (false) represent subexpressions returning true/false.
Table 2. Boolean operations on null entity A
| If A is NULL, then: | Is: | Because: |
|---|---|---|
| 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, in order to compute “X or true” and “X and false”, you simply don't need to know X's value, is also the basis of a feature we know from various programming languages: short-circuit boolean evaluation.
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 data type 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 justifiedly returns NULL if A and B are different NULL entities, A = A should always return true, even if A is NULL.
How is this implemented 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 >= ''
'' <= NULL
A = A (with A a null field or variable)
So much for consistency.
In aggregate functions like COUNT, SUM, AVG, MAX, and MIN, NULL is handled differently: to calculate the outcome, only non-NULL fields are taken into consideration. That is, if you have this table:
MyTable
| ID | Name | Amount |
|---|---|---|
| 1 | John | 37 |
| 2 | Jack | <NULL> |
| 3 | Joe | 5 |
| 4 | Josh | 12 |
| 5 | Jay | <NULL> |
...the statement select sum(Amount) from MyTable returns 54, which is 37 + 5 + 12. Had all five fields been summed, the result would have been NULL. For AVG, the non-NULL fields are summed and the sum divided by the number of non-NULL fields.
There is one exception to this rule: COUNT(*) returns the count of all rows, even rows whose fields are all NULL. But COUNT(FieldName) behaves like the other aggregate functions in that it only counts rows where the specified field is not NULL.
Another thing worth knowing is that COUNT(*) and COUNT(FieldName) never return NULL: if there are no rows in the set, both functions return 0. Also, COUNT(FieldName) returns 0 if all FieldName fields in the set are NULL. The other aggregate functions return NULL in such cases. Be warned that even SUM returns NULL if used on an empty set, which is contrary to common logic.
|
| Firebird Docset → Firebird Database Docs → Firebird Null Guide → NULL in expressions |