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