NVL 和 COALESCE 函數

傳回一系列運算式中不為 null 的第一個運算式的值。找到非 Null 值時，就不會評估清單中剩餘的運算式。

NVL 與 COALESCE 相同。它們是同義詞。本主題說明語法，並包含兩者的範例。

語法

NVL( expression, expression, ... )

COALESCE 的語法是相同的：

COALESCE( expression, expression, ... )

如果所有表達式都是 Null，則結果為 Null。

當您想要在主要值遺失或為​​ null 時傳回次要值，這些函數非常有用。例如，查詢可能會傳回三個可用電話號碼中的第一個：行動電話號碼、住家或公司。函數中運算式的順序決定評估的順序。

引數

運算式

要評估 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 運算式，以 700.0 取代這些 Null 值：加總 funds_collected 的結果不是 485 而是 1885，因為兩個有 null 的列被替換為 700。

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