ZEROIFNULL

​

Syntax

​

Scalar

zeroifnull(value)

​

Arguments

​

Returns

• Type: INTEGER for TINYINT, SMALLINT or INTEGER input; BIGINT, FLOAT or DOUBLE for the corresponding input type; DECIMAL(min(38, max(p, s+10)), s) for the effective DECIMAL(p,s) input
• NULL behavior: Returns numeric zero when value is NULL.

​

Properties

​

Usage notes

• ZEROIFNULL(value) has the same effect as COALESCE(value, 0).
• The integer zero participates in choosing a compatible result type.
• 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.
• The default zero is INTEGER, so TINYINT and SMALLINT inputs widen to INTEGER. For DECIMAL inputs, p and s describe the type after conversion. The result reserves at least 10 integer digits, within the 38-digit precision limit.
• With the default decimal settings used here, casting a BIGINT column to DECIMAL(38, 2) produces an effective DECIMAL(19, 2) input. Query tools can report DOUBLE even though the value keeps its decimal scale.
• Deterministic: The same inputs and settings produce the same result.

​

Examples

​

Typical behavior

SELECT zeroifnull(CAST(NULL AS INT)) AS missing, zeroifnull(5) AS present;

​

Preserve a decimal value

SELECT zeroifnull(CAST(NULL AS DECIMAL(8,2))) AS missing, zeroifnull(CAST(2.50 AS DECIMAL(8,2))) AS present;

​

Related functions

• coalesce