23.10.11. Oracle SQL NULL, NVL, NVL2, COALESCE, NULLIF

■ NULL

- null 은 사용할 수 없거나, 할당되지 않았거나, 알 수 없거나 적용할 수 없는 값, 계산할 수 없다. 결측값(치) 
- null 0, 공백 아니다. 

SELECT 
    employee_id,
    salary,
    commission_pct,
    ((salary * 12) + (salary * 12 * commission_pct))annual_salary
FROM hr.employees;

■ NVL

- null 값을 실제값으로 리턴하는 함수
- nvl(컬럼명, 실제값), nvl(인수, 인수)
- nvl 함수 사용시에 두 인수의 타입이 일치해야 한다.

SELECT 
    employee_id,
    salary,
    commission_pct,
    ((salary * 12) + (salary * 12 * commission_pct)) annual_salary_1 ,
    ((salary * 12) + (salary * 12 * nvl(commission_pct, 0) )) annual_salary_2
FROM hr.employees;

SELECT 
    nvl(commission_pct, 0) "nvl()", 
    /*nvl(commission_pct, 'no comm')*/ 
    -- 오류발생 원인:형타입 불일치, nvl(숫자형 컬럼, 문자형)
    nvl(commission_pct, to_number('0')) "to_number",
    /*nvl(commission_pct, to_number('no comm'))*/ 
    -- 오류발생: to_number()작성해도 문자형이여도 무의미.
    nvl(to_char(commission_pct), 'no comm') "nvl(to_char)" 
    -- 문자형으로 출력하기 위해서는 컬럼을 문자형 타입으로 변형(to_char)
FROM hr.employees;

■ NVL2(exp1, exp2, exp3) 

- exp1 null 이 아니면 exp2 를 수행하고 exp1이 null 이면 exp3를 수행한다.
- exp2, exp3 인수 타입이 일치해야 한다.
- 인수값이 3개이여야만 한다.

SELECT 
    employee_id,
    salary,
    commission_pct,
    ((salary * 12) + (salary * 12 * commission_pct)) annual_salary_1 ,
    ((salary * 12) + (salary * 12 * nvl(commission_pct, 0) )) annual_salary_2 ,
    nvl2(commission_pct, ((salary * 12) + (salary * 12 * commission_pct)), salary * 12) annual_salary_3
        /* null 이 아니면,  위의 계산을 수행(2번째)
           null 이라면,                                                    , 위의 계산을 수행(3번째)*/
FROM hr.employees;

if commission_pct is not null then
    ((salary * 12) + (salary * 12 * commission_pct))
else 
    salary * 12 
end if ;

SELECT 
    nvl(commission_pct, 0) "nvl()", 
    nvl(commission_pct, to_number('0')) "to_number",
    nvl(to_char(commission_pct), 'no comm') "nvl(to_char)", 
    /*nvl2(commission_pct, salary*12*commission_pct, 'no_comm') ,*/ 
    -- 오류발생 salary*12*commission_pct(숫자타입), 'no_comm'(문자타입) : 이 두개의 인수 타입이 일치해야 출력
    nvl2(commission_pct, salary*12*commission_pct, to_number('0')) "nvl2" ,
    nvl2(commission_pct, to_char(salary*12*commission_pct), 'no_comm') "nvl2(to_char)"
FROM hr.employees;

■ COALESCE(exp1, exp2, exp3,...,expn) 

- exp1 null이면 exp2를 수행하고 exp2 null 이면 exp3 를 수행하고 exp3 null 이면 다음 exp을 수행한다.
    즉, null이 발생하지 않을 때까지 인수를 수행한다.

- exp1, exp2, .. 모든 인수 타입이 같아야 한다.

SELECT 
    employee_id,
    salary,
    commission_pct,
    ((salary * 12) + (salary * 12 * commission_pct)) annual_salary_1 ,
    ((salary * 12) + (salary * 12 * nvl(commission_pct, 0) )) annual_salary_nvl , -- 실무적으로 가장 많이 보게 됨
    nvl2(commission_pct, ((salary * 12) + (salary * 12 * commission_pct)), salary * 12) annual_salary_nvl2 ,
    coalesce(((salary * 12) + (salary * 12 * commission_pct)), salary * 12, 0 ) annual_salary_coalesce
FROM hr.employees;

■ NULLIF(exp1, exp2)

- exp1과 exp2가 일치하면 null, 일치하지 않으면 exp1을 리턴한다.

<PL/SQL> : 조건제어문
if exp1 = exp2 then
    null
else
    exp1
end if;

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

 

NULL	0, 공백 X
NVL(컬럼명, 실제값)	컬럼명(NULL 값)  → '실제값' 출력 컬럼명 = 실제값 이 '타입'이 일치
NVL2(exp1, exp2, exp3)	exp1 != NULL → exp2 출력 exp1 = NULL → exp3 출력 단, exp2 = exp3 의 인수타입이 일치
COALESCE(exp1, exp2, exp3, ... expn)	exp1 = NULL →  exp2 출력 exp2 = NULL →  exp3 출력   ... exp3 = NULL → expn 출력  NULL 출력되지 않을때까지 출력
NULLIF(exp1, exp3)	exp1 = exp2 → NULL 출력 exp1 != exp2 → exp1 출력