NULL 함수와 CASE

NULL 함수는 NULL을 다른 값으로 바꾸거나(NVL·NVL2·COALESCE) 값을 NULL로 바꿉니다(NULLIF). CASE와 DECODE는 조건에 따라 다른 값을 돌려줍니다.

NULL이 섞인 산술 연산은 결과도 NULL이라, 계산하기 전이나 화면에 보일 때 NULL을 0이나 '없음'으로 바꿔야 할 때가 많습니다. 값에 따라 '상·중·하'처럼 다른 결과를 내려면 CASE를 씁니다.

SELECT *FROM EMPORDER BY EMPNO;

SELECT EMPNO,  ENAME,  DEPTNO,  NVL(DEPTNO, 0) AS NVL_DEPTFROM EMPORDER BY EMPNO;

SELECT EMPNO,  ENAME,  MGR,  NVL2(MGR, '있음', '없음')    AS HAS_MGRFROM EMPORDER BY EMPNO;

SELECT COALESCE(NULL, NULL,    30, 40)FROM DUAL;

SELECT EMPNO,  ENAME,  DEPTNO,  NULLIF(DEPTNO, 20)    AS NULLIF_DEPTFROM EMPORDER BY EMPNO;

SELECT EMPNO,  ENAME,  SAL,  CASE    WHEN SAL >= 500 THEN '상'    WHEN SAL >= 400 THEN '중'    ELSE '하'  END AS SAL_CLASSFROM EMPORDER BY EMPNO;

SELECT ENAME,  DEPTNO,  CASE DEPTNO    WHEN NULL THEN '부서 없음'    ELSE '부서 있음'  END AS BY_CASE,  DECODE(DEPTNO, NULL,    '부서 없음', '부서 있음')    AS BY_DECODEFROM EMPORDER BY EMPNO;

SELECT *FROM EMPORDER BY EMPNO;

1NULL을 다루는 함수 넷

김민준은 MGR이, 정하늘은 DEPTNO가 NULL입니다. NULLIF에서 정하늘이 NULL인 것은 같아서가 아닙니다. NULL = 10은 알 수 없음이라 첫째 값 NULL이 그대로 나옵니다.

NVL은 둘째 인자를 첫째 인자의 형으로 바꿔 돌려줍니다. 숫자 열에 NVL(MGR, '없음')을 쓰면 '없음'을 숫자로 못 바꿔 ORA-01722 오류입니다. COALESCE는 인자를 둘 이상 몇 개든 받고, 모두 NULL이면 NULL입니다. NULLIF의 첫째 인자에는 NULL 상수를 쓸 수 없습니다. SQL Server는 NVL 대신 ISNULL을 쓰고, NVL2와 DECODE는 Oracle에만 있습니다.

집계 함수의 결과도 NULL일 수 있습니다. GROUP BY 없이 집계하면 WHERE에 맞는 행이 하나도 없어도 한 줄이 나오고, COUNT(*)는 0이지만 SUM·AVG는 NULL입니다(GROUP BY로 묶으면 0행 — GROUP BY와 집계 함수 레슨에서 자세히).

2CASE 식

CASE는 두 꼴로 씁니다.

어느 꼴이든 처음 참인 WHEN에서 멈추므로, 조건이 겹치는 WHEN끼리는 차례를 바꾸면 결과가 달라질 수 있습니다. ELSE가 없으면 맞는 WHEN이 없는 행은 NULL입니다. THEN·ELSE의 값은 형이 같아야 해서, 숫자와 문자를 섞으면 ORA-00932 오류입니다.

3DECODE와 NULL

DECODE는 DECODE(식, 값1, 결과1, …, 기본값)⁠처럼 견줄 값과 돌려줄 값을 짝지어 늘어놓는 Oracle 함수입니다. 짝이 없는 마지막 인자가 기본값이고, 없으면 NULL입니다. NULL을 찾을 때 단순형 CASE와 갈립니다.

예제

숫자 열 MGR의 빈칸에 '없음'을 보이려면 첫째 인자를 TO_CHAR로 먼저 문자로 바꿉니다.

SELECT EMPNO,
  NVL(TO_CHAR(MGR), '없음')
    AS MGR_TXT
FROM EMP
ORDER BY EMPNO;

단순형 CASE와 DECODE로 부서 이름을 붙이면 결과가 같습니다.

SELECT ENAME,
  DEPTNO,
  CASE DEPTNO
    WHEN 10 THEN '기획'
    WHEN 20 THEN '개발'
    WHEN 30 THEN '영업'
    ELSE '없음'
  END AS BY_CASE,
  DECODE(DEPTNO, 10, '기획',
    20, '개발', 30, '영업',
    '없음') AS BY_DECODE
FROM EMP
ORDER BY EMPNO;

DEPTNO가 NULL인 정하늘은 맞는 값이 없어 두 식 모두 '없음'입니다.

시험 포인트

• NVL·NVL2·COALESCE·NULLIF가 돌려주는 값을 보기마다 섞어 냅니다. NVL2는 NULL이 아닐 때 둘째 값입니다.
• NVL의 둘째 인자는 첫째 인자의 형으로 바뀌어야 합니다. NVL(MGR, '없음')은 ORA-01722 오류입니다.
• SQL Server는 NVL 대신 ISNULL을 씁니다. NVL2·DECODE는 Oracle 함수입니다.
• GROUP BY 없이 집계할 때 행이 하나도 없으면 COUNT(*)는 0, SUM은 0이 아니라 NULL인 한 줄입니다.
• CASE는 처음 참인 WHEN에서 멈추고, ELSE가 없으면 NULL입니다. THEN·ELSE의 형이 섞이면 오류입니다.
• CASE 열 WHEN NULL은 NULL을 찾지 못하고, DECODE는 두 NULL을 같다고 봅니다.

확인 문제

1. 다음 쿼리의 결과 네 값은 무엇일까요?

SELECT NVL2(NULL, 1, 2) AS A,
  NULLIF(10, 10) AS B,
  NULLIF(10, 20) AS C,
  COALESCE(NULL, 5, NULL) AS D
FROM DUAL;

2. 그림의 상·중·하 CASE에서 두 WHEN의 차례를 바꿨습니다. 김민준(520)의 결과는 무엇이고, '상'은 몇 명일까요?

SELECT EMPNO,
  ENAME,
  SAL,
  CASE
    WHEN SAL >= 400 THEN '중'
    WHEN SAL >= 500 THEN '상'
    ELSE '하'
  END AS SAL_CLASS
FROM EMP
ORDER BY EMPNO;

3. 40번 부서에는 사원이 없습니다. 다음 쿼리의 A와 B는 무엇일까요?

SELECT NVL(SUM(SAL), 0) AS A,
  SUM(NVL(SAL, 0)) AS B
FROM EMP
WHERE DEPTNO = 40;