[DB] Oracle - NULL의 이해

안녕하세요 오늘도 기분 좋은 하루입니다. 오라클에 대한 이해를 확장하고자 이 글을 써보겠습니다.

 

NULL의 의미와 특성

 

NULL이란 두가지 의미가 존재한다고 생각합니다. 

 

첫번째, "아직 정해지지 않은 값 또는 알 수 없는 값"의 의미입니다. 예를들어 '사원의 확인되지 않은 생일' 이라 한다면 현재는 알 수 없는 값이라고 생각합니다. 즉, 값이 존재하나 아직 알 수 없는 경우.

 

두번째, 적용할 수 있는 값이 없는 경우 입니다. "미혼인 사원의 배우자 명"입니다. 사원은 미혼이므로 당연히 배우자 명이 없는것으로 존재 할 수 없는 경우입니다. 

 

대부분의 SQL의 NULL의 의미는 적용할 수 없는 값이라고 생각하면 될 것 같습니다. 

 

NULL 연산의 특징

 

연산	산술 연산	+, - , *. /	NULL과 산술연산한 결과는 NULL이 된다.
	비교연산	=, >, <, <>, >=, <=	비교연산에서는 NULL을 제외하고 연산한다. 1. 해당하는 값(=) => TRUE(참)' 2. 해당하지 않는 값(<>) => FALSE(참) 3. NULL => UNKNOWN
		IS NULL, IS NOT NULL	NULL 비교는  UNKNOWN이기 때문에 IS NULL, IS NOT NULL로 구분한다
함수	일반함수	TO_CHAR, LENGTH 등	함수에 NULL을 인수로 제공하면 결과는 NULL이다.  ❗️ NVL 함수 등 NULL 처리 함수는 예외이다. ❗️오라클에서 REPLACE, CONCAT, trim 등 문자열 처리 함수는 예외가 존재한다. 즉, 이들 함수는 NULL을 인수로 제공해도 NULL이 안 나올 수 있다.
	집계함수	SUM, AVG, MIN/MAX 등	집계함수는 NULL을 제외하고 연산한다.  ❗️ COUNT(*)는 예외적으로 처리한다( NULL도 계산한다는 뜻)
기타		DECODE	DECODE는 NULL을 비교할 수 있다.
		CASE	CASE 표현식은 NULL을 비교할 수 있다. CASE 표현식에서 IS NULL로 비교하는 경우를 빼곤, NULL은 ELSE절에 포함된다.

 

 

NULL이 발생하는 경우 

 

NULL이 발생하는 경우	설명 및 예시
1) NULL 허용 컬럼의 값이 NULL일 때	NULL 허용 컬럼의 값이 NULL이면, NULL이 나온다. ❗️ 데이터모델링 관점에서 본질적인 NULL이다.
2) NULL과의 연산	산술연산과 NULL의 결과는 NULL이다.
3) 아우터조인에서 조인에 실패한 아우터조인된 테이블의 컬럼 값	아우터조인에서 조인에 실패하는 경우, 아우터조인된 테이블의 컬럼 값에는 적용할 수 있는 값이 없으므로 NULL이 된다. (적용할 수 있는 값이 없는 경우)
4) 스칼라서브쿼리에서 조건에 해당하는 데이터가 없는 경우 메인쿼리의 결과 값	스칼라서브쿼리에서 조건에 해당하는 값이 없으면, 메인쿼리의 결과값은 NULL이다. 따라서 NVL 처리가 필요할 수 있다. 적용할 수 있는 값이 없는 경우이다.
5)GROUP BY 절 없이 사용한 집계함수에서 조건에 해당하는 데이터가 없을 때	GROUP BY 절 없이 사용한 집계함수에서 조건에 해당하는 값이 없을 때, COUNT를 제외한 집계함수들은 적용할 수 있는 값이 없으므로 NULL이다.
6) CASE 표현식을 이용한 컬럼 변환에서 처리에 누락되는 컬럼값	CASE 표현식 or DECODE 함수를 이용한 컬럼 변환에서 처리에 누락되는 컬럼값에는 적용할 수 있는 값이 없으므로 NULL이다.
7) 그 외 적용할 수 있는 값이 없는 모든 경우	분석함수인 LAG함수에서 첫 로우의 이전 값, LEAD함수에서의 최종 로우의 다음 값은 적용할 수 있는 값이 없으므로 NULL이다. ROLLUP에서 그룹핑 컬럼의 집계 값 역시 적용할 수 있는 값이 없으므로 NULL이다.

 

NULL의 처리 방법 

 

NULL 처리함수 이외의 알반함수는 인수로 제공되는 컬럼 값이 NULL일 때 일반적으로 NULL을 반환한다. 집계함수는 NULL을 제외하고 처리한다. 쉽게 NULL 처리방법을 3가지로 간략해보자.

첫 째, NULL의비교는 IS NULL, IS NOT NULL 연산자를 이용한다. WHERE절에서 특정 값이 NULL인지 아닌지 비교할 때는 IS NULL, IS NOT NULL 연산자를 이용한다.

 

둘 째, NULL을 디폴트 값으로 치환할 때는 NVL 함수(또는 COALESCE 함수)를 이용한다. SELECT 절이나 WHERE 절에서 NULL을 디폴트 값으로 치환할 때는 NVL 함수를 이용한다.

 

셋 째, 나머지는 CASE 표현식으로 처리한다. 위의 두 가지 외에 나머지는 모두CASE 표현식을 이용한다. 

 

WHERE절의 NULL 처리 예시문 

🌈  [문제1] [퇴사일자]가 2010년 12월 1일 데이터 찾기 ([퇴사일자]는 NULL 허용 컬럼)

 

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

 

 

 

🌈 [문제2] NULL을 포함하여 [퇴사일자]가 2010년 12월 1일 아닌 데이터 찾기 

SELECT 사원번호, 사원명, 퇴사일자
FROM 사원
WHERE 퇴사일자 <> TO_DATE('20101201', 'YYYYMMDD')

 

 

위 두 예시를 본다면 [퇴사일자]가 NOT NULL인 데이터 중에서 '2010년12월1일' 이거나 아닌 데이터만을 찾는다. 

여기서 문제점은 퇴사일자가 2010년 12월 1일이 아니지만 퇴사일자가 정해지지않은 사람들은 어떻게 할것이냐? 가 쟁점이다. 

그렇다면 NULL을 포함한다면 어떻게 해야할까? 

 

1. 퇴사일자가 2010년 12월 1일이 아니다.

혹은

2. 퇴사일자가 NULL이다 (퇴사일자가 없다는소리) 

 

SELECT 사원번호, 사원명, 퇴사일자
FROM 사원
WHERE (퇴사일자 <> TO_DATE('20101201', 'YYYYMMDD') OR 퇴사일자 IS NULL)

 

이렇게 된다면 NOT NULL이 아닌 컬럼까지 생각해서 추출할 수 있다. 

 

🌈 [문제3] <기타급여>의 [성과급]과 <급여지급>의 [성과급]이 같은 데이터 찾기 

([성과급]의 데이터 타입은 NUMBER(10)이며 NULL 허용 컬럼, 양쪽 모두 NULL일 때 같은 값으로 취급한다.)

 

SELECT 	A.사원번호
	,A.성과급	지급성과급
	,B.성과급	예상성과급
FROM     급여지급 A
	,기타급여 B
WHERE A.급여월 = "201106"
AND   A.사원번호 = B.사원번호
AND  ((A.성과급 = B.성과급)				--> 1.NOT NULL인 값을 비교
		OR (A.성과급 IS NULL AND B.성과급 IS NULL))--> 2.NULL인 값을 비교

 

❗️ 그렇다면 <기타급여>의 [성과급]과 <급여지급>의 [성과급]이 서로 다른 데이터를 찾는 쿼리라면, 어떻게 작성해야 하는지 생각해보자.

 

SELECT 	A.사원번호
	,A.성과급	지급성과급
	,B.성과급	예상성과급
FROM     급여지급 A
	,기타급여 B
WHERE A.급여월 = "201106"
AND   A.사원번호 = B.사원번호
AND  (A.성과급 <> B.성과급)				
AND  NVL(A.성과급,0) <> NVL(B.성과급,0)

 

자 기본구성은 이렇다. NVL(A,성과급) 에서 어떠한 값과  NULL이 서로  비교연산을 한다면 UNKNOWN이라고 표시될것이다. 이를 방지하기위해서 NVL을 사용하여 NULL을 0으로 만들어주고 상수끼리 서로 비교할 수 있다.

 

그런데 의문점을 제기할수 있다. NULL값이 0으로 환산되는것인가?  우리 대부분의 업무에서는 NULL값과 0을 같은 의미로 사용한다. 그렇지만 아닌 곳도 그렇다. 아닌 값 또한 방지해보자. 

 

SELECT 	A.사원번호
	,A.성과급	지급성과급
	,B.성과급	예상성과급
FROM     급여지급 A
	,기타급여 B
WHERE A.급여월 = "201106"
AND   A.사원번호 = B.사원번호
AND  (A.성과급 <> B.성과급)				
OR  (A.성과급 IS NOT NULL AND B.성과급 IS NULL) -> A는 NOT NULL, B는 NULL
OR  (A.성과급 IS NULL AND B.성과급 IS NOT NULL) -> A는 NULL, B는 NOT NULL

 

이렇게 된다면 둘다 NULL이 아닐경우 비교, A만 NULL이 아닐 경우, B만 NULL이 아닐 경우로 비교할 수 있다. 이게 만약 복잡하다면 

SELECT 	A.사원번호
	,A.성과급	지급성과급
	,B.성과급	예상성과급
FROM     급여지급 A
	,기타급여 B
WHERE A.급여월 = "201106"
AND   A.사원번호 = B.사원번호
AND  (A.성과급 <> B.성과급)				
AND  NVL(A.성과급,10000000000) <> NVL(B.성과급,10000000000)

 

아까 VARCHAR(10)인 컬럼은 성과급이다. 이때 NULL일때 치환되는 값은 10000000000 로 11자리로 치환할 수 있다. 하지만 이 값은 발생할 수 없는값. 이렇게 된다면 아까 길었던 SQL 코드를 재정리하여 더 간편하게 만들 수 있다.

 

 

SELECT 절의 NULL 처리 예시문 

기본적인 NULL 처리

 

SELECT 사원번호, 
사원명, 
NVL(퇴사일자, TO_DATE('99991231', "YYYYMMDD")) 퇴사일자
FROM 사원

 

이거는 퇴사일자가 NULL일 때 99991231로 보여주어 값을 좀 편하게 보여준다. 

 

SELECT 사원번호, 
사원명, 
CASE WHEN 퇴사일자 IS NULL
    	THEN TO_DATE('99991231', "YYYYMMDD") => NULL인 경우 디폴트 값 치환 처리
    	ELSE 퇴사일자
END
FROM 사원

 

이거또한 똑같은 위치 CASE 표현식으로도 NVL 함수를 구현할 수 있다.

 

🌈 [문제4] 집계함수와 NULL 처리의 조합 

 

집계의 결과가 NULL일 때 다음 SQL처럼 NULL을 처리하는 사례가 있다. 그러나 비효율식적인 방식이다. 
(<사원>의 [월급여]는 데이터 타입이 NUMBER(10)으로 NOT NULL 컬럼이다)

 

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

 

COUNT 함수는 절대 NULL을 추출하지않으므로 NVL 로 NULL처리는 필요없다. [월급여]컬럼 또한 NOT NULL속성이므로 불필요하다.

 

-- 1

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

-- 2

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

1,2번의 쿼리의 차이를 이해하겠는가? 결과값은 똑같을 것이다. 물론 직관적으로 차이를 물어보는걸 수도 있겠지만 지금 동작하는 SQL의 연산횟수를 고려하는게 좋을것이다.

 

성과급	SUM(NVL(성과급,0))		연산 순서	NVL(SUM(성과급,0))
	1차 연산	2차 연산		1차 연산	2차 연산
	NVL 처리	SUM 처리		SUM 처리	NVL 처리
{NULL}	0	0	1	제외
200000	200000	0 + 200000	2	200000
{NULL}	0	200000 + 0	3	제외
{NULL}	0	200000 + 0	4	제외
200000	200000	200000 + 200000	5	200000
수행횟수	5회 수행	5회 수행	6	2회 수행	최종 1회 수행

 

 

NVL을 먼저 처리하는 계산식은 모든 ROW에 NULL 처리 함수를 적용하고, 그 이후에 불필요한 집계함수를 사용해야한다.

그러나 SUM을 먼저 한다면 NULL이 아닌 값만 집계하고 최종 결과에만 NULL만 수행한다.  이렇게 NULL 처리를 어떻게 하는지 함수를 잘 이해할 수 있다면 속도 측면에서도 유리할 수 있다는 생각을 가질 수 있다. 

 

🌈  [문제4] CASE 표현식의 NULL 처리 

 

<사원> 테이블에서 [퇴사일자]가 NULL이면 '재직', 2010년 1월 1일보다 작으면 '퇴직' 2010년 1월1일과 같거나 크면 '재직'으로 표현하는 쿼리를 만들어보자.

 

SELECT 사원번호
    ,사원명
    ,부서코드
    ,퇴사일자
    ,CASE
        WHEN 퇴사일자 IS NULL
        THEN '재직'
        WHEN 퇴사일자 < TO_DATE('20100101', 'YYYYMMDD')
        THEN '퇴직'
        WHEN 퇴사일자 >= TO_DATE('20100101', 'YYYYMMDD')
        THEN '재직'
    END 직원상태
FROM 사원

 

윗 코드를 보면 모든 상태를 적어 놓았다. 하지만 우리는 CASE WHEN에 나오는 ELSE문을 생각해봐야한다. ELSE문은 NULL이 포함된다는 사실을 알면된다 .

 

SELECT 사원번호
    ,사원명
    ,부서코드
    ,퇴사일자
    ,CASE
        WHEN 퇴사일자 < TO_DATE('20100101', 'YYYYMMDD')
        THEN '퇴직'
		ELSE '재직'
    END 직원상태
FROM 사원

 

이렇게 된다면 퇴사일자 IS NULL과 TO_DATE(20100101) 부분을 묶어서 ELSE로 사용할 수 있다. 

 

조금만 더 응용해서 문제를 마지막으로 풀어보자

 

🌈 [문제6] <사원>을 기준으로 <급여지급>과 아우터조인하여, <급여지급>의 [급여월]이 2010년 1월인 데이터가 있으면 <급여지급>의 [월급여]를 보여주고 없으면 <사원>의 [월급여]를 보여주되, 만약 <사원>의 [월급여]가  NULL이면 0으로 보여주는 쿼리를 작성해보자.

 

❗️<사원>의 [월급여]는 NULL 허용컬럼이고, <급여지급>의 [월급여]는 NOT NULL 컬럼으로 가정한다.

SELECT A.사원번호
    ,A.사원명
    ,A.부서코드
    ,A.월급여
    ,B.월급여
    ,NVL(B.월급여, NVL(A.월급여,0)) 월급여1
    ,COALESEC (B.월급여, A,월급여,0) 월급여2
    ,CASE
        WHEN B.월급여 IS NOT NULL THEN B.월급여 --> B.월급여가 NOT NULL이면 B.월급여 이용
        WHEN A.월급여 IS NOT NULL THEN A.월급여 --> A.월급여가 NOT NULL이면 A.월급여 이용
        ELSE 0 월급여0
    END
FROM 사원 A
,급여지급 B
WHERE B.급여월(+) = '201001'
AND B.사원번호(+) = A.사원번호

 

[월급여1],[월급여2],[월급여0]는 모두 같은 의미를 내포하고있다. NVL함수를 두번 사용하거나 COALESCE 함수를 사용하는 것이 좀 더 효율적으로 보인다. 단순히 NULL을 사용할 때에는 NVL, COALESCE 함수를 사용하는 것이 실제로 편리해보인다. 하지만 복잡한 쿼리로 NULL처리 함수를 사용하기에는 어려운 감이 있다. 이렇게 [월급여0]의 쓰임은 아마 복잡한 NULL 처리함수에는 CASE 표현식을 사용해야 SQL이 쉽고 명확하지 않을까란 생각이다. 

 

이렇게 확실하게 깊게 파고들어가지 않았지만 NULL을 어떻게 사용하고 효율적으로 사용해야지에 대한 환경을 조금이나마 이해할 수 있었다. 다음에는 DB에 대해서 더 효율적이고 편리하게 알아야겠다.