오라클(Oracle) 집계함수와 공집합의 NULL 처리

Last Updated on 9월 17, 2022 by Jade(정현호)

샘플 테이블 데이터

SCOTT의 EMP 테이블 데이터 이며 포스팅의 내용은 아래 테이블의 데이터를 통해서 진행 합니다. 
              

공집합의 의미

먼저 공집합의 의미를 확인 해보도록 하겠습니다.

SELECT MGR FROM EMP
WHERE ENAME='SCOTT';

결과: 7566 


SELECT MGR FROM EMP
WHERE ENAME='KING';
결과는?


SELECT MGR FROM EMP
WHERE ENAME='JUNG';
결과는?


그럼 아래와 같이 다시 조회를 해보겠습니다.

A)
SELECT NVL(MGR,9999) FROM EMP
WHERE ENAME='KING';

B)
SELECT NVL(MGR,9999) FROM EMP
WHERE ENAME='JUNG';


위의 2개의 쿼리가 처음 조회 했던 것처럼 동일 하게 출력(결과없음) 이 될까요?

조회 해보면

A) 는 우리가 원하는 데로 9999 로 출력 되며,   B) 는 여전히 결과가 나타나지 않습니다.


이제 다시 생각 해보면, NULL 은 아직 정의되지 않은 값으로 0 또는 공백과 다르며 0은 숫자이며, 공백은 하나의 문자 가 됩니다.
그러므로 

A)
SELECT MGR FROM EMP
WHERE ENAME='KING';

A) 의 결과는 NULL 이며

B)
SELECT MGR FROM EMP
WHERE ENAME='JUNG';

B) 의 결과는 공집합인 것 입니다.

NVL 은 NULL 값을 다른 값으로 치환 하기 위해서 사용하는 내장 함수 입니다.

B) 는 공집합으로 NULL 과는 다른 결과집합이며, 그러므로 공집합을 NVL 함수를 사용하더라도 원하는 값(9999)으로 출력 할 수가 없습니다.
                 

집계 함수 와 NULL, 공집합 처리

집계 함수와 NULL 과 관련된 공집합 처리 등을 확인 해보도록 하겠습니다 먼저 COUNT 집계 함수 부터 알아보도록 하겠습니다.
        

COUNT

A)
SELECT COUNT(*) FROM EMP
WHERE ENAME='JUNG';


B)
SELECT COUNT(MGR) FROM EMP;


C)
SELECT COUNT(MGR) FROM EMP
WHERE ENAME='KING';


D)
SELECT COUNT(*) FROM EMP;


E)
SELECT COUNT(*) FROM EMP
WHERE 1=2;


F)
SELECT COUNT(*) FROM EMP
WHERE ENAME='KING';


위의 나열 된 6개(A~F) 의 결과값은 어떻게 될 것인지를 한번 생각해 보세요

(참고로 EMP 테이블의 ROW 수는 14 입니다)


정답 아래와 같습니다.

A : 0

B : 13

C : 0

D : 0

E : 14

F : 0

G : 1


다중행 입력 함수인 집계함수는 입력값 전체가 NULL 값인 경우만 함수의 결과가 NULL 이 나오게 됩니다.
일부만 NULL 일 경우 NULL 를 제외하고 집계가 됩니다.

COUNT 의 경우 결과가 없는(공집합) 경우 와 모두 NULL 일 경우 0 으로 결과가 나타나게 됩니다.


그럼 다시 쿼리를 살펴보겠습니다.

A)
SELECT COUNT(*) FROM EMP
WHERE ENAME='JUNG';

위에서 보듯이 ENAME='JUNG' 은 결과가 없는 공집합이며 COUNT 로 조회하게 되면 공집합 이기 때문에 당연히 건수는 0 으로 출력 됩니다.


B)
SELECT COUNT(MGR) FROM EMP;

KING 제외한 모든 사원의 MANAGER(MGR) 은 존재 하며, KING 만 MGR 의 값이 없습니다.(NULL)
EMP 테이블의 총건수인 14건에서 1건(NULL) 을 제외하고 13건이 되게 됩니다.


C)
SELECT COUNT(MGR) FROM EMP
WHERE ENAME='KING';

KING 에 해당하는 MGR 은 NULL 이 되고 집계함수에서 NULL 은 집계 대상에서 제외 되기 때문에 공집합과 같이 해당 건수는 0 으로 출력 되게 됩니다.


D)
SELECT COUNT(*) FROM EMP;

전체 컬럼을 대상으로 COUNT 를 하고 있음으로 14 건이 출력 됩니다

E)
SELECT COUNT(*) FROM EMP
WHERE 1=2;

1=2 라는 구문은 조건을 거짓으로 만드는 대표적인 표기형태 임으로 A) 와 동일 하게 공집합이 되며 결과는 0 이 되게 됩니다.


F)
SELECT COUNT(*) FROM EMP
WHERE ENAME='KING';

C) 와 달리 조회대상의 컬럼이 전체 대상이기 때문에 결과는 NULL 이 아닌 1건이 추출 되며, 그러므로 건수는 1건으로 출력 됩니다.
                

SUM

이번에는 SUM 을 살펴보도록 하겠습니다. 아래 표는 이번에 사용할 샘플 데이터 입니다.

A)
SELECT SUM(COMM) FROM EMP
WHERE ENAME='JUNG';

결과 : NULL

SUM 은 입력 값의 전체 건수가 NULL 일 경우 함수의 결과가 NULL 이 되며 공집합도 동일한 결과가 출력 되게 됩니다.


B)
SELECT SUM(COMM) FROM EMP;

결과 : 2200

NULL 은 집계 대상에서 제외 됨으로 EMP테이블을 참조하면 결과는 2200(300+500+1400+0) 이 되게 됩니다.


C)
SELECT SUM(COMM) FROM EMP C
WHERE ENAME='TURNER';

결과 : 0

ENAME='TURNER' 의 COMM 값은 NULL 아닌 0 이라는 숫자로 지정 되어있기 때문에 해당 결과는 0 이 됩니다.


D)
SELECT SUM(COMM) FROM EMP
WHERE ENAME='BLAKE';

결과 : NULL
ENAME='BLAKE' 에 해당 하는 COMM 값이 NULL 이고, 그러므로 입력되는 모든 값이 NULL 이기 때문에 결과는 NULL 이 됩니다.


평균을 구하는 AVG/MIN/MAX 함수도 SUM 과 동일 하게 처리 되며 NULL 이거나 공집할 일때는 결과가 NULL 이 되며 NULL은 집계대상이 아니므로 NULL을 제외한 값으로 만 평균을 계산하게 됩니다.


SELECT AVG(COMM) FROM EMP;

결과는 550 이 되게 됩니다.

(300+500+1400+0)/4 = 550
         

집계함수와 NVL

아래의 쿼리에서 원하는 데로 ENAME 이 없는 사람도 공집합(결과없음) 이 아니라 MGR 값을 9999 로 해주기 위해서는 위에서 테스트 해본것 처럼 집계함수를 먼저 사용하여 NULL 로 출력되게 한 후 NVL 을 사용하면 NVL 처리가 가능하게 됩니다.

• AS-IS
SELECT NVL(MGR,9999) FROM EMP
WHERE ENAME='JUNG';


• TO-BE
SELECT NVL(SUM(MGR),9999) FROM EMP
WHERE ENAME='JUNG';


실제로 많이 실수 하는 부분이며, SQL 검수 시 살펴본 바로는 꽤 많이 아래와 같이 작성된 부분이 발견 되고는 합니다.

SUM(NVL(SAL,0))

개별 데이터의 SAL 값이 NULL 인 경우 NULL 의 특성으로 집계(연산) 대상에서 제외 되는데 불필요하게 NVL 함수를 사용하여 집계대상이 아닌 NULL 을 0 으로 변환시켜서 데이터 건수 만큼 연산을 하게되는 형태가 되는 것 입니다.

100+100+0 과 100+100+ NULL 의 결과는 당연히 동일하기 때문입니다. 
SUM 의 경우 불필요한 연산이지만 AVG 함수의 경우 결과 값이 달라지는 상황을 초래 하게 됩니다.


A)
SELECT TRUNC(AVG(NVL(COMM,0))) FROM EMP;
결과 : 157


B)
SELECT TRUNC(NVL(AVG(COMM),0)) FROM EMP;
결과 : 550


위의 2개의 쿼리에서 NVL의 위치가 달라짐으로써 결과도 달라지게 됩니다.

A) 에서 NVL(COMM,0) 이 먼저 수행 되기 때문에 NULL 이 모두 0 으로 변환이 되고 합계과정 후 나누게 되는 건수가 (EMP 테이블의 14건) 달라지게 되는 것 입니다.

(300+500+1400+ 0+0 + 0+ 0+ 0+0+0+0+0+0+0) / 14

빨간색  0 이 NULL에서 0 으로 치환 된 값
          

CASE/DECODE 와 NVL

CASE 표현 사용시 ELSE 절을 생략 하게 되면 DEFAULT 값은 NULL 이 되게 됩니다

위에서 여러번 설명 했던 것처럼 NULL 은 연산 대상이 아닌 반면, SUM(CASE MGR WHEN 7698 THEN COMM ELSE 0 END) 과 같이 ELSE 절에 0 을 지정 하면 이 또한 불필요하게 0 이 SUM 연산에 사용 되게 됩니다.


값은 결과를 얻을 수 있다면 아래처럼 ELSE 절을 작성하지 않는 것이 좋을 것 같습니다.

SUM(CASE MGR WHEN 7698 THEN COMM END)


DECODE 의 경우도 4번째 인자 값을 지정하지 않으면 DEFAULT 는 NULL 이 되게 됩니다.
SUM(DECODE(MGR,7698,COMM,0)) => SUM(DECODE(MGR,7698,COMM))


참고: SQL 전문가 가이드


관련된 다른 글

오라클(Oracle) NOT IN 과 NULL 허용 컬럼

현재 버전이 11gR2 임으로 _OPTIMIZER_NULL_AWARE_ANTIJOIN 파라미터를 FALSE 로 변경 하고 진행합니다.

오라클(Oracle) Smart Flash Cache - 11gR2 NF(New Features)

Oracle Smart Flash Cache Oracle 11.2부터 Database Smart Flash Cache라는 것이 추가되었으며 이 개념은 buffer cache가 Aging Out되어 Disk로 내려가는 단점을 보완 하는 기능 입니다.

오라클(Oracle) - Block Corruption에 임하는 우리들의 자세 - 오라클 블럭 복구

Oracle RMAN 을 이용하여 Block Corruption 을 복구 하는 문서 입니다.

오라클(Oracle) 11gR2 NF(New Features) - 향상된 ASM(ADVM 과 ACFS)

오라클 11gR2 ASM NF(New Features) 중 ADVM 과 ACFS 에 관한 문서 입니다.

Oracle - ASM to Non-ASM Convert using RMAN

RMAN을 이용하여 Non-ASM File 과 ASM 를 convert 하는 내용의 문서 입니다.

11gR2 NF(New Features) ASM - IDP(지능적 배포)

안녕하세요 11gR2 NF중 하나인 ASM의 IDP에 대한 정리 문서 입니다. 

Jade(정현호)

Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io