7일차 # 3-52: COALESCE

 

COALESCE

문법

coalesce::=

그림 설명

 

목적

COALESCE 함수는 나열된 값을 순차적으로 체크하여 NULL이 아닌 첫번째 인수를 반환한다. 모든 인수가 NULL인경우에는 NULL을 반환한다.

만약 expr 전체가 수치 데이터형이거나 암묵적으로 수치 데이터 형으로 변환 가능한 수치 외의 데이터형이라면, 오라클 데이터 베이스는 가장 높은 수치 순위를 결정하여 나머지 인수를 암묵적으로 그 데이터형으로 변환하고, 그 데이터 형을 반환한다.

이 함수는 NVL함수의 일반화된것이다.

COALSESCE함수는 CASE식의 표현으로 사용 할수 있다.예를 들어,

COALESCE (expr1, expr2)
는 아래와 같다.

CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

는 동일하게,

COALESCE (expr1, expr2, ..., exprn), for n>=3

이것은 아래와 동일하다.

CASE WHEN expr1 IS NOT NULL THEN expr1 
   ELSE COALESCE (expr2, ..., exprn) END

참 조 : NVL and "CASE Expressions "

 

예제

다음 예제는 oe.prodct_information 테이블을 이용하여, 상품의 재고 판매를 기획한다. 10% 할인 판매를 한다. 만약 표시 가격이 없다면, 세일 가격은 최소가격이 된다. 만약 최소가격이 없다면, 세일 가격은 5이다.

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050;

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      2382        850        731        765
      3355                                5
      1770                    73         73
      2378        305        247      274.5
      1769         48                  43.2

출처:http://www.statwith.pe.kr/ORACLE/functions019.htm#i1001341

- short-circuit evaluation

  

  drop table t1 purge;

  create table t1 as select comm a, mgr b, sal c from emp;

  select * from t1;

  select a, b, c, nvl(nvl(nvl(a, b), c), 0) d from t1;

  select a, b, c, coalesce(a, b, c, 0) d from t1;