IFNULL

Returns the value of the first non-null expression in the list.

Returns the value of the first non-null expression in the list.

IFNULL is an alias of NVL.

Behavior type

Immutable

Syntax

IFNULL ( expression1 , expression2 );

Parameters

  • If *expression1 *is null, then IFNULL returns expression2.

  • If *expression1 *is not null, then IFNULL returns expression1.

Notes

  • COALESCE is the more standard, more general function.

  • IFNULL is equivalent to ISNULL.

  • IFNULL is equivalent to COALESCE except that IFNULL is called with only two arguments.

  • ISNULL(a,b) is different from x IS NULL.

  • The arguments can have any data type supported by Vertica.

  • Implementation is equivalent to the CASE expression. For example:

    CASE WHEN expression1 IS NULL THEN expression2
ELSE expression1 END;

  • The following statement returns the value 140:

    SELECT IFNULL(NULL, 140) FROM employee_dimension;

  • The following statement returns the value 60:

    SELECT IFNULL(60, 90) FROM employee_dimension;

Examples

=> SELECT IFNULL (SCORE, 0.0) FROM TESTING;
 IFNULL
--------
  100.0
   87.0
     .0
     .0
     .0
(5 rows)

See also