NVL 및 COALESCE 함수

일련의 표현식에서 NULL이 아닌 첫 번째 표현식의 값을 반환합니다. NULL이 아닌 값이 발견되면 목록의 나머지 표현식은 평가되지 않습니다.

NVL은 COALESCE와 동일합니다. 이 둘은 동의어입니다. 이 항목에서는 구문을 설명하고 두 가지에 대한 예제를 모두 제공합니다.

구문

NVL( expression, expression, ... )

COALESCE의 구문은 동일합니다.

COALESCE( expression, expression, ... )

모든 표현식이 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 같은 집계 함수는 평가할 행이 없으면 0이 아닌 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