Propagation of NULL values in expressions

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

>=

NULL

returns

False (0)

A

>

NULL

returns

False (0)