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

Last Updated on 10월 8, 2020 by 태랑(정현호)

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

SCOTT의 EMP 테이블 데이터

1.먼저 공집합의 의미부터 살펴보자

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)으로 출력 할수 없는 것이다.

2. 집계 함수와 NULL 그리고 공집합 처리

2-1 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건으로 출력 된다.

2-2 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

3. 집계함수 와 NVL

위쪽에서 본 아래의 쿼리에서 원하는 데로 ENAME 이 없는 사람도 공집합(결과없음) 이 아니라

MGR 값을 9999 로 해주기 위해서는 위에서 테스트 해본것 처럼 집계함수를 먼저 사용하여

NULL 로 출력되게 한 후 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 으로 치환 된 값

4. 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 전문가 가이드

태랑(정현호)

Senior DBA(Mysql, Oracle) - 현재 위메프에서 많은 새로움을 경험중입니다
At WeMakePrice / Previous - Oracle Korea ACS Support / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io

hoing.io