Skip to main content

Propagation of NULL values in expressions

ON THIS PAGE

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 >= returns False (0
  • A > NULL returns False (0