ISNULL
Evaluates an expression and returns a default value if the expression is NULL
.
Syntax
ISNULL( expr, value_expr )
Arguments
Parameter |
Description |
---|---|
|
Expressions to evaluate |
|
Default value to return if |
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 toCOALESCE(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