2.1. 조인을 활용한 데이터 연결 (by hiphop4775) [2009.05.01]
1.조인에 대한 잘못된 생각
- 조인은 관련된 다른 집합을 찾아오는 것이 아니다.
- EQUAL(=)로만 조인하는 것이 아니다.
2.조인에 대한 올바른 생각
- 조인은 집합간의 곱이다.
ex) 1 * M = M
M * 1 = M
1 * 1 = 1
M * M = MM - 모든 연산자가 연결조건이 될 수 있다.
3.실습
(1) ERD
(2) 테이블 설계
{*}1)급여체계(SAL)(b)*
직급(JOB_GRD/PK),호봉(SAL_CLASS/PK),기본급(BASE_SAL)
{*}2)사원(EMP)(a)*
사번(EMPNO/PK),성명(NAME),주소(ADDR),직급(JOB_GRD/FK),호봉(SAL_CLASS),직무(JOB_KIND),퇴직일(RES_DATE)
{*}3)가족(FAM)(d)*
일련번호(SEQ/PK),관계(REL),성명(NAME),사번(EMPNO/FK)
{*}4)근태(WORK)©*
일자(WORK_DATE/PK),사번(EMPNO/PK/FK),근태코드(WORK_CD),근무시간(WORK_TIM)
(3) CREATE TABLE
SQL>
CREATE TABLE ORA9.SAL (
2 JOB_GRD VARCHAR2(10) NOT NULL
3 , SAL_CLASS VARCHAR2(2) NOT NULL
4 , BASE_SAL NUMBER(10) NOT NULL
5 )
6 TABLESPACE ORA9;
테이블이 생성되었습니다.
SQL>
CREATE TABLE ORA9.EMP (
2 EMPNO VARCHAR2(10) NOT NULL
3 , NAME VARCHAR2(10) NOT NULL
4 , ADDR VARCHAR2(30) NOT NULL
5 , JOB_GRD VARCHAR2(10) NOT NULL
6 , SAL_CLASS VARCHAR2(2) NOT NULL
7 , JOB_KIND VARCHAR2(10) NOT NULL
8 , RES_DATE VARCHAR2(8) NULL
9 )
10 TABLESPACE ORA9;
테이블이 생성되었습니다.
SQL>
CREATE TABLE ORA9.FAM (
2 SEQ VARCHAR2(10) NOT NULL
3 , REL VARCHAR2(10) NOT NULL
4 , NAME VARCHAR2(10) NOT NULL
5 , EMPNO VARCHAR2(10) NOT NULL
6 )
7 TABLESPACE ORA9;
테이블이 생성되었습니다.
SQL>
CREATE TABLE ORA9.WORK (
2 WORK_DATE VARCHAR2(8) NOT NULL
3 , EMPNO VARCHAR2(10) NOT NULL
4 , WORK_CD VARCHAR2(10) NOT NULL
5 , WORK_TIM VARCHAR2(10) NOT NULL
6 )
7 TABLESPACE ORA9;
테이블이 생성되었습니다.
(4) CREATE PK
SQL>
ALTER TABLE ORA9.SAL ADD (
2 CONSTRAINT SAL_PK PRIMARY KEY ( JOB_GRD,SAL_CLASS )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
ALTER TABLE ORA9.EMP ADD (
2 CONSTRAINT EMP_PK PRIMARY KEY ( EMPNO )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
ALTER TABLE ORA9.FAM ADD (
2 CONSTRAINT FAM_PK PRIMARY KEY ( SEQ )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
ALTER TABLE ORA9.WORK ADD (
2 CONSTRAINT WORK_PK PRIMARY KEY ( WORK_DATE,EMPNO )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
(5) CREATE FK
SQL>
ALTER TABLE ORA9.SAL ADD (
2 CONSTRAINT SAL_PK PRIMARY KEY ( JOB_GRD,SAL_CLASS )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
ALTER TABLE ORA9.EMP ADD (
2 CONSTRAINT EMP_PK PRIMARY KEY ( EMPNO )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
ALTER TABLE ORA9.FAM ADD (
2 CONSTRAINT FAM_PK PRIMARY KEY ( SEQ )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
SQL>
ALTER TABLE ORA9.WORK ADD (
2 CONSTRAINT WORK_PK PRIMARY KEY ( WORK_DATE,EMPNO )
3 USING INDEX TABLESPACE ORA9 );
테이블이 변경되었습니다.
(6) INSERT
(7) SELECT COUNT(ALL)
SQL>
SELECT COUNT(ALL)
2 FROM ORA9.SAL;
COUNT(ALL)
=========
10
SQL>
SELECT COUNT(ALL)
2 FROM ORA9.EMP;
COUNT(ALL)
=========
20
SQL>
SELECT COUNT(ALL)
2 FROM ORA9.FAM;
COUNT(ALL)
=========
40
SQL>
SELECT COUNT(ALL)
2 FROM ORA9.WORK;
COUNT(ALL)
=========
60
(8) 실습 1
{*}1)조인은 연결 조건을 만족하는 다른 집합의 데이터를 찾아오는 것이 아니다.*
{*}2)결과{*}
SQL>
SELECT a.JOB_GRD AS EMP_FK1
2 ,a.SAL_CLASS AS EMP_FK2
3 ,b.JOB_GRD AS SAL_PK1
4 ,b.SAL_CLASS AS SAL_PK2
5 ,b.BASE_SAL
6 FROM ORA9.EMP a, ORA9.SAL b
7 WHERE a.JOB_GRD = b.JOB_GRD
8 AND a.SAL_CLASS = b.SAL_CLASS;
EMP_FK1 EMP_FK2 SAL_PK1 SAL_PK2 BASE_SAL
======= ======= ======= ======= ========
사원 1 사원 1 800000
사원 1 사원 1 800000
사원 1 사원 1 800000
사원 2 사원 2 1000000
사원 2 사원 2 1000000
대리 1 대리 1 1200000
대리 1 대리 1 1200000
대리 2 대리 2 1400000
대리 2 대리 2 1400000
과장 1 과장 1 1600000
과장 1 과장 1 1600000
과장 2 과장 2 1800000
과장 2 과장 2 1800000
과장 2 과장 2 1800000
차장 1 차장 1 2000000
차장 1 차장 1 2000000
차장 1 차장 1 2000000
차장 2 차장{color:#ff0000} 2 2200000{color}
부장 1 부장 1 2400000
부장 2 부장 2 2600000
20 개의 행이 선택되었습니다.
(9) 실습 2
{*}1)조인한 집합에서 1쪽의 집합에 있던 컬럼을 SUM한다면 잘못된 결과를 얻게 된다.*
{*}2)결과{*}
SQL>
SELECT (800000 + 1000000 + 1200000 + 1400000 + 1600000
2 + 1800000 + 2000000 + 2200000 + 2400000 + 2600000) 원하는값
3 FROM SYS.DUAL;
원하는값
=======
17000000
SQL>
SELECT SUM(b.BASE_SAL) 잘못된값
2 FROM ORA9.EMP a, ORA9.SAL b
3 WHERE a.JOB_GRD = b.JOB_GRD
4 AND a.SAL_CLASS = b.SAL_CLASS;
잘못된값
=======
31400000
SQL>
SELECT SUM(원하는값.정상값) 정상값
2 FROM ( SELECT a.JOB_GRD
3 ,a.SAL_CLASS
4 ,MAX(b.BASE_SAL) 정상값
5 ,SUM(b.BASE_SAL)
6 FROM ORA9.EMP a, ORA9.SAL b
7 WHERE a.JOB_GRD = b.JOB_GRD
8 AND a.SAL_CLASS = b.SAL_CLASS
9 GROUP BY a.JOB_GRD,a.SAL_CLASS,b.BASE_SAL) 원하는값;
정상값
=======
17000000
(10) 실습 3
{*}1)카테시안 곱과 GROUP BY시 MAX/MIN 함수 사용{*}
{*}2)결과{*}
SQL>
SELECT a.EMPNO, b.WORK_DATE
2 FROM ORA9.EMP a, ORA9.WORK b
3 WHERE a.EMPNO = b.EMPNO;
EMPNO WORK_DATE
====== ==========
0001 20090401
0001 20090402
0001 20090403
0002 20090401
0002 20090402
0002 20090403
0003 20090401
0003 20090402
0003 20090403
0004 20090402
0004 20090403
0004 20090401
0005 20090402
0005 20090403
0005 20090401
0006 20090402
0006 20090403
0006 20090401
0007 20090402
0007 20090403
0007 20090401
0008 20090402
0008 20090403
0008 20090401
0009 20090402
0009 20090403
0009 20090401
0010 20090402
0010 20090403
0010 20090401
0011 20090402
0011 20090403
0011 20090401
0012 20090402
0012 20090403
0012 20090401
0013 20090402
0013 20090403
0013 20090401
0014 20090402
0014 20090403
0014 20090401
0015 20090402
0015 20090403
0015 20090401
0016 20090402
0016 20090403
0016 20090401
0017 20090402
0017 20090403
0017 20090401
0018 20090402
0018 20090403
0018 20090401
0019 20090402
0019 20090403
0019 20090401
0020 20090402
0020 20090403
0020 20090401
60 개의 행이 선택되었습니다.
SQL>
SELECT a.EMPNO, b.MAX_WORKDT
2 FROM ORA9.EMP a,
3 ( SELECT EMPNO,MAX(WORK_DATE) MAX_WORKDT
4 FROM ORA9.WORK
5 GROUP BY EMPNO) b
6 WHERE a.EMPNO = b.EMPNO;
EMPNO MAX_WORKDT
====== ===========
0001 20090403
0002 20090403
0003 20090403
0004 20090403
0005 20090403
0006 20090403
0007 20090403
0008 20090403
0009 20090403
0010 20090403
0011 20090403
0012 20090403
0013 20090403
0014 20090403
0015 20090403
0016 20090403
0017 20090403
0018 20090403
0019 20090403
0020 20090403
20 개의 행이 선택되었습니다.
(11) 실습 4
{*}1)급여체계,사원,가족,근태 네개의 엔터티를 하나의 SQL로 조인하여 구현{*}
{*}2)실행 과정{*}
SQL1>
SELECT a.EMPNO 사원번호
2 ,COUNT(DISTINCT a.NAME) * 10000 가족수당
3 ,SUM(DECODE(c.WORK_CD,'결근',0,10000)) "만근수당(잘못된값)"
4 ,COUNT(DECODE(c.WORK_CD, '결근', NULL, '출장', NULL, 1)) * 3500 "중식비(잘못된값)"
5 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c, ORA9.FAM d
6 WHERE b.JOB_GRD = a.JOB_GRD
7 AND b.SAL_CLASS = a.SAL_CLASS
8 AND c.EMPNO = a.EMPNO
9 AND c.WORK_DATE between '20090401' and '20090402'
10 AND d.EMPNO = c.EMPNO
11 AND a.RES_DATE IS NULL
12 GROUP BY a.EMPNO;
사원번호 가족수당 만근수당(잘못된값) 중식비(잘못된값)
====== ====== ============== ============
0001 10000 40000 14000
0002 10000 40000 14000
0003 10000 40000 14000
0004 10000 40000 14000
0005 10000 40000 14000
0006 10000 40000 14000
0007 10000 40000 14000
0008 10000 40000 14000
0009 10000 40000 14000 \--> 2배 증가된 잘못된 값
0010 10000 40000 14000
0011 10000 40000 14000
0012 10000 40000 14000
0013 10000 40000 7000
0014 10000 40000 14000
0015 10000 40000 14000
0016 10000 40000 14000
0017 10000 40000 14000
0018 10000 40000 14000
0019 10000 40000 14000
0020 10000 20000 0
20 개의 행이 선택되었습니다.
SQL2>
SELECT COUNT(ALL)
2 FROM ORA9.EMP a, ORA9.SAL b
3 WHERE a.RES_DATE IS NULL
4 AND b.JOB_GRD = a.JOB_GRD
5 AND b.SAL_CLASS = a.SAL_CLASS;
COUNT(ALL)
==========
20
SQL3>
SELECT COUNT(ALL)
2 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c
3 WHERE a.RES_DATE IS NULL
4 AND b.JOB_GRD = a.JOB_GRD
5 AND b.SAL_CLASS = a.SAL_CLASS
6 AND c.EMPNO = a.EMPNO
7 AND c.WORK_DATE between '20090401' and '20090402';
COUNT(ALL)
==========
40
SQL4>
SELECT COUNT(ALL)
2 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c, ORA9.FAM d
3 WHERE a.RES_DATE IS NULL
4 AND b.JOB_GRD = a.JOB_GRD
5 AND b.SAL_CLASS = a.SAL_CLASS
6 AND c.EMPNO = a.EMPNO
7 AND c.WORK_DATE between '20090401' and '20090402'
8 AND d.EMPNO = c.EMPNO;
COUNT(ALL)
==========
80
SQL5>
SELECT SUM(DECODE(WORK_CD,'결근',0,10000)) "만근수당(정상값)"
2 ,COUNT(DECODE(WORK_CD, '결근', NULL, '출장', NULL, 1)) * 3500 "중식비(정상값)"
3 FROM ORA9.WORK
4 WHERE WORK_DATE between '20090401' and '20090402'
5 AND EMPNO = '0001';
만근수당(정상값) 중식비(정상값)
============ ==========
20000 7000
SQL6>
SELECT *
2 FROM ORA9.WORK
3 WHERE WORK_DATE between '20090401' and '20090402'
4 AND EMPNO = '0001';
WORK_DATE EMPNO WORK_CD WORK_TIM
========== ====== ======== =========
20090401 0001 출근 16
20090402 0001 출근 18
SQL7>
SELECT SUM(DECODE(c.WORK_CD,'결근',0,10000)) "만근수당(정상값)"
2 ,COUNT(DECODE(c.WORK_CD, '결근', NULL, '출장', NULL, 1)) * 3500 "중식비(정상값)"
3 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c
4 WHERE b.JOB_GRD = a.JOB_GRD
5 AND b.SAL_CLASS = a.SAL_CLASS
6 AND c.EMPNO = a.EMPNO
7 AND c.WORK_DATE between '20090401' and '20090402'
8 AND a.RES_DATE IS NULL
9 AND a.EMPNO = '0001';
만근수당(정상값) 중식비(정상값)
============ ==========
20000 7000
SQL8>
SELECT *
2 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c
3 WHERE b.JOB_GRD = a.JOB_GRD
4 AND b.SAL_CLASS = a.SAL_CLASS
5 AND c.EMPNO = a.EMPNO
6 AND c.WORK_DATE between '20090401' and '20090402'
7 AND a.RES_DATE IS NULL
8 AND a.EMPNO = '0001';
EMPNO NAME ADDR JOB_GRD SAL_CLASS JOB_KIND RES_DATE JOB_GRD SAL_CLASS BASE_SAL WORK_DATE EMPNO WORK_CD WORK_TIM
====== ===== ===== ======== ========= ======== ======== ======= ========= ======== ========== ====== ======== ========
0001 오라구 신림동 사원 1 개발 사원 1 800000 20090401 0001 출근 16
0001 오라구 신림동 사원 1 개발 사원 1 800000 20090402 0001 출근 18
SQL9>
SELECT COUNT(DISTINCT a.NAME) * 10000 가족수당
2 ,SUM(DECODE(c.WORK_CD,'결근',0,10000)) "만근수당(잘못된값)"
3 ,COUNT(DECODE(c.WORK_CD, '결근', NULL, '출장', NULL, 1)) * 3500 "중식비(잘못된값)"
4 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c, ORA9.FAM d
5 WHERE b.JOB_GRD = a.JOB_GRD
6 AND b.SAL_CLASS = a.SAL_CLASS
7 AND c.EMPNO = a.EMPNO
8 AND c.WORK_DATE between '20090401' and '20090402'
9 AND d.EMPNO = c.EMPNO
10 AND a.RES_DATE IS NULL
11 AND a.EMPNO = '0001';
가족수당 만근수당(잘못된값) 중식비(잘못된값)
====== ============== ============
10000 40000 14000
SQL10>
SELECT *
2 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c, ORA9.FAM d
3 WHERE b.JOB_GRD = a.JOB_GRD
4 AND b.SAL_CLASS = a.SAL_CLASS
5 AND c.EMPNO = a.EMPNO
6 AND c.WORK_DATE between '20090401' and '20090402'
7 AND d.EMPNO = c.EMPNO
8 AND a.RES_DATE IS NULL
9 AND a.EMPNO = '0001';
EMPNO NAME ADDR JOB_GRD SAL_CLASS JOB_KIND RES_DATE JOB_GRD SAL_CLASS BASE_SAL WORK_DATE EMPNO WORK_CD WORK_TIM SEQ REL NAME EMPNO
====== ===== ===== ======== ========= ======== ======== ======= ========= ======== ========== ====== ======== ======== === === ===== ======
0001 오라구 신림동 사원 1 개발 사원 1 800000 20090401 0001 출근 16 000101 부 오라구F 0001
0001 오라구 신림동 사원 1 개발 사원 1 800000 20090402 0001 출근 18 000101 부 오라구F 0001
0001 오라구 신림동 사원 1 개발 사원 1 800000 20090401 0001 출근 16 000102 모 오라구M 0001
0001 오라구 신림동 사원 1 개발 사원 1 800000 20090402 0001 출근 18 000102 모 오라구M 0001
SQL11>
SELECT a.EMPNO 사원번호
2 ,COUNT(DISTINCT a.NAME) * 10000 가족수당
3 ,SUM(DECODE(c.WORK_CD,'결근',0,10000)) "만근수당(정상값)"
4 ,COUNT(DECODE(c.WORK_CD, '결근', NULL, '출장', NULL, 1)) * 3500 "중식비(정상값)"
5 FROM ORA9.EMP a, ORA9.SAL b, ORA9.WORK c,
6 ( SELECT *
7 FROM ORA9.FAM
8 GROUP BY EMPNO) d
9 WHERE b.JOB_GRD = a.JOB_GRD
10 AND b.SAL_CLASS = a.SAL_CLASS
11 AND c.EMPNO = a.EMPNO
12 AND c.WORK_DATE between '20090401' and '20090402'
13 AND d.EMPNO = c.EMPNO
14 AND a.RES_DATE IS NULL
15 GROUP BY a.EMPNO;
사원번호 가족수당 만근수당(정상값) 중식비(정상값)
====== ====== ============ ==========
0001 10000 20000 7000
0002 10000 20000 7000
0003 10000 20000 7000
0004 10000 20000 7000
0005 10000 20000 7000
0006 10000 20000 7000
0007 10000 20000 7000
0008 10000 20000 7000
0009 10000 20000 7000
0010 10000 20000 7000
0011 10000 20000 7000
0012 10000 20000 7000
0013 10000 20000 3500
0014 10000 20000 7000
0015 10000 20000 7000
0016 10000 20000 7000
0017 10000 20000 7000
0018 10000 20000 7000
0019 10000 20000 7000
0020 10000 10000 0
20 개의 행이 선택되었습니다.
3)주석(NESTED LOOPS 조인시)
\* 사원 테이블(a)에서 근무중인 사원 체크(11 행)
\* 사원 테이블(a)의 직급/호봉(PK)으로 급여체계 테이블(b)에 연결 : SQL2의 결과(20 건)
\* 사원 테이블(a)의 사번(PK)으로 근태 테이블©의 근무 일자 범위만큼 연결(8,9 행) : SQL3의 결과(40) 건
\* 2배 증가된 로우마다 읽어둔 근태 테이블©의 사번(FK)으로 가족 테이블(d)과 연결(10 행) : SQL4의 결과(80) 건
\* 반복 수행
\*전체 집합에 대한 GROUP BY/필요한 연산(2,3,4,12) : SQL1의 결과(20 건)
4)수행결과 검증
\*근무 테이블에서 사원번호가 '0001'인 사원에 대해서만 만근수당과 중식비를 계산 : SQL5/SQL6의 결과(정상값)
\*사원/급여체계/근무 테이블을 조인하면서 사원번호가 '0001'인 사원에 대해서만 만근수당과 중식비를 계산 : SQL7/SQL8의 결과(정상값)
\*사원/급여체계/근무/가족 테이블을 조인하면서 사원번호가 '0001'인 사원에 대해서만 만근수당과 중식비를 계산 : SQL9/SQL10의 결과(가족의 수에 따라 증가된 잘못된 값)
\*이 SQL에서는 가족수당을 계산할때 가족의 수는 관계없이 가족의 유무만을 고려하므로 사원번호별로 중복제거를 한 후, 만근수당과 중식비를 계산 : SQL11의 결과(정상값)
5)결론
문제점 : 하나 이상의 로우를 가지는 집합을 방사형으로 여러 개를 조인시 카테시안 곱만큼의 집합이 생긴다.
수행속도 측면에서 매우 비효율적이고 수행결과도 잘못 되었음.
해결방안 : SQL을 분리하여 절차형 처리 \-> 처리를 복잡하게 만들고 수행속도를 저하시킴
모든 M을 1로 만든 후에 조인 \-> 효율적임
(12) 의문점
\* SQL 분리시 수행속도의 저하
1)SQL 통합(12 시간 소요)
2)SQL 분리(4 시간 소요)
4.기타
(1)조인시 결과만 유일하다면 어떤 비교 연산자도 사용 가능하다.
- 어음수불 엔터티/지급어음 엔터티
- 지급어음 엔터티에 수불일련번호(FK) 생성없이
어음번호와 어음시작번호/어음종료번호로 이미 1:M 관계를 맺고 있음 - BETWEEN으로 조인했더라도 반드시 상위 엔터티의 유일한 로우와 연결됨
SELECT ..............
FROM 어음수불 x, 지급어음 y
WHERE y.어음번호 BETWEEN x.어음시작번호 and x.어음종료번호;
(2)정보통신회사의 사례
- 고객 가입시 전화번호 부여, 미리 낱개의 로우로 분할하여 관리
- 사용 상태에 따라 고객이 원하는 번호
- 대리점이 연속된 임시번호를 할당받을 때 문제 발생
SELECT 국번호, 시작번호, 종료번호, 개수, 골든번호등급
FROM 전화번호
WHERE 사용상태 = '미사용'
AND 개수 >= 50
AND 시작번호 >= 2000
AND 종료번호 <= 2999
AND ROWNUM = 1;
(3)조인이란 로우를 다루는 것이 아니라 집합을 다루는 것{}