[Oracle] 오라클에서 Null 처리하는 방법과 연산시 주의사항(NVL, NVL2, NULLIF, DECODE)

오라클에서 SELECT 할 때 NULL이면 다른 값으로 변경해줘야 하는 경우가 많다. 이것을 NULL 처리한다고 하는데 오라클에서 제공하는 NVL, NVL2 함수를 써서 쉽게 처리할 수 있다. NVL함수는 오라클에서만 제공하므로 다른 데이터베이스와의 호환을 염두에 둔다면 피해야 할 함수이기도 한다.

 

NVL, NVL2 함수 사용법

NVL(컬럼, NULL일 경우 반환 값)

NVL2(컬럼, NULL이 아닐 경우 반환 값, NULL일 경우 반환 값)

 

예제

-- 컬림이 NULL일때 A반환
SELECT NVL(NULL, 'A') FROM DUAL; --A 
 
-- 컬럼이 NULL일때 B반환 NULL이 아니면 A
SELECT NVL2(NULL, 'A', 'B') FROM DUAL; --B

NVL, NVL2 함수로 NULL 처리가 가능하지만 DECODE 함수로도 NULL 처리가 가능하다.  DECODE 함수는 IF함수랑 같다고 생각하면 되고 그래서 NVL, NVL2 함수와 다르게 컬럼에 대한 조건이 필요하다.  NVL, NVL2 같은 함수를 안 사용해도 DECODE로 원하는 결과를 도출할 수 있지만 용도에 맞는 명시적인 함수를 사용하는 것이 좋다. 

 

 

DECODE 함수 사용법

DECODE(컬럼, 조건, 조건이 맞는 경우 반환 값, 조건이 틀린 경우 반환 값)

 

예제

-- 컬럼이 NULL이면 'Y' 그렇지 않으면 'N'
SELECT DECODE(NULL, NULL, 'Y', 'N') FROM DUAL; -- Y
SELECT DECODE(NULL, 'TEST', 'Y', 'N') FROM DUAL; -- N

 

 

 

NULL 처리를 해야 할 때

컬럼값이 NULL이면 연산할 때 그 컬럼은 제외된다. 이 것은 COUNT, AVG 등 연산에서 치명적인 오류를 발생할 수 있는데  NULL인 컬럼은 제외되기 때문에 COUNT 연산에서 제외된다. 그래서 반드시 NULL 처리를 해줘야 한다.

 

WITH TMP AS (
    SELECT 5 AS AMT FROM DUAL UNION ALL
    SELECT NULL AS AMT FROM DUAL UNION ALL
    SELECT 15 AS AMT FROM DUAL 
)
 
SELECT COUNT(AMT) CNT    FROM TMP;  -- CNT: 2
 
 
WITH TMP AS (
    SELECT 5 AS AMT FROM DUAL UNION ALL
    SELECT NULL AS AMT FROM DUAL UNION ALL
    SELECT 15 AS AMT FROM DUAL 
)
 
SELECT COUNT(NVL(AMT, 0)) CNT    FROM TMP;  -- CNT: 3

 

아래 예제는 AMT 컬럼이 NULL일 때 0으로 변환하는 것이다.

 

 

연산시 주의 사항

(+, -) 할 때 NULL이 포함돼 있으면 NULL이 되고, / 를 할때 0이 있으면 오류가 발생한다. 그래서 +, - 할때는 NVL을 사용해야 하고 / 를 할때는 NULLIF를 사용해야 한다.

 

NULLIF는 컬럼값이 특정 값일때 NULL로 변환시켜주는 함수 이다.

 

 

사용예제

1 + 2 + NULL을 하니 3이 안나오고 NULL이 나온다 그래서 NVL함수를 사용해 NULL을 0으로 바꿔주었다.

 

1 / 0 을 하니 제수가 0 입니다 라는 에러가 발생한다. 그래서 NULLIF함수를 이용해 컬럼값이 0 이면 NULL로 변환시켜주었다.