COALESCE

Syntax

COALESCE(<expr1>, <expr2>, ...)

Parameters

expr: Required. The value to evaluate.

Return value

The return value has the same data type as the input arguments.

How it works

COALESCE scans arguments from left to right and stops at the first non-null value:

Examples

Replace null with a default value

Return a display label, falling back to a short description, then to (none) if both are null:

SELECT COALESCE(description, short_description, '(none)') FROM products;

Basic usage

-- Returns 1 (the first non-null value)
SELECT COALESCE(NULL, NULL, 1, NULL, 3, 5, 7);

Undefined column reference causes an error

An unquoted identifier that cannot be resolved as a column fails at parse time:

-- FAILED: ODPS-0130071:[1,34] Semantic analysis exception - column abc cannot be resolved
SELECT COALESCE(NULL, NULL, 1, NULL, abc, 5, 7);

-- Returns 1 ('abc' is a valid string literal)
SELECT COALESCE(NULL, NULL, 1, NULL, 'abc', 5, 7);

All null literals cause an error

When all arguments are null literals (not from a table), MaxCompute requires at least one argument to be statically non-null:

-- FAILED: ODPS-0130071:[1,17] Semantic analysis exception - parameter 1 for function COALESCE expect at least one parameter should be non-null
SELECT COALESCE(NULL, NULL, NULL, NULL);

All null values from a table return null

When null values come from table columns, COALESCE returns null without error:

Raw data in sale_detail:

+-----------+-------------+-------------+
| shop_name | customer_id | total_price |
+-----------+-------------+-------------+
| ad        | 10001       | 100.0       |
| jk        | 10002       | 300.0       |
| ad        | 10003       | 500.0       |
| tt        | NULL        | NULL        |
+-----------+-------------+-------------+

The following query returns null because both customer_id and total_price are null for shop_name = 'tt':

SELECT COALESCE(customer_id, total_price) FROM sale_detail WHERE shop_name = 'tt';

Related functions

COALESCE is a miscellaneous function. For more information about other functions, see Other functions.