[ORACLE] NVL, NVL2, NULLIF, COALESCE 함수 (NULL 값 처리, 치환 등)

쿼리를 작성하다 보면 NULL 값을 그대로 사용할 수 없는 경우 NULL 값에 대한 처리를 많이 하는 편이다.

NULL 값을 체크하거나 치환하는 등의 처리를 위해서는 알아두면 좋을 것 같아 정리해본다.

 

 

 

NVL 구문 예시

NVL(대상 컬럼, 대상 컬럼이 NULL 때의 값)
NVL(COLUMN, 'COLUMN IS NULL')

 

NVL2 구문 예시

NVL2(대상 컬럼, 대상 컬럼이 NULL이 아닌 경우의 값, 대상 컬럼이 NULL일 경우의 값)
NVL2(COLUMN, 'COLUMN IS NOT NULL', 'COLUMN IS NULL')

 

NULLIF 구문 예시

NULLIF(대상 컬럼, 비교할 컬럼)

NULLIF(COLUMN1, COLUMN2)
 > COLUMN1와 COLUMN2의 값이 같다면 NULL 반환
 > COLUMN1와 COLUMN2의 값이 같지 않다면 COLUMN1 반환

 

COALESCE 구문 예시

COALESCE(컬럼1, 컬럼2, 컬럼3, ...)

COALESCE(COLUMN1, COLUMN2, COLUMN3, COLUMN4)
 > COLUMN1이 NULL이 아닌 경우 COLUMN1 반환
 > COLUMN1이 NULL이고 COLUMN2이 NULL이 아닌 경우 COLUMN2 반환
 > COLUMN1,2이 NULL이고 COLUMN3이 NULL이 아닌 경우 COLUMN3 반환
 > COLUMN1,2,3이 NULL이고 COLUMN4이 NULL이 아닌 경우 COLUMN4 반환
 > COLUMN1,2,3,4이 모두 NULL인 경우 NULL 반환

 

 

 

활용 예시

-- NVL 활용 예시
-- 금액(PRICE)이 없는 경우 기본값 '0' 반환하기
SELECT NVL(PRICE, 0) AS PRICE
  FROM PRODUCT


-- NVL2 활용 예시
-- 결제금액(PAYMENT_PRICE)이 있는 경우 '결제완료', 아닌 경우 '결제중' 반환하기
SELECT NVL2(PAYMENT_PRICE, '결제완료', '결제중') AS PAYMENT_STATUS
  FROM ORDER


-- NULLIF 활용 예시
-- 주문금액(ORDER_PRICE)과 결제금액(PAYMENT_PRICE)이 일치하는 경우 '정상', 아닌 경우 '금액 착오' 반환하기
SELECT NVL2(NULLIF(ORDER_PRICE, PAYMENT_PRICE), '금액 착오', '정상') AS PAYMENT_STATUS
  FROM ORDER


-- COALESCE 활용 예시
-- 상품의 금액을 정상판매금액(PRICE) < 1차할인금액(FIRST_SALE_PRICE) < 2차할인금액(SECOND_SALE_PRICE) 순으로 금액 표기하기
SELECT COALESCE(SECOND_SALE_PRICE, FIRST_SALE_PRICE, PRICE) AS PRICE
  FROM PRODUCT