NULL values will propagate through an expression according to a few logical and quite reasonable rules.
Functions
The general rule is that functions return NULL when the parameters fall outside the range for which the function is defined.
Example:
- asin(2) returns NULL
- log(-5) returns NULL
- round(A,0) returns NULL
As a result of the above follows that functions generally return NULL when any of the parameters necessary for the evaluation are NULL.
Example:
- sin(NULL) returns NULL
- chr(NULL) returns NULL
- if(NULL, A, B) returns B
- if(True, NULL, A) returns NULL
- if(True, A, NULL) returns A
The exception to the second rule are logical functions testing for type.
Example:
- isnull(NULL) returns True (-1)
- isnum(NULL) returns False (0)
Arithmetic and string operators
If NULL is encountered on any side of these operators NULL is returned.
Example:
- A + NULL returns NULL
- A - NULL returns NULL
- A / NULL returns NULL
- A * NULL returns NULL
- NULL / A returns NULL
- 0 / NULL returns NULL
- 0 * NULL returns NULL
- A & NULL returns A
Relational operators
If NULL is encountered on any side of relational operators special rules apply.
Example:
- NULL rel.op NULL returns NULL
- A <> NULL returns True (-1)
- A < NULL returns False (0)
- A <= NULL returns False (0)
- A = NULL returns False (0)
- A >= returns False (0)
- A > NULL returns False (0)
