COALESCE - MaxCompute - Alibaba Cloud Documentation Center

The COALESCE function of MaxCompute checks the input parameters from left to right and returns the first non-null value without the need to check the following input parameters. This topic describes the syntax and parameters of the COALESCE function. This topic also provides examples on how to use this function.

Syntax

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

Parameters

expr: required. The values that you want to check.

Return value

The data type of the return value is the same as the data type of the input parameter.

Examples

Example 1: common use. Sample statement:

-- The return value is 1. 
select coalesce(null,null,1,null,3,5,7);

Example 2: If the data types of parameter values are not defined, an error is returned.

Incorrect sample statement:

-- The value abc cannot be identified because the data type of the value abc is not defined. An error is returned. 
select coalesce(null,null,1,null,abc,5,7);

Correct sample statement:

select coalesce(null,null,1,null,'abc',5,7);

Example 3: If data is not read from a table and all the values of the input parameters are null, an error is returned. Incorrect sample statement:
```
-- An error is returned because one or more non-null values exist. 
select coalesce(null,null,null,null);
```

Example 4: If data is read from a table and all the values of the input parameters are null, null is returned.

Original data table:

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

The field values for the tt shop in the original table are all null. After the following statement is executed, the value null is returned:

select coalesce(customer_id,total_price) from sale_detail where shop_name='tt';

Related functions

For more information, see Other functions.