ISNULL

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

ISNULL is an alias of NVL.

Behavior Type

Immutable

Syntax

ISNULL ( expression1 , expression2 );

Parameters

  • If expression1 is null, then ISNULL returns expression2.
  • If expression1 is not null, then ISNULL returns expression1.

Notes

  • COALESCE is the more standard, more general function.
  • ISNULL is equivalent to COALESCE except that ISNULL 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 ISNULL(NULL, 140) FROM employee_dimension;

  • The following statement returns the value 60:

    SELECT ISNULL(60, 90) FROM employee_dimension;

Examples

SELECT product_description, product_price, 
ISNULL(product_cost, 0.0) AS cost 
FROM product_dimension;
      product_description       | product_price | cost 
--------------------------------+---------------+------
 Brand #59957 wheat bread       |           405 |  207
 Brand #59052 blueberry muffins |           211 |  140
 Brand #59004 english muffins   |           399 |  240
 Brand #53222 wheat bread       |           323 |   94
 Brand #52951 croissants        |           367 |  121
 Brand #50658 croissants        |           100 |   94
 Brand #49398 white bread       |           318 |   25
 Brand #46099 wheat bread       |           242 |    3
 Brand #45283 wheat bread       |           111 |  105
 Brand #43503 jelly donuts      |           259 |   19
(10 rows)

See Also