[Oracle] 내장 함수 : NULL 관련 함수

Oracle에서 NULL은 중요하게 다루어야 할 값입니다.

덕분에(?) NULL을 다룰 수 있는 다수의 함수들이 있습니다. NULL 관련 함수는 다음과 같습니다.

1. NULL 관련 함수

함수명	양식	기능 설명
NVL	NVL(expr1, expr2)	expr1 != null : expr2 반환 expr1 =  null : expr2 반환
NVL2	NVL2(expr1, expr2, expr3)	expr1 != null : expr2 반환 expr1 =  null : expr3 반환
COALESCE	COALESCE(expr1, expr2, ...)	expr# 가운데서 null이 아닌 첫 번째 표현식(expr#)을 반환
NULLIF	NULLIF(expr1, expr2)	expr1 =  expr2 : null 반환 expr1 != expr2 : expr1 반환
LNNVL	LNNVL(조건식)	조건식 = (null or FALSE) : TRUE 반환 조건식 = TRUE            : FALSE 반환

1.1 NVL(expr1, expr2)

expr1 != null : expr1이 null이 아니면 expr2 반환
expr1 =  null : expr1이 null이면 expr2 반환

 

SELECT NVL('a','값이 null입니다'), NVL(null,'값이 null입니다') FROM DUAL;
       -------------------------   ---------------------------
       [a]                         [값이 null입니다]

 

1.2 NVL2(expr1, expr2, expr3)

expr1 != null : expr1이 null이 아니면 expr2 반환
expr1 =  null : expr1이 null이면 expr3 반환

 

SELECT NVL2('a','A','값이 null'), NVL2(null,'A','값이 null') FROM DUAL;
       -------------------------  --------------------------
       [A]                        [값이 null]

 

1.3 COALESCE(expr1, expr2, ... , expr#)

expr# 가운데서 null이 아닌 첫 번째 표현식(expr#)을 반환

 

SELECT COALESCE('A',null), COALESCE(null,'B'), FROM DUAL;
       ------------------  ------------------ 
       [A]                 [B]
       
SELECT COALESCE(null,null,null,'C') FROM DUAL;
       ----------------------------
       [C]


SELECT COALESCE(10*null, 100*100), COALESCE(10*1, 100*100) FROM DUAL;
       --------------------------  -----------------------
       10000                       10

수식에서 null과의 산술 결과는 null이다.
null로 나눌 경우의 결과도 null

SELECT null+1, null-1, null*10, null/10, 10/null FROM DUAL;
       ------  ------  -------  -------  -------
       null    null    null     null     null

 

1.4 NULLIF(expr1, expr2)

expr1 =  expr2 : 두 컬럼이 같으면 null 반환
expr1 != expr2 : 두 컬럼이 다르면 expr1 반환

 

SELECT NULLIF('A', 'A'), NULLIF('A', 'B') FROM DUAL;
       ----------------  ----------------
       null              [A]

 

1.5 LNNVL(조건식)

조건식 = (null or FALSE) : TRUE 반환
조건식 = TRUE              : FALSE 반환

 

LNNVL은 사용하기 조금은 까다로운 함수입니다.
칼럼에도 사용할 수 있지만 주로 WHERE 조건절에서 사용합니다.

 

LNNVL (column = 0)
-----------------------------
column != 0 OR column IS NULL

 

컬럼에 사용할 경우 컬럼 값이 NULL을 포함한, 조건을 만족하지 않는 컬럼을 의미합니다.

해당 컬럼이 NULL 값을 가진 컬럼을 포함하여 조회할 경우 사용할 수 있습니다.

 

즉 조회하려는 조건의 반대 값을 부여해야 합니다. 

 

아래의 조건은 '수당률이 0.2 미만이면서 NULL'인 컬럼의 수를 파악합니다.

실제 LNNVL에 사용할 조건은 '수당률이 0.2 이상'을 조건으로 적어야 합니다.

 

-- 전체 사원수 : 107명
SELECT COUNT(*) FROM employees;

------------------------------------------------------
-- 수당률이 0.2 이상인 사람의 수 : 24명
SELECT COUNT(*) FROM employees
 WHERE commission_pct >= 0.2;

-- 수당률이 0.2 미만이거나 null인 사람의 수 : 83명
SELECT COUNT(*) FROM employees
 WHERE commission_pct < 0.2 OR commission_pct IS NULL;

-- LNNVL로 위의 수식 표현 : 83명 
SELECT COUNT(*) FROM employees
 WHERE LNNVL(commission_pct >= 0.2)

 

 

 

참고

https://docs.oracle.com/database/121/SQLRF/functions002.htm#SQLRF30049