[ORACLE]Null 처리 방법

이번 포스팅은 ORACLE에서 Null 처리 및 검사 방법을 설명합니다.

 

목차

1. IS [NOT] NULL
2. NVL
3. NVL2
4. NULLIF
5. COALESCE
6. DECODE
7. CASE

---

IS [NOT] NULL

expr1 IS [NOT] NULL

테이블에서 expr1 칼럼이 NULL 값이거나 NULL 값이 아닌 행을 반환합니다.

EMP 테이블에서 MONEY 칼럼의 값이 NULL인 직원들을 조회하려면 다음과 같이 쿼리를 작성할 수 있습니다.

SELECT *
FROM EMP
WHERE MONEY IS NULL;

실행 결과

 

반대로 MONEY 칼럼의 값이 NULL이 아닌 직원들은 IS NOT NULL을 사용하여 조회할 수 있습니다.

SELECT *
FROM EMP
WHERE MONEY IS NOT NULL;

실행 결과

NULL은 숫자 0과 다르며, NULL과 NULL은 다릅니다.

 

즉, 다음 쿼리는 어떠한 행도 조회되지 않습니다.

SELECT *
FROM EMP
WHERE MONEY = NULL;

NULL = NULL은 FALSE이며, 칼럼의 NULL값을 체크하고 싶다면, 칼럼 IS [NOT] NULL을 사용해야 합니다.

---

NVL

NVL(expr1, expr2)

expr1이 NULL이 아니면 expr1을 반환합니다.

expr1이 NULL인 경우 expr2를 반환합니다.

SELECT NVL('A', 'B')  // A
     , NVL(NULL, 'B') // B
     , NVL(1, 2)      // 1
     , NVL(NULL, 2)   // 2
     , NVL('A', 2)    // A
FROM DUAL;

첫 번째 표현식이 NULL인 경우 두 번째 표현식으로 대체합니다.

 

다음 쿼리는 오류가 발생합니다.

SELECT NVL(1, 'A')
FROM DUAL;

문자열 'A'를 숫자로 변환할 수 없으므로 오류가 발생합니다.

 

다음 쿼리는 숫자형 값인 '2'를 varchar2 타입으로 변환하므로 오류가 발생하지 않습니다.

SELECT NVL('A', 2)    // A
FROM DUAL;

---

NVL2

NVL2(expr1, expr2, expr3)

expr1이 NULL이 아니면 expr2를 반환합니다.

expr1이 NULL인 경우 expr3을 반환합니다.

SELECT NVL2('A', 'B', 'C')   // C
     , NVL2(NULL, 'B', 'C')  // 2
     , NVL2(1, 2, 3)         // 3
     , NVL2(NULL, 2, 3)      // 3
     , NVL2(NULL, 'A', 2)    // 2
FROM DUAL;

 

NVL과 마찬가지로 문자열을 숫자로 변환할 수 없으므로 다음 쿼리는 오류가 발생합니다.

SELECT NVL2(NULL, 1, 'A') 
FROM DUAL;

---

NULLIF

NULLIF(expr1, expr2)

NULLIF는 expr1이 expr2와 같으면 NULL을 반환합니다.

expr1이 expr2과 다르면, expr1을 반환합니다.

SELECT NULLIF('A', 'B') // A
     , NULLIF('A', 'A') // NULL
     , NULLIF(2, 3)     // 2
     , NULLIF(2, 2)     // NULL
FROM DUAL;

 

NVL, NVL2와 다르게 암시적 변환이 없으므로 expr1과 expr2는 동일한 데이터 타입이어야 합니다.

 

다음 쿼리는 데이터 타입이 다르므로 오류가 발생합니다.

SELECT NULLIF('1', 1)
FROM DUAL;

SELECT NULLIF(1, '1')
FROM DUAL;

---

COALESCE

COALESCE(expr [, expr ]...)

COALESCE는 목록에 있는 표현식에서 NULL이 아닌 첫 번째 표현식을 반환합니다.

 

목록은 2개 이상의 값으로 구성되어야 하며, 모든 표현식이 NULL이면 NULL을 반환합니다.

SELECT COALESCE('A', 'B', 'C')    // A
     , COALESCE(NULL, 'B', 'C')   // B
     , COALESCE(NULL, NULL, 'C')  // C
     , COALESCE(NULL, 'B', NULL)  // B
FROM DUAL;

 

NULLIF와 동일하게 암시적 형변환이 없습니다.

 

다음 쿼리는 포현식의 데이터 타입이 다르므로 오류가 발생합니다.

SELECT COALESCE(NULL, 1, '1')
FROM DUAL;

SELECT COALESCE(NULL, '1', 1)
FROM DUAL;

---

DECODE

DECODE(expr, search, result [, search, result ]... [, default ])

DECODE를 사용하면 표현식을 사용하여 반환할 항목을 결정할 수 있습니다.

 

default 값을 설정하지 않으면 NULL을 반환합니다.

 

예시 1)

SELECT DECODE(NULL, NULL, 'A', 'B') // A
FROM DUAL;

표현식 NULL과 첫 번째 조건인 NULL과 일치하면 'A'를 반환하며, 일치하지 않으면 default 값인 'B'를 반환합니다.

 

예시 2)

SELECT DECODE('A', 'B', 'C', 'D', 'E') // NULL
FROM DUAL;

표현식 'A'와 첫 번째 조건인 'B'가 일치하는 경우 'C'를 반환합니다.

 

일치하지 않는 경우 'A'와 두 번째 조건인 'D'를 비교하여 일치하면, 'E'를 반환합니다.

 

'A'는 첫 번째 조건인 'B'와 두 번째 조건인 'D'와 일치하지 않으며, DECODE에 default 값이 없으므로 NULL을 반환합니다. 

---

CASE

CASE문을 이용하여 NULL을 체크할 수 있습니다.

 

예시 1)

SELECT CASE WHEN NULL IS NULL THEN 'A' ELSE 'B' END // A
FROM DUAL;

CASE문에 NULL을 체크하는 경우 CASE WHEN 표현식 IS NULL THEN으로 체크해야 합니다.

 

예시 2)

SELECT CASE NULL WHEN NULL THEN 'A' ELSE 'B' END // B
FROM DUAL;

위 쿼리에서 CASE문의 조건문이 NULL = NULL로 처리가 되므로 'A'가 아닌 'B'를 반환합니다.

 

앞에서 언급했듯이 NULL = NULL은 FALSE입니다.

 

예시 3)

SELECT CASE NVL(NULL, 'C') 
            WHEN 'A' THEN 'A' 
            WHEN 'B' THEN 'B'
            WHEN 'C' THEN 'C'
       ELSE 'NOT FOUND' END // C
FROM DUAL;

CASE문 표현식에 대해 NVL와 같은 NULL을 체크하는 함수를 이용하여 조건문을 처리할 수 있습니다.

 

예시 4)

SELECT CASE 'A' WHEN 'B' THEN 'A' END // NULL
FROM DUAL;

CASE 문에서 만족하는 조건이 없으며,  ELSE문이 생략된 경우 NULL을 반환합니다.