RSS구독하기:SUBSCRIBE TO RSS FEED
즐겨찾기추가:ADD FAVORITE
글쓰기:POST
관리자:ADMINISTRATOR
'NULL'에 해당되는 글 1


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

 

 

 


SCOTTEMP 테이블 데이터

 

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

 

 

 

 

 

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 을 살펴보자

 

 

 

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

 

 

 

 

 

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




페도라 한국 사용자 모임

페도라 한국 사용자 모임 태랑의 포스팅글입니다
  파이어폭스에 최적화 되어있습니다
2012/04/22 02:43 2012/04/22 02:43

이 포스트가 유용하셨다면 구독하세요

http://www.commit.co.kr/trackback/118
태랑:Extreme Performance DBA 를 꿈꾼다
admin@commit.co.kr / 
purityboy83@gmail.com
Extreme Performance DBA 를 꿈꾼다 admin@commit.co.kr / purityboy83@gmail.com
전체보기 (112)
Solaris (14)
Linux (45)
DATABASE (32)
Enterprise Manager (4)
Virtualization (0)
JAVA / C / 그외 개발언어 (3)
넓은 세상의 이야기들 (14)

Tag List