coalesce function

Applies to: Databricks SQL Databricks Runtime

Returns the first non-null argument.

Syntax

coalesce(expr1 [, ...] )

Arguments

• exprN: Any expression that shares a least common type across all exprN.

Returns

The result type is the least common type of the arguments.

There must be at least one argument. Unlike for regular functions where all arguments are evaluated before invoking the function, coalesce evaluates arguments left to right until a non-null value is found. If all arguments are NULL, the result is NULL.

Examples

> SELECT coalesce(NULL, 1, NULL);
 1
>  SELECT coalesce(NULL, 5 / 0);
 Division by zero

> SELECT coalesce(2, 5 / 0);
 2

> SELECT coalesce(NULL, 'hello');
 hello

Related

• nvl function
• nvl2 function
• SQL data type rules