Oracle에서 NULL처리

개요

---

DB를 사용하다 보면 항상 NULL과의 싸움입니다. 언제 NULL 위험이 있는지, NULL의 위험이 있는 경우 어떻게 처리해야 하는지 정리합니다. (이 글에서 다루는 칼럼들은 NULL 허용입니다. 그래야 NULL 발생을 대비해 학습할 수 있습니다.)

 

 

NULL이 발생하는 경우

---

NULL이 발생하는 경우는 크게 6가지로 구분할 수 있습니다.

1. NULL 허용 칼럼의 값이 NULL일 때
2. NULL과 조인
3. 아우터조인에서 조인에 실패한 아우터 조인된 테이블의 칼럼 값
4. 스칼라서브쿼리에서 조건에 해당하는 데이터가 없는 경우 메인쿼리의 결과 값
5. GROUP BY 절 없이 사용한 집계함수에서 조건에 해당하는 데이터가 없을 때
6. CASE 표현식을 이용한 컬럼 변환에서 처리에 누락되는 컬럽 값

 

NULL이 발생하는 경우 예제

---

 NULL이 발생하는 경우 예제를 하나씩 살펴보겠습니다.

 

 

1. NULL 허용 칼럼의 값이 NULL일 때

---

 테이블 칼럼이 NULL 허용이라면 NULL이 발생할 수 있다는 것을 반드시 염두에 두고 처리합니다.

 

SELECT A.사원번호,
	NVL(A.기본수당액, 0) AS 기본수당액,
    NVL(A.특별수당액, 0) AS 특별수당액,
    NVL(A. 성과급, 0) AS 성과급
FROM 기타급여 A

 

 

2. NULL과의 연산

---

 NULL과 산술연산하면 NULL입니다. NULL은 알 수 없는 값이므로, 알 수 없는 값과 연산하면 NULL입니다. 함수 연산도 마찬가지므로 다음과 같이 연산이 필요한 경우 개별로 NULL 처리를 해야 합니다. (기본수당액, 특별수당액, 성과급이 모두 NULL 허용이라고 가정합니다.)

 

GREATEST(B.기본수당액, B.특별수당액, B.성과급) -- 하나라도 NULL이면 NULL을 리턴
GREATEST(NVL(B.기본수당액, 0), NVL(B.특별수당액, 0), NVL(B.성과급)) -- NVL처리를 통해 NULL 방지

 

 

3. 아우터조인 NULL

---

아우터조인에서 조인에 실패한 아우터조인된 테이블의 칼럼 값에는 적용할 수 있는 값이 없으므로 NULL입니다.

 

SELECT
    A.사원번호,
    A.사원명,
    B.월기본급 -- NVL(B.월기본급, 0)으로 수정
FROM
	사원 A,
    금액제급여 B
WHERE
	A.부서코드 = '20'
    AND A.사원번호 = B.사원번호(+)
ORDER BY
	A.사원번호

 

B를 A에 OUTER JOIN 했을 때, A에 존재하는 사원번호가 B에 없어 B.월기본급은 NULL을 반환합니다.

 

 

4. 스칼라 서브쿼리의 NULL

---

스칼라서브쿼리에서 해당하는 값이 없을 때, 메인쿼리의 결과는 NULL입니다.

 

SELECT
    A.사원번호,
    A.사원명,
    (
    SELECT B.월기본급 -- NVL(B.월기본급, 0)으로 수정
    FROM 금액제급여 B
    WHERE A.사원번호 = B.사원번호
    ) 월기본급
FROM 사원 A
WHERE A.부서코드 = '20'
ORDER BY A.사원번호

 

만약 사원 A 테이블에 존재하는 사원번호가 금액제급여 B 테이블에 존재하지 않는다면, B.월기본급은 NULL을 반환합니다.

 

 

5. 집계함수의 NULL

---

GROUP BY 절 없이 집계함수를 사용할 때 조건에 해당하는 값이 하나도 없으면, 결과 값이 NULL인 로우 1개를 반환합니다.  적용할 수 있는 값이 없기 때문입니다. GROUP BY 절 있이 집계함수를 사용할 때 조건에 해당하는 값이 하나도 없으면 아무런 출력이 없습니다.

 

 COUNT함수는 절대 NULL을 반환하지 않습니다. 해당 데이터가 없어도 0을 반환합니다. 바꿔 말하면 COUNT를 제외한 집계함수는 모두 NULL을 반환할 수 있습니다.

 

SELECT
    COUNT(*),
    COUNT(B.월기본급),
    COUNT(NULL),
    SUM(B.월기본급),
    MIN(B.월기본급),
    MAX(B.월기본급)
FROM 금액제급여 B
WHERE 1 = 0;  --무조건 항상 실패하는 SQL

 

COUNT(*)	COUNT(월기본급)	COUNT(NULL)	SUM(월기본급)	MIN(월기본급)	MAX(월기본급)
0	0	0	NULL	NULL	NULL

 

COUNT는 어떠한 경우도 NULL이 아닌 0을 반환하며, GROUP BY가 없는 집계함수는 NULL을 반환합니다.

 

 

6. CASE 표현식 NULL

---

CASE 식에서 컬럼에 존재하는 모든 값에 대해 변환 처리해주지 않으면, 처리에 누락된 칼럼값은 NULL을 반환합니다.

 

SELECT
    원본컬럼값,
    CASE	원본컬럼값
    WHEN	'1' THEN '1.서울'
    WHEN	'2'	THEN '2.부산'
    END	변환컬럼값 -- ELSE '9.기타' 와 같은 처리가 필요
FROM (
    SELECT '1' 원본컬럼값 FROM DUAL UNION ALL
    SELECT '2' 원본컬럼값 FROM DUAL UNION ALL
    SELECT '3' 원본컬럼값 FROM DUAL
    )

 

원본컬럼값	변환컬럼값
1	1.서울
2	2.부산
3	NULL

 

3에 해당하는 CASE문이 없으므로 NULL을 반환합니다. 따라서 ELSE '9.기타' 와 같은 처리가 필요합니다.

 

 

NULL의 처리방법

---

1.  NULL의 비교는 IS NULL, IS NOT NULL 연산자를 사용합니다.
2. NULL을 디폴트 값으로 치환할 때 NVL을 사용합니다
3. 나머지는 CASE로 처리합니다. (DECODE는 복잡해질 수 있으므로 CASE가 편함)

 

• [퇴사일자]가 2011년 12월 1일인 데이터 찾기(NULL 허용)

SELECT * FROM 사원
WHERE 퇴사일자 = TO_DATE('20101201', 'YYYYMMDD')

 

• [퇴사일자]가 2011년 12월 1일이 아닌 데이터 찾기(NULL 허용)

SELECT * FROM 사원
WHERE (퇴사일자 <> TO_DATE('20111201', 'YYYYMMDD')
		OR 퇴사일자 IS NOT NULL)

 

'<>'은 NOT NULL인 데이터 중에서 출력하므로 꼭 IS NOT NULL 검사를 추가해야 합니다.

 

• <기타급여>의 [성과급]과 <급여지급>의 [성과급]이 같은 데이터 찾기

SELECT
	*
FROM
    급여지급 A,
    기타급여 B
WHERE
	... 
AND	((A.성과급 = B.성과급)                      --NOT NULL 비교
OR	(A.성과급 IS NULL AND B.성과급 IS NULL))    --NULL 비교

 

NULL = NULL은 UNKNOWN을 반환하므로 급여지급 혹은 기타급여 테이블에 하나라도 NULL이 있으면 제대로 비교가 되지 않습니다. 따라서 NOT NULL인 경우와 NULL인 경우 2가지를 모두 비교해야 합니다.

 

• <기타급여>의 [성과급]과 <급여지급>의 [성과급]이 다른 데이터 찾기

SELECT
	*
FROM
    급여지급 A,
    기타급여 B
WHERE
	... 
AND	((A.성과급 <> B.성과급)
	OR (A.성과급 IS NULL AND B.성과급 IS NOT NULL)
    OR (A.성과급 IS NOT NULL AND B.성과급 IS NULL))

 

NOT NULL, NULL 상황을 모두 고려해 정밀하게 조건을 만들 수 있습니다.

 

SELECT
	*
FROM
    급여지급 A,
    기타급여 B
WHERE
	... 
AND	NVL(A.성과급, 0) <> NVL(B.성과급, 0)

 

 만약 성과급의 NULL과 0이 같은 개념이라면, NVL을 이용하면 쉽게 작성할 수 있습니다. NULL과 0을 다른 개념으로 정의하면 사용할 수 없습니다.

 

• 집계함수와 NULL 처리의 조합

SELECT 부서코드,
    NVL(COUNT(*), 0) 사원수,       --COUNT(*)
    NVL(SUM(월급여), 0) 월급여     --SUM(월급여)
FROM 사원
GROUP BY 부서코드

 

COUNT(*)는 항상 NULL을 반환하지 않으므로 NVL을 할 필요가 없습니다. 마찬가지로 GROUP BY절에서 사용하는 집계함수 SUM(월급여)는 항상 NULL을 반환하지 않으므로 NVL을 할 필요가 없습니다.

 

SELECT NVL(SUM(성과급), 0) 성과급합계
FROM 급여지급 A
WHERE A.급여월 = '201106'

 

 GROUP BY 절이 아닌 경우 집계함수를 사용하면 NVL을 사용합니다. 이 때 SUM(NVL(성과급 ,0)을 사용하지 않습니다. 이는 불필요하게 모든 로우에 NVL처리를 하고 SUM을 처리하는 2가지 과정을 거쳐야 합니다. NVL(SUM(성과급), 0)을 사용하면 NULL인 경우 제외하고 계산하므로 1차 연산에서 끝납니다.

 

NVL(SUM(수당), 0) + NVL(SUM(성과급), 0)
NVL(SUM(수당 + 성과급) ,0)

 

아래 NVL(SUM(수당 + 성과급) ,0)로 계산을 하는 경우 수당 혹은 성과급 중 하나라도 NULL이라면 연산 결과는 무조건 NULL이므로 기대 값보다 더 적은 값을 반환하므로 꼭 개별적으로 NVL을 처리해야 합니다.

 

--해당 건이 없어도 NULL이라도 반환하는 MAX
SELECT NVL(MAX(이력순번), 0) + 1 이력순번
FROM 발령이력
WHERE 사원번호 = 100

--해당 건이 없으면 GROUP BY 때문에 아무것도 반환하지 못하는 MAX
SELECT NVL(MAX(이력순번), 0) + 1 이력순번
FROM 발령이력
WHERE 사원번호 = 100
GROUP BY 사원번호

 

위의 경우 집계함수를 사용하는데 GROUP BY가 없으므로 NULL을 반환하고 NVL 처리를 했으므로 정상적으로 이력 순번을 구할 수 있습니다.

 

하지만 아래의 경우 집계함수를 사용하는데 GROUP BY를 사용했습니다. GROUP BY 대상 자체가 없기 때문에 해당 쿼리문은 어떠한 행도 출력 할 수 없습니다.

 

 

출처

---

*SQL 컨셉 for ORACLE