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

Share

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

샘플 테이블 데이터

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

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

1980/12/17 00:00:00

800

 

20

7499

ALLEN

SALESMAN

7698

1981/02/20 00:00:00

1600

300

30

7521

WARD

SALESMAN

7698

1981/02/22 00:00:00

1250

500

30

7566

JONES

MANAGER

7839

1981/04/02 00:00:00

2975

 

20

7654

MARTIN

SALESMAN

7698

1981/09/28 00:00:00

1250

1400

30

7698

BLAKE

MANAGER

7839

1981/05/01 00:00:00

2850

 

30

7782

CLARK

MANAGER

7839

1981/06/09 00:00:00

2450

 

10

7788

SCOTT

ANALYST

7566

1987/03/20 00:00:00

3000

 

20

7839

KING

PRESIDENT

 

1981/11/17 00:00:00

5000

 

10

7844

TURNER

SALESMAN

7698

1981/09/08 00:00:00

1500

0

30

7876

ADAMS

CLERK

7788

1987/05/23 00:00:00

1100

 

20

7900

JAMES

CLERK

7698

1981/12/03 00:00:00

950

 

30

7902

FORD

ANALYST

7566

1981/12/03 00:00:00

3000

 

20

7934

MILLER

CLERK

7782

1982/01/23 00:00:00

1300

 

10

         

공집합의 의미

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

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 을 살펴보도록 하겠습니다. 아래 표는 이번에 사용할 샘플 데이터 입니다.

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7369

SMITH

CLERK

7902

1980/12/17 00:00:00

800

 

20

7499

ALLEN

SALESMAN

7698

1981/02/20 00:00:00

1600

300

30

7521

WARD

SALESMAN

7698

1981/02/22 00:00:00

1250

500

30

7566

JONES

MANAGER

7839

1981/04/02 00:00:00

2975

 

20

7654

MARTIN

SALESMAN

7698

1981/09/28 00:00:00

1250

1400

30

7698

BLAKE

MANAGER

7839

1981/05/01 00:00:00

2850

 

30

7782

CLARK

MANAGER

7839

1981/06/09 00:00:00

2450

 

10

7788

SCOTT

ANALYST

7566

1987/03/20 00:00:00

3000

 

20

7839

KING

PRESIDENT

 

1981/11/17 00:00:00

5000

 

10

7844

TURNER

SALESMAN

7698

1981/09/08 00:00:00

1500

0

30

7876

ADAMS

CLERK

7788

1987/05/23 00:00:00

1100

 

20

7900

JAMES

CLERK

7698

1981/12/03 00:00:00

950

 

30

7902

FORD

ANALYST

7566

1981/12/03 00:00:00

3000

 

20

7934

MILLER

CLERK

7782

1982/01/23 00:00:00

1300

 

10


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


관련된 다른 글

 

 

 

 

 

 

         

0
글에 대한 당신의 생각을 기다립니다. 댓글 의견 주세요!x