NVL および COALESCE 関数
一連の式の中で、Null 以外の最初の式の値を返します。Null 以外の値が見つかると、リスト内の残りの式は評価されません。
NVL は COALESCE と同じです。これらはシノニムです。このトピックでは、両方の構文について説明し、例を示します。
構文
NVL( expression, expression, ... )
COALESCE の構文は同じです。
COALESCE( expression, expression, ... )
すべての式が null の場合、結果は null になります。
これらの関数は、プライマリ値がないか Null の場合にセカンダリ値を返すときに役に立ちます。例えば、クエリを実行すると、使用可能な 3 つの電話番号 (携帯、自宅、職場) のうち最初の電話番号が返されることがあります。関数内の式の順序によって、評価の順序が決まります。
引数
- 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 式をその 2 列に適用します。
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;
結果は、以下のとおりです。最後の 2 つのレコードには 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
に置き換えることができます。485
の代わりに、funds_collected
を合計した結果が 1885
になります。これは、Null を含む 2 つの行が 700
に置き換えられるためです。
select sum(nvl(funds_collected, 700.0)) as sumresult from booking_info;
sumresult ------ 1885