NULLIF

​

Syntax

​

Scalar

nullif(value, other)

​

Arguments

​

Returns

• Type: Same SQL type as value
• Possible values: The first argument value or NULL.
• NULL behavior: NULLIF(NULL, x) is NULL. NULLIF(x, NULL) returns x when x is not NULL; both NULL inputs return NULL.

​

Properties

​

Usage notes

• Comparison operands may be converted, but the result preserves the first argument 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.
• 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 nullif(7, 7) AS equal_value, nullif(7, CAST(NULL AS INT)) AS null_other;

​

Replace a sentinel string with NULL

SELECT nullif('unknown', 'unknown') AS missing, nullif('red', 'unknown') AS present;

​

Related functions

• coalesce
• equal_null