NVL 과 NVL2 함수의 차이와 사용 방법에 대해 알아보도록 하겠습니다.
우선 직관적으로 확인할 수 있는 차이는 함수의 이름이죠? 하나는 NVL, 나머지 하나는 NVL2 이라고 이름을 지은만큼, 기능이 확장된 것으로 보이기도 하는데, 사용상 차이가 존재한답니다.
그 차이를 문법(syntax)을 통해 간단히 비교해 보는데, NVL를 먼저 알아보고 그 다음 NVL2를 그리고 마지막으로 두 함수를 비교해 보겠습니다.
NVL (exp1, exp2)
NVL 함수는 인수를 2개 사용합니다. 인수를 각각 exp1, exp2라 지칭하겠습니다. NVL(exp1, exp2)은 exp1 값이 NOT NULL인 경우, exp1 값을 그대로 반환하며, NULL인 경우에는 exp2 값을 반환합니다.
SELECT 'NVL (1, 2)', NVL (1, 2) FROM DUAL
UNION ALL
SELECT 'NVL (NULL, 2)', NVL (NULL, 2) FROM DUAL
UNION ALL
SELECT 'NVL (1, NULL)', NVL (1, NULL) FROM DUAL
UNION ALL
SELECT 'NVL (NULL, NULL) ', NVL (NULL, NULL) FROM DUAL;
'NVL(1,2)' NVL(1,2)
---------------------------------- ----------
NVL (1, 2) 1
NVL (NULL, 2) 2
NVL (1, NULL) 1
NVL (NULL, NULL) NULL- NVL (1, 2) => 첫 번째 인수가 1(NOT NULL)이므로 첫 번째 인수 1을 반환
- NVL (NULL, 2) => 첫 번째 인수가 NULL이므로 두 번째 인수 2를 반환
- NVL (1, NULL) => 첫 번째 인수가 1(NOT NULL)이므로 첫 번째 인수 1을 반환, NVL 함수를 이렇게 사용하지는 않습니다. 왜냐 하면 NVL(column_name, NULL)은 column과 동일한 결과를 표시하기 때문입니다.
- NVL (NULL, NULL) => 첫 번째 인수가 NULL이므로 두 번째 인수 NULL을 반환, 위와 마찬가지로 이렇게 사용하지는 않습니다.
NVL2 (exp1, exp2, exp3)
NVL의 인수가 2개인데 반해, NVL2 함수는 인수를 3개 사용한다는 차이점이 있답니다. 인수를 각각 exp1, exp2, exp3라 칭하겠습니다. NVL2(exp1, exp2, exp3) 함수는 exp1의 값이 NOT NULL인지, NULL인지 확인하여, NOT NULL인 경우 exp2를 반환하고 NULL인 경우 exp3를 반환합니다.
SELECT 'NVL2 (1, 2, 3)', NVL2 (1, 2, 3) FROM DUAL
UNION ALL
SELECT 'NVL2 (NULL, 2, 3)', NVL2 (NULL, 2, 3) FROM DUAL
UNION ALL
SELECT 'NVL2 (1, NULL, 3)', NVL2 (1, NULL, 3) FROM DUAL
UNION ALL
SELECT 'NVL2 (1, 2, NULL)', NVL2 (1, 2, NULL) FROM DUAL
UNION ALL
SELECT 'NVL2 (NULL, NULL, 3)', NVL2 (NULL, NULL, 3) FROM DUAL
UNION ALL
SELECT 'NVL2 (NULL, 2, NULL)', NVL2 (NULL, 2, NULL) FROM DUAL
UNION ALL
SELECT 'NVL2 (1, NULL, NULL)', NVL2 (1, NULL, NULL) FROM DUAL
UNION ALL
SELECT 'NVL2 (NULL, NULL, NULL)', NVL2 (NULL, NULL, NULL) FROM DUAL;
'NVL2(1,2,3)' NVL2(1,2,3)
---------------------------------------------- -----------
NVL2 (1, 2, 3) 2
NVL2 (NULL, 2, 3) 3
NVL2 (1, NULL, 3) NULL
NVL2 (1, 2, NULL) 2
NVL2 (NULL, NULL, 3) 3
NVL2 (NULL, 2, NULL) NULL
NVL2 (1, NULL, NULL) NULL
NVL2 (NULL, NULL, NULL) NULL- NVL2 (1, 2, 3) => 첫 번째 인수가 NOT NULL이므로 두 번째 인수 2를 반환
- NVL2 (NULL, 2, 3) => 첫 번째 인수가 NULL이므로 세 번째 인수 3을 반환
- NVL2 (1, NULL, 3) => 첫 번째 인수가 NOT NULL이므로 두 번째 인수 NULL을 반환
- NVL2 (1, 2, NULL) => 첫 번째 인수가 NOT NULL이므로 두 번째 인수 2를 반환
- NVL2 (NULL, NULL, 3) => 첫 번째 인수가 NULL이므로 세 번째 인수 3을 반환
- NVL2 (NULL, 2, NULL) => 첫 번째 인수가 NULL이므로 세 번째 인수 NULL을 반환
- NVL2 (1, NULL, NULL) => 첫 번째 인수가 NOT NULL이므로 두 번째 인수 NULL을 반환
- NVL2 (NULL, NULL, NULL) => 첫 번째 인수가 NULL이므로 세 번째 인수 NULL을 반환
NVL vs NVL2
위의 비교 예시를 잘 읽어보았으면 이제 NVL과 NVL2를 더이상 헷갈리지 않을 것이리라 기대합니다.
두 함수의 공통점은 첫 번째 인수가 NULL 여부를 확인하는 기준 값이라는 점입니다.
두 함수의 차이점은 해당 값의 NOT NULL, NULL 여부에 따라 반환하는 값이 달라지는 것인데, NVL 함수는 NOT NULL인 경우, 기준이 되는 exp1값을 그대로 반환하고, NVL2 함수는 exp2값을 반환합니다.
요약하면 NVL(exp1,exp2) 함수는 NVL2(exp1,exp1,exp2)와 동일한 반환값을 기대할 수 있습니다.
NVL, NVL2 이외에도 NULLIF 나 COALESCE 함수를 제공하기는 하나, NVL 이외에는 코드의 직관성을 고려하여 CASE 구문을 추천합니다. 이 부분은 개발환경이나 협력사항에 따라 적절히 적용하면 될 것입니다.
WITH TABLE_NAME
AS (SELECT 1 LEV, '100' POINT FROM DUAL
UNION ALL
SELECT 2, '70' FROM DUAL
UNION ALL
SELECT 3, '20' FROM DUAL
UNION ALL
SELECT 4, NULL FROM DUAL
UNION ALL
SELECT 5, '-50' FROM DUAL)
SELECT LEV,
POINT,
NVL (POINT, '0'),
NVL2 (POINT, POINT, '0'),
NVL (POINT, 'NULL'),
NVL2 (POINT, 'NOT NULL', 'NULL')
FROM TABLE_NAME;
LEV POINT NVL(PO NVL2(P NVL(POIN NVL2(POINT,'NOTN
---------- ------ ------ ------ -------- ----------------
1 100 100 100 100 NOT NULL
2 70 70 70 70 NOT NULL
3 20 20 20 20 NOT NULL
4 0 0 NULL NULL
5 -50 -50 -50 -50 NOT NULL