ISNULL

A general SQL function that tests for NULL and returns the appropriate expression.

Synopsis

ISNULL(check-expression,replace-expression)

Arguments

check-expression	The expression to be evaluated.
replace-expression	An expression that is returned if check-expression is NULL.

Description

ISNULL evaluates check-expression and returns one of two values:

If check-expression is NULL, replace-expression is returned.
If check-expression is not NULL, check-expression is returned.

The possible data type(s) of replace-expression must be compatible with the data type of check-expression.

The following table shows the various SQL comparison functions. Each function returns one value if the comparison tests True (A equals B) and another value if the comparison tests False (A not equal to B):

SQL Function	Comparison Test	Return Value
NULLIF	expression1 = expression2	True = NULL False = expression1
IFNULL (2 argument form)	expression1 = NULL	True = expression2 False = NULL
ISNULL	expression1 = NULL	True = expression2 False = expression1
NVL	expression1 = NULL	True = expression2 False = expression1
IFNULL (3 argument form)	expression1 = NULL	True = expression2 False = expression3

Note that the ISNULL function is the same as the NVL function, which is provided for Oracle compatibility.

Examples

The following example returns the string 'No Preference' if FavoriteColors is NULL; otherwise, it returns the value of FavoriteColors:

SELECT Name,
ISNULL(FavoriteColors,'No Preference') AS ColorPref
FROM Sample.Person

Compare the behavior of ISNULL with IFNULL:

SELECT Name,
IFNULL(FavoriteColors,'No Preference') AS ColorPref
FROM Sample.Person

This following example returns the second expression (99) because the first expression is NULL:

SELECT DISTINCT ISNULL(NULL,99) AS NullTest
FROM Sample.Person

This following example returns the first expression (33) because the first expression is not NULL:

SELECT DISTINCT ISNULL(33,99) AS NullTest
FROM Sample.Person