NULLIF

The NULLIF function compares the values of its parameters and returns NULL if they are equal. Otherwise, it returns the value of its first parameter. Therefore, NULLIF performs the inverse operation of the COALESCE function.

Syntax

NULLIF(<expression1>, <expression2>)

Parameters

Parameter	Description	Supported input types
`<expression1>`	Expression of any type that is comparable to the type of `<expression2>`	Any
`<expression2>`	Expression of any type that is comparable to the type of `<expression1>`	Any

If <expression1> and <expression2> have different types, they will be promoted to their least common supertype for the purpose of comparison. The same type promotion rules as for the regular comparison operators (e.g. =) apply.

Return Types

If <expression1> and <expression2> have the same type, this will also be the return type of the NULLIF function. If <expression1> and <expression2> have different types, the return type will be the least common supertype to which they are promoted for comparison purposes.

Examples

This example below highlights an instance where NULL would be returned from NULLIF:

SELECT NULLIF('(none)', '(none)');

nullif text null
NULL

Rows: 1Execution time: 4.99ms

In the next example, NULLIF returns the value of <expression1> because it is not equal to the value of <expression2>:

SELECT NULLIF('Level 4', '(none)');

nullif text null
Level 4

Rows: 1Execution time: 6.26ms

The following example illustrates type promotion if the parameters to NULLIF have different types. The least common supertype of REAL and BIGINT is DOUBLE:

SELECT NULLIF(3.14::REAL, 42::BIGINT);

nullif double null
3.140000104904175

Rows: 1Execution time: 7.99ms

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Types

Examples

​Syntax

​Parameters

​Return Types

​Examples

Syntax

Parameters

Return Types

Examples