IFNULL

​

Syntax

​

Scalar

ifnull(value, replacement)

• All result alternatives must have compatible types. Matching types are preserved; mixed types may be converted to a common type. Use explicit casts to choose the result type.

​

Arguments

​

Returns

• Type: Common SQL type after conversion of value and replacement
• NULL behavior: Returns replacement when value is NULL; returns NULL when both values are NULL.

​

Properties

​

Usage notes

• IFNULL requires compatible same-type arguments. Use explicit casts when the two arguments have different types.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT ifnull(CAST(NULL AS INT), 7) AS "value", ifnull(3, 7) AS present;

​

Supply a string default

SELECT ifnull(CAST(NULL AS VARCHAR), 'unknown') AS missing, ifnull('', 'unknown') AS empty_string;

​

Limitations

• The two arguments must admit a common type.

​

Related functions

• coalesce
• nvl2