Oracle (4) - null값을 치환하는 방법 (NVL, NVL2, COALESCE)

오라클을 사용하 다보면 null에 따른 이벤트 처리를 많이 하게 됩니다. 저는 그동안 CASE 문을 이용하여 사용하였으나 간단하게 함수로 처리하는 방법이 있어 소개하려고 합니다. 직관적으로 알 수 있도록 CASE 문을 사용하는 경우 어떤 형식으로 작성할 수 있는지 추가로 작성하겠습니다.

 

예제 테이블 데이터

GRADE	NAME	PRICE	SCORE
1	강감찬	3000	300
2	홍길동	5000	500
3	황진이		600
4	장영실		700
5	이순신

 

NVL

SELECT NAME
    ,  CASE WHEN PRICE IS NULL THEN SCORE
            ELSE PRICE
       END  AS NVL_EX
  FROM EX_TABLE
/
SELECT NAME, NVL(PRICE,SCORE) AS NVL_EX  -- PRICE값이 있는 경우 PRICE 출력, NULL인 경우 SCORE 출력
  FROM EX_TABLE

NVL 함수는 값이 null인 경우에 지정된 값을 출력하며 null인 경우에 원래값을 출력합니다. 두 값의 데이터 타입은 일해야 합니다.

NAME	NVL_EX
강감찬	3000
홍길동	5000
황진이	600
장영실	700
이순신

 

NVL2

SELECT NAME
    ,  PRICE
    ,  CASE WHEN PRICE IS NOT NULL THEN 'Y'
            ELSE 'N'
       END  AS NVL_EX2
  FROM EX_TABLE
/
SELECT NAME, PRICE
     , NVL2(PRICE,'Y','N') AS NVL2_EX -- PRICE가 있는 경우 'Y' 없는 경우 'N' 출력
  FROM EX_TABLE

NVL2 함수는 값이 null인 경우에 null에 유무에 따라 null인 경우에 지정되는 값과 null이 아닌 경우 지정되는 값을 출력합니다. 추가로 두 지정된 값의 데이터 타입은 일치해야 합니다.

NAME	PRICE	NVL2_EX
강감찬	3000	Y
홍길동	5000	Y
황진이		N
장영실		N
이순		N

 

COALESCE

-- COALESCE(arg1,arg2,arg3,....) arg2까지는 필수

SELECT NAME
    ,  PRICE
    ,  CASE WHEN PRICE IS NULL THEN CASE WHEN SCORE IS NULL THEN GRADE
                                         ELSE SCORE
                                    END
            ELSE PRICE
       END  AS COALESCE_EX3
  FROM EX_TABLE

SELECT NAME
     , PRICE
     , NVL(PRICE,SCORE) AS NVL_EX
     , COALESCE(PRICE,SCORE) AS COALESCE_EX2
     , COALESCE(PRICE,SCORE,GRADE) AS COALESCE_EX3
  FROM EX_TABLE

COALESCE 함수는 NVL 함수의 기능이 여러 개 적용 되어있는 함수입니다. 인자의 개수는 2개 이상으로 동적으로 입력합니다. NVL 함수가 null인 경우 지정된 값을 출력하는데, 갯수에 제한 없이 왼쪽부터 순서대로 null인 경우 값을 지정합니다. COALESCE 함수에 인자를 두 개만 사용하는 경우 NVL 함수처럼 사용할 수 있습니다. 마찬가지로 지정된 값의 데이터 타입은 일치해야 합니다.

NAME	PRICE	NVL_EX	COALESCE_EX2	COALESCE_EX3
강감찬	3000	3000	3000	3000
홍길동	5000	5000	5000	5000
황진이		600	600	600
장영실		700	700	700
이순신				5