안녕하세요 오늘도 기분 좋은 하루입니다. 오라클에 대한 이해를 확장하고자 이 글을 써보겠습니다.
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에 대해서 더 효율적이고 편리하게 알아야겠다.