NVL 和 COALESCE 函数

返回表达式系列中不为 null 的第一个表达式的值。当找到非 null 值时，将不计算该列表中的剩余表达式。

NVL 与 COALESCE 相同。它们是同义词。本主题说明了其语法，并提供这两者的示例。

语法

NVL( expression, expression, ... )

用于 COALESCE 的语法是相同的：

COALESCE( expression, expression, ... )

如果所有表达式为 null，则结果为 null。

如果您要在主要值缺失或为 null 时返回次要值，则这些函数非常有用。例如，一个查询可能会返回前三个可用电话号码中的第一个：手机、家庭或工作号码。函数中表达式的顺序决定了计算结果的顺序。

参数

expression

一个要针对 null 状态进行计算的表达式，如列名称。

返回类型

Amazon Redshift 根据输入表达式确定返回值的数据类型。如果输入表达式的数据类型不是通用类型，则会返回错误。

示例

如果列表包含整数表达式，则该函数返回一个整数。

SELECT COALESCE(NULL, 12, NULL);

coalesce
--------------
12

此示例与前面的示例相同（不同之处在于它使用 NVL），返回相同的结果。

SELECT NVL(NULL, 12, NULL);

coalesce
--------------
12

以下示例返回字符串类型。

SELECT COALESCE(NULL, 'Amazon Redshift', NULL);

coalesce
--------------
Amazon Redshift

以下示例会导致错误，因为表达式列表中的数据类型有变化。在这种情况下，列表中既有字符串类型，也有数字类型。

SELECT COALESCE(NULL, 'Amazon Redshift', 12);
ERROR: invalid input syntax for integer: "Amazon Redshift"

对于本例，您创建一个包含 START_DATE 和 END_DATE 列的表，插入几个包含 null 值的行，然后将 NVL 表达式应用于这两个列。

create table datetable (start_date date, end_date date);           
insert into datetable values ('2008-06-01','2008-12-31');
insert into datetable values (null,'2008-12-31');
insert into datetable values ('2008-12-31',null);

select nvl(start_date, end_date)
from datetable
order by 1;
               
coalesce
------------
2008-06-01
2008-12-31
2008-12-31

NVL 表达式的默认列名称为 COALESCE。以下查询将返回相同的结果：

select coalesce(start_date, end_date)
from datetable
order by 1;

在以下示例查询中，您创建一个包含示例酒店预订信息的表，然后插入几行。一些记录包含 null 值。

create table booking_info (booking_id int, booking_code character(8), check_in date, check_out date, funds_collected numeric(12,2));

插入以下示例数据。一些记录没有 check_out 日期或 funds_collected 数量。

insert into booking_info values (1, 'OCEAN_WV', '2023-02-01','2023-02-03',100.00);
insert into booking_info values (2, 'OCEAN_WV', '2023-04-22','2023-04-26',120.00);
insert into booking_info values (3, 'DSRT_SUN', '2023-03-13','2023-03-16',125.00);
insert into booking_info values (4, 'DSRT_SUN', '2023-06-01','2023-06-03',140.00);
insert into booking_info values (5, 'DSRT_SUN', '2023-07-10',null,null);
insert into booking_info values (6, 'OCEAN_WV', '2023-08-15',null,null);

以下查询返回日期列表。如果 check_out 日期不可用，它会列出 check_in 日期。

select coalesce(check_out, check_in)
from booking_info
order by booking_id;

结果如下。请注意，最后两条记录显示了 check_in 日期。

coalesce
------------
2023-02-03
2023-04-26	
2023-03-16	
2023-06-03	
2023-07-10	
2023-08-15	

如果您希望查询为特定函数或列返回 null 值，则可使用 NVL 表达式将这些 null 值替换为其他一些值。例如，聚合函数（如 SUM）在没有要计算的行时会返回 null 值而不是零。您可以使用 NVL 表达式将这些 null 值替换为 700.0。对 funds_collected 求和的结果不是 485，而是 1885，因为值为 null 的两行被替换为 700。

select sum(nvl(funds_collected, 700.0)) as sumresult from booking_info;
               
sumresult
------
 1885