MaxCompute:COALESCE

Syntax

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

-- Standard example.
-- Returns 1.
SELECT coalesce(NULL,NULL,1,NULL,3,5,7);

Parameters

expr: Required. The value to be evaluated.

Return value

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

Examples

• Example 1: Returns an error if a parameter has an undefined data type.

  -- The data type of the parameter abc is not defined. The system engine cannot resolve it.
  -- FAILED: ODPS-0130071:[1,34] Semantic analysis exception - column abc cannot be resolved
  SELECT COALESCE(NULL,NULL,1,NULL,abc,5,7);
  
  -- Returns 1.
  SELECT COALESCE(NULL,NULL,1,NULL,'abc',5,7);

• Example 2: Returns an error if all parameters are NULL and you are not reading data from a table.

  -- An error is returned, indicating that at least one parameter must be 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);

• Example 3: Returns NULL if all parameters are NULL and you are reading data from a table.

  Raw data table:

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

  The raw data table shows that all values in the tt column are NULL. The following statement returns NULL.

  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.