ISNULL

Evaluates an expression and returns a default value if the expression is NULL.

Syntax

ISNULL( expr, value_expr )

Arguments

Parameter

Description

expr

Expressions to evaluate

value_expr

Default value to return if expr is null

Returns

Returns either expr or value_expr.

Notes

  • All expressions must have the same type, which is also the type of the result.

  • See also COALESCE. ISNULL(x,y) is equivalent to COALESCE(x,y).

Examples

ISNULL

master=> SELECT ISNULL(NULL, 5);
5

Replacing NULL values in aggregations

In some cases, replacing NULL values with a default can affect results.

master=> SELECT
.                AVG(num_eyes),
.                AVG(ISNULL(num_eyes,2)) AS "Corrected average"
.
.        FROM (
.              VALUES ('Copepod',1),('Spider',8)
.                    ,('Starfish',5),('Praying mantis',5)
.                    ,('Human (average)',2),('Eagle',2)
.                    ,('Horseshoe crab',10),('Kiwi',NULL)
.                    ,('Fox',NULL),('Badger',NULL)
.              ) AS cool_animals (name , num_eyes);
avg | Corrected average
----+------------------
  4 |                 3