[SQL] NULL 치환 함수와 조건식 - NVL/ COALESCE/ LNNVL/ DECODE와 CASE~WHEN~THEN~ELSE~END

1. NVL, NVL2, COALESCE

• Null 값과의 연산은 어떤 연산이라도 결과는 Null
• Null 값을 실제 값으로 변환

 

1) NVL

• 첫번째 인수가 Null이 아니면 첫번째 인수를, 맞으면 두번째 인수 반환

NVL(값 또는 표현식1, 값 또는 표현식2)

-- COMM이 NULL이면 0으로 변환
SELECT ENAME, COMM, NVL(COMM, 0)
FROM EMP;

-- 연봉 계산 시 COMM이 NULL이면 0으로 처리
SELECT ENAME, SAL, COMM, SAL * 12 + NVL(COMM, 0) AS ANNUAL_SAL
FROM EMP;

 

2) NVL2

• 첫번째 인수가 Null이 아니면 두번째 인수를, Null이면 세번째 인수 반환

NVL2(값 또는 표현식1, 값 또는 표현식2, 값 또는 표현식3)

-- COMM이 NULL이 아니면 '수당있음', NULL이면 '수당없음'
SELECT ENAME, COMM, NVL2(COMM, '수당있음', '수당없음') AS COMM_STATUS
FROM EMP;

-- COMM이 NULL이 아니면 COMM값 출력, NULL이면 0 출력
SELECT ENAME, COMM, NVL2(COMM, COMM, 0) AS COMM_RESULT
FROM EMP;

 

3) COALESCE

• 인수들 중 처음 Null이 아닌 인수 반환

COALESCE(값 또는 표현식1, 값 또는 표현식2, ... )

-- COMM이 NULL이면 BONUS, BONUS도 NULL이면 0 반환
SELECT ENAME, COMM, BONUS, COALESCE(COMM, BONUS, 0) AS EXTRA_PAY
FROM EMP;

-- 여러 값 중 가장 먼저 NULL이 아닌 값 선택
SELECT COALESCE(NULL, NULL, 'A', 'B') AS RESULT
FROM DUAL;

 

2. LNNVL, DECODE

1) LNNVL

• 인수의 결과가 FALSE 또는 UNKKOWN(NULL)이면 TRUE, 아니면 FALSE 반환

LNNVL(값 또는 표현식)

-- SAL > 3000 이 FALSE이거나 NULL인 행 조회
SELECT ENAME, SAL
FROM EMP
WHERE LNNVL(SAL > 3000);

-- COMM > 1000 이 FALSE이거나 NULL인 행 조회
SELECT ENAME, COMM
FROM EMP
WHERE LNNVL(COMM > 1000);

 

2) DECODE(Oracle 전용)

• 조건에 따라 값을 반환하는 함수
• IF THEN ELSE 구문과 유사한 방법
• 자주 사용됨
• 마지막에 기본값도 지정 가능(지정하지 않으면 null)

DECODE(컬럼명 또는 표현식, 
		비교값1, 결과값1, 
		비교값2, 결과값2,
		 ... ,
		 기본값)

-- 부서번호에 따라 부서명 변환
SELECT ENAME, DEPTNO,
       DECODE(DEPTNO,
              10, 'ACCOUNTING',
              20, 'RESEARCH',
              30, 'SALES',
              'ETC') AS DNAME
FROM EMP;

-- COMM이 NULL이면 '없음', 0이면 '0', 그 외는 '있음'
SELECT ENAME, COMM,
       DECODE(COMM,
              NULL, '없음',
              0, '0',
              '있음') AS COMM_STATUS
FROM EMP;

 

3. CASE~WHEN~THEN~ELSE~END

CASE 컬럼명 또는 표현식
    WHEN 값1 THEN 반환값1
    WHEN 값2 THEN 반환값2
    ...
    ELSE 기본값
END

-- 부서번호에 따라 부서명 변환
SELECT ENAME, DEPTNO,
       CASE DEPTNO
            WHEN 10 THEN 'ACCOUNTING'
            WHEN 20 THEN 'RESEARCH'
            WHEN 30 THEN 'SALES'
            ELSE 'ETC'
       END AS DNAME
FROM EMP;

 

• 조건식 지정하는 방법

CASE
    WHEN 조건1 THEN 반환값1
    WHEN 조건2 THEN 반환값2
    ...
    ELSE 기본값
END

-- 부서번호에 따라 부서명 변환
SELECT ENAME, DEPTNO,
       CASE 
            WHEN DEPTNO=10 THEN 'ACCOUNTING'
            WHEN DEPTNO=20 THEN 'RESEARCH'
            WHEN DEPTNO=30 THEN 'SALES'
            ELSE 'ETC'
       END AS DNAME
FROM EMP;