[함수] NULL 함수 - NVL, NVL2, COALESCE, NULLIF

 

테이블 속에 저장된 데이터 중에 종종 NULL인 경우가 있다.

이러한 경우에는 다양한 함수를 통해서 NULL을 대신하여 대체값을 출력되도록 하거나 그대로 두기도 한다.

 

오늘은 NULL을 처리하기 위해 사용되는 다양한 함수에 대해서 알아보자.

 

 

---

 

 

1. NVL

• nvl(컬럼, 실제값), nvl(인수, 인수)
• null 값을 실제값으로 리턴하는 함수이다.
• nvl 함수 사용 시에는 두 인수의 타입이 일치해야 한다.
• 즉, 해당 컬럼이 int 데이터 타입이면 null에 대한 대체값으로도 int 값이이어야 한다는 의미이다.

 

SELECT employee_id
    , salary
    , commission_pct
    , ((salary * 12) + (salary * 12 * commission_pct)) AS anual_salary_1
    -- nvl 함수로 인해 commission_pct 컬럼 속 null값은 0으로 대체되어 계산
    , ((salary * 12) + (salary * 12 * nvl(commission_pct,0))) AS anual_salary_2
FROM hr.employees;

 

 

 

아래 쿼리를 통해 데이터 타입 일치의 필요성을 알 수 있다.

데이터 타입이 일치하지 않으면 내부적으로 형변환이 일어난다. 이는 곧 성능 저하의 원인이 될 수 있으니 조심하자.

SELECT 
       nvl(commission_pct, 0)
       -- 1) 형변환 발생
     , nvl(commission_pct, to_number('0')) 
     
       -- 2) 숫자 > 문자 형변환 후 결측치 처리
     , nvl(to_char(commission_pct), 'no_comm') 
FROM hr.employees;

 

 

 

 

2. NVL2

• nvl2(exp1, exp2, exp3)
• exp1이 NULL이 아니면 exp2로 대체되고, exp1이 NULL이면 exp3로 대체한다.
• exp2, exp3 데이터 타입이 일치해야 한다.

 

아래 쿼리는 nvl2 함수를 사용한 예시이다.

만약 commission_pct 컬럼에 null이 존재하지 않으면 두번째 표현식 값으로 대체하고, 

null이 존재하면 세번째 표현식 값으로 대체하라는 의미이다.

SELECT employee_id
    , salary
    , commission_pct
    , ((salary * 12) + (salary * 12 * nvl(commission_pct,0))) AS anual_salary
    , nvl2(commission_pct
        , ((salary * 12) + (salary * 12 * commission_pct))
        , ((salary * 12) + (salary * 12 * nvl(commission_pct,0)))
           ) AS anual_salary_NOT_NULL
FROM hr.employees;

 

 

아래 쿼리를 통해 데이터 타입을 다시 한번 확인 할 수 있다.

주석 처리된 코드를 통해 exp2와 exp3의 데이터 타입이 일치하지 않으면 오류가 발생을 확인 할 수 있다.

SELECT nvl(commission_pct, 0)
     , nvl(commission_pct, to_number('0')) 
     , nvl(to_char(commission_pct), 'no_comm') 
     -- 오류 발생/ exp2, exp3 타입 오류
     --, nvl2(commission_pct,salary * 12 * commission_pct,'no comm') 
     
     -- exp2, exp3 타입 일치 필수!
     , nvl2(commission_pct,to_char(salary * 12 * commission_pct),'no comm') -- exp2, exp3 타입 일치 필수!
FROM hr.employees;

 

 

 

 

3. COALESCE()

• COALESCE(exp1, exp2, exp3, ...., expn)
• exp1 NULL이면 exp2를 수행하고, exp2가 NULL 이면 exp3를 수행하고, exp3 null이면 다음 expn을 수행한다.
• NULL 이 발생하지 않을 때까지 인수를 수행한다.

SELECT employee_id
    , salary
    , commission_pct
    , ((salary * 12) + (salary * 12 * commission_pct)) AS annual_salary
    , ((salary * 12) + (salary * 12 * nvl(commission_pct,0))) AS annual_salary_nvl
    , nvl2(commission_pct,((salary * 12) + (salary * 12 * commission_pct)),((salary * 12) + (salary * 12 * nvl(commission_pct,0)))) AS annual_salary_nvl2
    , COALESCE(((salary * 12) + (salary * 12 * commission_pct)),salary*12, 0 ) AS annual_salary_coalese
FROM hr.employees;

 

 

 

4. NULLIF()

• NULLIF(exp1, exp2)
• exp1과 exp2가 일치하면 NULL 일치하지 않으면 exp1을 리턴한다.

 

아래 간단한 코드? 처럼 일치하면 null로 리턴하고 일치하지 않으면 첫번째 인수 값으로 리턴하라는 의미를 가진다.

if exp1 = exp2 then
    null
else 
    exp1
end if

 

 

 

아래 쿼리는 성(first_name)과 이름(last_name)의 길이가 같으면 이름 길이 출력하도록 하였다.

SELECT length(last_name)
     , length(first_name)
     , nullif(length(last_name),length(first_name))
FROM hr.employees;

 

 

 

---

 

 

오늘은 oracle database에서 null 함수에 대해서 알아보았다.

이러한 함수들은 ms sql, maria db등에도 다른 이름으로 존재한다. 참고하도록 하자.