Skip to main content

IFNULL (SQL)

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

Synopsis

IFNULL(expression-1,expression-2 [,expression-3])

{fn IFNULL(expression-1,expression-2)}

Description

InterSystems IRIS supports IFNULL as both an SQL general function and an ODBC scalar function. Note that while these two perform very similar operations, they are functionally different. The SQL general function supports three arguments. The ODBC scalar function supports two arguments. The two-argument forms of the SQL general function and the ODBC scalar function are not the same; they return different values when expression-1 is not null.

The SQL general function evaluates whether expression-1 is NULL. It never returns expression-1:

  • If expression-1 is NULL, expression-2 is returned.

  • If expression-1 is not NULL, expression-3 is returned.

  • If expression-1 is not NULL, and there is no expression-3, NULL is returned.

The ODBC scalar function evaluates whether expression-1 is NULL. It either returns expression-1 or expression-2:

  • If expression-1 is NULL, expression-2 is returned.

  • If expression-1 is not NULL, expression-1 is returned.

Refer to NULL for further details on NULL handling.

The collation of the resulting expression is determined by the first argument that is not of a collation type that may be coerced into the collation of the other. A collation that may be coerced into a different collation is called “coercible.” Possible scenarios are as follows:

  • The collations of expression-2 and expression-3 (if it exists) are not coercible: The output collation is the collation of expression-2.

  • The collation of expression-2 is not coercible, but expression-3 is coercible (if it exists): The output collation is the collation of expression-2.

  • The collation of expression-2 is coercible, but expression-3 is not coercible: The output collation is the collation of expression-3.

  • The collations of expression-2 and expression-3 are coercible: The output collation is coercible.

Data Type of Returned Value

  • IFNULL(expression-1,expression-2): returns the data type of expression-2. If expression-2 is a numeric literal, a string literal, or NULL returns data type VARCHAR.

  • IFNULL(expression-1,expression-2,expression-3): if expression-2 and expression-3 have different data types, returns the data type with the higher (more inclusive) data type precedence. If expression-2 or expression-3 is a numeric literal or a string literal, returns data type VARCHAR. If expression-2 or expression-3 is NULL, returns the data type of the non-NULL argument.

    If expression-2 and expression-3 have different length, precision, or scale, IFNULL returns the greater length, precision, or scale of the two expressions.

  • {fn IFNULL(expression-1,expression-2)}: returns the data type of expression-1. If expression-1 is a numeric literal, a string literal, or NULL, returns data type VARCHAR.

DATE and TIME Display Conversion

Some expression-1 data types, such as DATE and TIME data types, require conversion from Logical mode (mode 0) to ODBC mode (mode 1) or Display mode (mode 2). If the expression-2 or expression-3 value is not the same data type, this value cannot be converted in ODBC mode or Display mode, and an SQLCODE error is generated: -146 for DATE data type; -147 for TIME data type. For example, IFNULL(DOB,'nodate',DOB) cannot be executed in ODBC mode or Display mode; it issue an SQLCODE -146 error with the %msg Error: 'nodate' is an invalid ODBC/JDBC Date value or Error: 'nodate' is an invalid DISPLAY Date value. To execute this statement in ODBC mode or Display mode, you must CAST the value as the appropriate data type: IFNULL(DOB,CAST('nodate' as DATE),DOB). This results in a date 0, which displays as 1840-12-31.

%List Display Conversion

A %List field is a string data type field with encoding. If expression-1 is a %List field, the appropriate expression-2 or expression-3 value depends on the Select Mode:

  • In Logical mode (mode 0) or Display mode (mode 2), a %List value is returned as string data type with the format $lb("element1","element2",...). Therefore, an expression-2 or expression-3 value must be specified as a %List, as shown in the following example:

    SELECT TOP 20 Name, 
    IFNULL(FavoriteColors,$LISTBUILD('No Preference'),FavoriteColors) AS ColorChoice 
    FROM Sample.Person
  • In ODBC mode (mode 1), a %List value is returned as a string of comma-separated elements: element1,element2,.... Therefore, an expression-2 or expression-3 value can be specified as a string as shown in the following example:

    SELECT TOP 20 Name,
    IFNULL(FavoriteColors'No Preference',FavoriteColors) AS ColorChoice
    FROM Sample.Person
    

Arguments

expression-1

The expression to be evaluated to determine if it is NULL or not.

expression-2

An expression that is returned if expression-1 is NULL.

expression-3

An optional expression that is returned if expression-1 is not NULL. If expression-3 is not specified, a NULL value is returned when expression-1 is not NULL.

The returned data type is described below.

NULL Handling Functions Compared

The following table shows the various SQL comparison functions. Each function returns one value if the logical comparison tests True (A same as B) and another value if the logical comparison tests False (A not same as B). These functions allow you to perform NULL logical comparisons. You cannot specify NULL in an actual equality (or non-equality) condition comparison.

SQL Function Comparison Test Return Value
IFNULL(ex1,ex2) [two-argument form] ex1 = NULL

True returns ex2

False returns NULL

IFNULL(ex1,ex2,ex3) [three-argument form] ex1 = NULL

True returns ex2

False returns ex3

{fn IFNULL(ex1,ex2)} ex1 = NULL

True returns ex2

False returns ex1

ISNULL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

NVL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

NULLIF(ex1,ex2) ex1 = ex2

True returns NULL

False returns ex1

COALESCE(ex1,ex2,...) ex = NULL for each argument

True tests next ex argument. If all ex arguments are True (NULL), returns NULL.

False returns ex

Examples

In the following example, the general function and the ODBC scalar function both returns the second expression (99) because the first expression is NULL:

SELECT IFNULL(NULL,99) AS NullGen,{fn IFNULL(NULL,99)} AS NullODBC

In the following example, the general function and the ODBC scalar function examples return different values. The general function returns <null> because the first expression is not NULL. The ODBC example returns the first expression (33) because the first expression is not NULL:

SELECT IFNULL(33,99) AS NullGen,{fn IFNULL(33,99)} AS NullODBC

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

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

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

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

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

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

The following ODBC syntax examples return the string 'No Preference' if FavoriteColors is NULL, otherwise they return the FavoriteColors data value:

SELECT Name,
       {fn IFNULL(FavoriteColors,$LISTBUILD('No Preference'))} AS ColorPref
FROM Sample.Person
SELECT Name,
{fn IFNULL(FavoriteColors,'No Preference')} AS ColorChoice
FROM Sample.Person

The following example uses IFNULL in the WHERE clause. It selects people under the age of 21 who do not have favorite color preferences. If FavoriteColors is NULL, IFNULL returns the Age field value, which is used for the condition test:

SELECT Name,FavoriteColors,Age
FROM Sample.Person
WHERE 21 > IFNULL(FavoriteColors,Age)
ORDER BY Age

Refer to the NULL predicate (IS NULL, IS NOT NULL) for similar functionality.

See Also

FeedbackOpens in a new tab