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)

Did this information help you?

Thanks for letting us know. Is there anything you'd like to tell us about this topic?

Can you tell us why it did not help you and how we can improve it?