Functions for NULL values ​

These functions replace missing values or count which arguments are present. SQL NULL is distinct from empty text, numeric zero and a JSON null document.

Use coalesce for a fallback value and num_nulls/num_nonnulls to inspect a row. Use IS NULL when the result should be a missing-value predicate.

Example: Supplying a display fallback ​

SELECT coalesce(NULL, 'anonymous') AS display_name,
       num_nulls(NULL, '', 0) AS missing_fields
FORMAT csv;

"anonymous",1

coalesce ​

Return the first non-NULL argument and stop evaluating later arguments.

Syntax ​

coalesce(value1, value2, ...)

Arguments ​

Two or more SQL expressions evaluated from left to right until a non-NULL result is found.

Returned value ​

The first non-NULL value with its type preserved; NULL when every argument is NULL.

Example ​

SELECT coalesce(NULL,'fallback') FORMAT csv;

"fallback"

num_nonnulls ​

Count non-NULL arguments.

Syntax ​

num_nonnulls(value,...)

Arguments ​

One or more arguments. Only the outer SQL NULL value is counted as missing.

Returned value ​

INTEGER number of non-NULL arguments.

Example ​

SELECT num_nonnulls(1,NULL,2) FORMAT csv;

2

num_nulls ​

Count SQL NULL arguments.

Syntax ​

num_nulls(value,...)

Arguments ​

One or more arguments. Native JSON null and collection members are ordinary non-NULL argument values.

Returned value ​

INTEGER number of NULL arguments.

Example ​

SELECT num_nulls(1,NULL,2) FORMAT csv;

1