NVL - MaxCompute - Alibaba Cloud Documentation Center

MaxCompute allows you to use the NVL function to replace a null value in a query with a specific value. This way, no unexpected null values are included in the returned result during data analysis or report generation. This topic describes how to use the NVL function in MaxCompute.

Syntax

nvl(T <value>, T <default_value>)

Parameters

value: required. The input parameter. T specifies the type of input data. The type can be any data type supported by MaxCompute.
default_value: required. The value that is used to replace null. The data type of default_value must be the same as the data type of value.

Return value

If value is null, default_value is returned. Otherwise, value is returned. The value and default_value parameters must be of the same data type.

Sample data

This section provides sample source data to demonstrate how to use the NVL function. In this example, a table named nvl_test is created and data is added to the table. Sample statement:

CREATE TABLE nvl_test (
  c1 string,
  c2 bigint, 
  c3 datetime);
  
 -- Insert data into the table.
 INSERT INTO nvl_test VALUES 
 	('aaa',23,'2024-01-11 00:00:00'),
  ('bbb',NULL,'2024-01-12 08:00:00'),
  (NULL,20,'2024-01-13 05:00:00'),
  ('ddd',25,NULL);

Examples

The nvl_test table contains the following columns: c1, c2, and c3. The NVL function replaces a null value in the c1 column with 00000, replaces a null value in the c2 column with 0, and replaces a null value in the c3 column with a hyphen (-). Sample statement:

SELECT nvl(c1,'00000'),nvl(c2,0),nvl(c3,'-') FROM nvl_test;

-- The following result is returned:
+-----+------------+-----+
| _c0 | _c1        | _c2 |
+-----+------------+-----+
| aaa | 23         | 2024-01-11 00:00:00 |
| bbb | 0          | 2024-01-12 08:00:00 |
| 00000 | 20         | 2024-01-13 05:00:00 |
| ddd | 25         | -   |
+-----+------------+-----+

Related functions

For more information, see Other functions.