CREATE TABLE T_MANAGER (UTILITY VARCHAR2(20) NOT NULL,
MAIN_EMP NUMBER(4) NOT NULL,
SUB1_EMP NUMBER(4),
SUB2_EMP NUMBER(4),
CONSTRAINT MAN_PK PRIMARY KEY (UTILITY,MAIN_EMP)
);
INSERT INTO T_MANAGER VALUES ('소방시설',7366,NULL,NULL);
INSERT INTO T_MANAGER VALUES ('전기시설',7499,7521,NULL);
INSERT INTO T_MANAGER VALUES ('냉방시설',7566,7654,7844);
INSERT INTO T_MANAGER VALUES ('수도시설',7876,7900,7844);
INSERT INTO T_MANAGER VALUES ('전산장비',7782,NULL,NULL);
INSERT INTO T_MANAGER VALUES ('저장시설',7698,NULL,NULL);
INSERT INTO T_MANAGER VALUES ('수송설비',8854,NULL,NULL);
다수 NULL허용 COLUMN의 명칭을 가져올때.
조건1.
T_MANAGER TABLE 에 유틸리티 명칭을 KEY로 하여
MAIN_EMP,SUB1_EMP,SUB2_EMP 세개의 사번을 가진다.
유틸리티 정관리자,부관리자1,부관리자2 를 나타낸다.
UTILITY MAIN_EMP SUB1_EMP SUB2_EMP
-------------------- ---------- ---------- ----------
소방시설 7566
전기시설 7499 7521
수도시설 7876 7900 7844
전산장비 7782
저장시설 7698
냉방시설 7566 8915 8948
다음은 ORACLE SCOTT/TIGER 로 접속하면 흔히 볼 수 있는 EMP TABLE이다.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- -------- --------- ----- --------- ------ ------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
문제1.MAIN_EMP 는 NOT NULL 이며
SUB1_EMP 와 SUB2_EMP 는 NULL을 허용한다.
즉,정관리자는 반드시 입력되고 부관리자는 있을 수도 없을 수도 있다.
각 관리자에 대한 명칭을 읽어오는데 모두 EMP 에 명칭이 존재한다.
다음과 같은 결과가 나오도록 QUERY를 구성한다.
UTILITY MAIN_EMP ENAME SUB1_EMP ENAME SUB2_EMP ENAME
---------- ---------- ------ ---------- ------- ---------- -------
수도시설 7876 ADAMS 7900 JAMES 7844 TURNER
냉방시설 7566 JONES 8915 8948
전기시설 7499 ALLEN 7521 WARD
소방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
SUB1_EMP 또는 SUB2_EMP 가 NULL이더라도 MAIN_EMP 와 그명칭은 반드시
가져와야 한다.
생각1.
OUTER JOIN의 기본적인 이해를 돕기위한 예제이다.
해법1.
요점:EMP TABLE 을 세번 ACCESS 하여 EMPNO 로 EQUAL JOIN을 각각
걸어준다.
SUB1_EMP 와 SUB2_EMP 가 서로다른 ALIAS 를 가진 EMP TABLE과 OUTER
JOIN 으로 연결됨으로 EMP 에 NULL 인 ID 가 없어도
T_MANAGER 의 DATA를 가져올 수 있다.
SELECT A.UTILITY,
A.MAIN_EMP,
B.ENAME,
A.SUB1_EMP,
C.ENAME,
A.SUB2_EMP,
D.ENAME
FROM EMP D,
EMP C,
EMP B,
T_MANAGER A
WHERE A.UTILTIY > ' '
AND A.MAIN_EMP > ' '
AND B.EMPNO = A.MAIN_EMP
AND C.EMPNO(+) = A.SUB1_EMP
AND D.EMPNO(+) = A.SUB2_EMP
PLAN :
SELECT STATEMENT Optimizer=CHOOSE
MERGE JOIN (OUTER)
SORT (JOIN)
MERGE JOIN (OUTER)
SORT (JOIN)
NESTED LOOPS
TABLE ACCESS (FULL) OF EMP
TABLE ACCESS (BY INDEX ROWID) OF T_MANAGER
INDEX (RANGE SCAN) OF MAN_PK (UNIQUE)
SORT (JOIN)
TABLE ACCESS (FULL) OF EMP
SORT (JOIN)
TABLE ACCESS (FULL) OF EMP
A TABLE에 서로다른 두개의 TABLE C,D 가 각각 OUTER-JOIN으로
연결 되어있다.
같은 EMP TABLE 이지만 ALIAS를 다르게 주어 마치 다른 TABLE
인 것처럼 이용을 하고 있다.
뒷풀이.
OUTER JOIN의 기본 사용법을 익혔으니 다음 문제를 풀어보자.
코드의 명칭을 양쪽에서 가져올때.
조건2.
T_MANAGER TABLE에 다음과 같은 DATA가 추가되었다.
UTILITY MAIN_EMP SUB1_EMP SUB2_EMP
-------------------- ---------- ---------- ----------
수송설비 8854
그리고 냉방시설의 부관리자와 수송설비의 정관리자는 모두
계약직사원이다.
따라서 계약직 사원 관리TABLE인 EMP_TEMP 에 존재며 EMP에는 없다.
정사원은 7로 시작되며 계약직 사원은 8로 시작된다.
문제2.
우선 MAIN_EMP만의 성명을 읽어올 경우를 생각해 보자.
EMP와 EMP_TEMP 중 하나에 반드시 그 사번과 성명이 존재하는 경우이다.
정직원과 계약직 직원의 구분이 없으므로 양쪽을 다 찾아야한다.
순서는 INDEX에 따라 변경 가능하므로 신경쓰지 않아도 좋다.
결과는 다음과 같다.
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
생각2.
앞서 다루었던 문제와 유사하지만 차이가 있다면 경우에 따라
명칭을 차별적으로 보여줘야 한다는 것이다.
T_MANAGER를 기준으로 EMP와 EMP_TEMP에 OUTER JOIN을 사용함으로하여,
EMP와 EMP_TEMP 두개의 TEABL에 명칭을 찾기위해 ACCESS를
시도 할 것이며,OUTER JOIN이기에 없으면 NULL을,
있으면 명칭을 가져올 것이다.
그래도 다행인것은 두군데중에 한군데는 반드시 있는 경우이다.
어찌하나?.
해법2.
일단 문제1을 참고삼아 다음 문장을 만들어 보자.
SELECT A.UTILITY,
A.MAIN_EMP,
B.ENAME,
C.ENAME
FROM EMP C,
EMP_TEMP B,
T_MANAGER A
WHERE A.MAIN_EMP = B.EMPNO(+)
AND A.MAIN_EMP = C.EMPNO(+)
UTILITY MAIN_EMP ENAME ENAME
-------------------- ---------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
일단 갸져올 수는 있다.
다음 단계에서는 두개로 나뉘어져 읽혀온 명칭을 합쳐주기만 하면된다.
다음처럼 DECODE를 이용해 보자.
SELECT A.UTILITY,
A.MAIN_EMP,
DECODE(C.ENAME,NULL,B.ENAME,C.ENAME) ENAME
FROM EMP C,
EMP_TEMP B,
T_MANAGER A
WHERE A.MAIN_EMP = B.EMPNO(+)
AND A.MAIN_EMP = C.EMPNO(+)
원하는 결과가 나온다.
OUTER JOIN에 DECODE가 왠지 마음에 않들면 INLINE VIEW 도 괜찮다.
다음과 같이 두 테이블을 하나로 UNION 한후 사용하는 방법이다.
SELECT A.UTILITY,
A.MAIN_EMP,
B.ENAME
FROM (SELECT * FROM EMP
UNION ALL
SELECT * FROM EMP_TEMP
) B,
T_MANAGER A
WHERE B.EMPNO = A.MAIN_EMP
뒷풀이2.
여기까지 이해가 되었으면 다음문제를 고민해보자.
해답2에서 제시된 2개의 QUERY는 항상 같은 결과를 RETURN 하는가?..
현재까지의 DATA만 보면 그렇다.
하지만 EMP 와 EMP_TEMP 모두에 존재하지 않는 사번이 있는
경우는 어떠할까?..
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
조명시설 9999
위와 같은 DATA가 추가되었다.
두개의 결과를 비교해보자.
먼저 OUTER-JOIN의 경우는 결과가 다음과 같다.
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
조명시설 9999
8 행이 선택되었습니다
IN-LINE VIEW의 경우는 결과가 다음과 같다.
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
7 행이 선택되었습니다
분명한 차이가 있다.
상황에 따라 다르게 쓰여야 한다.
이와같이 내가 원하는 결과가 과연 어느쪽이냐에 따라서
구별 되어 쓰여져야 한다.
MAIN_EMP NUMBER(4) NOT NULL,
SUB1_EMP NUMBER(4),
SUB2_EMP NUMBER(4),
CONSTRAINT MAN_PK PRIMARY KEY (UTILITY,MAIN_EMP)
);
INSERT INTO T_MANAGER VALUES ('소방시설',7366,NULL,NULL);
INSERT INTO T_MANAGER VALUES ('전기시설',7499,7521,NULL);
INSERT INTO T_MANAGER VALUES ('냉방시설',7566,7654,7844);
INSERT INTO T_MANAGER VALUES ('수도시설',7876,7900,7844);
INSERT INTO T_MANAGER VALUES ('전산장비',7782,NULL,NULL);
INSERT INTO T_MANAGER VALUES ('저장시설',7698,NULL,NULL);
INSERT INTO T_MANAGER VALUES ('수송설비',8854,NULL,NULL);
다수 NULL허용 COLUMN의 명칭을 가져올때.
조건1.
T_MANAGER TABLE 에 유틸리티 명칭을 KEY로 하여
MAIN_EMP,SUB1_EMP,SUB2_EMP 세개의 사번을 가진다.
유틸리티 정관리자,부관리자1,부관리자2 를 나타낸다.
UTILITY MAIN_EMP SUB1_EMP SUB2_EMP
-------------------- ---------- ---------- ----------
소방시설 7566
전기시설 7499 7521
수도시설 7876 7900 7844
전산장비 7782
저장시설 7698
냉방시설 7566 8915 8948
다음은 ORACLE SCOTT/TIGER 로 접속하면 흔히 볼 수 있는 EMP TABLE이다.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------- -------- --------- ----- --------- ------ ------- ----------
7369 SMITH CLERK 7902 80/12/17 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7566 JONES MANAGER 7839 81/04/02 2975 20
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7698 BLAKE MANAGER 7839 81/05/01 2850 30
7782 CLARK MANAGER 7839 81/06/09 2450 10
7788 SCOTT ANALYST 7566 82/12/09 3000 20
7839 KING PRESIDENT 81/11/17 5000 10
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30
7876 ADAMS CLERK 7788 83/01/12 1100 20
7900 JAMES CLERK 7698 81/12/03 950 30
7902 FORD ANALYST 7566 81/12/03 3000 20
7934 MILLER CLERK 7782 82/01/23 1300 10
문제1.MAIN_EMP 는 NOT NULL 이며
SUB1_EMP 와 SUB2_EMP 는 NULL을 허용한다.
즉,정관리자는 반드시 입력되고 부관리자는 있을 수도 없을 수도 있다.
각 관리자에 대한 명칭을 읽어오는데 모두 EMP 에 명칭이 존재한다.
다음과 같은 결과가 나오도록 QUERY를 구성한다.
UTILITY MAIN_EMP ENAME SUB1_EMP ENAME SUB2_EMP ENAME
---------- ---------- ------ ---------- ------- ---------- -------
수도시설 7876 ADAMS 7900 JAMES 7844 TURNER
냉방시설 7566 JONES 8915 8948
전기시설 7499 ALLEN 7521 WARD
소방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
SUB1_EMP 또는 SUB2_EMP 가 NULL이더라도 MAIN_EMP 와 그명칭은 반드시
가져와야 한다.
생각1.
OUTER JOIN의 기본적인 이해를 돕기위한 예제이다.
해법1.
요점:EMP TABLE 을 세번 ACCESS 하여 EMPNO 로 EQUAL JOIN을 각각
걸어준다.
SUB1_EMP 와 SUB2_EMP 가 서로다른 ALIAS 를 가진 EMP TABLE과 OUTER
JOIN 으로 연결됨으로 EMP 에 NULL 인 ID 가 없어도
T_MANAGER 의 DATA를 가져올 수 있다.
SELECT A.UTILITY,
A.MAIN_EMP,
B.ENAME,
A.SUB1_EMP,
C.ENAME,
A.SUB2_EMP,
D.ENAME
FROM EMP D,
EMP C,
EMP B,
T_MANAGER A
WHERE A.UTILTIY > ' '
AND A.MAIN_EMP > ' '
AND B.EMPNO = A.MAIN_EMP
AND C.EMPNO(+) = A.SUB1_EMP
AND D.EMPNO(+) = A.SUB2_EMP
PLAN :
SELECT STATEMENT Optimizer=CHOOSE
MERGE JOIN (OUTER)
SORT (JOIN)
MERGE JOIN (OUTER)
SORT (JOIN)
NESTED LOOPS
TABLE ACCESS (FULL) OF EMP
TABLE ACCESS (BY INDEX ROWID) OF T_MANAGER
INDEX (RANGE SCAN) OF MAN_PK (UNIQUE)
SORT (JOIN)
TABLE ACCESS (FULL) OF EMP
SORT (JOIN)
TABLE ACCESS (FULL) OF EMP
A TABLE에 서로다른 두개의 TABLE C,D 가 각각 OUTER-JOIN으로
연결 되어있다.
같은 EMP TABLE 이지만 ALIAS를 다르게 주어 마치 다른 TABLE
인 것처럼 이용을 하고 있다.
뒷풀이.
OUTER JOIN의 기본 사용법을 익혔으니 다음 문제를 풀어보자.
코드의 명칭을 양쪽에서 가져올때.
조건2.
T_MANAGER TABLE에 다음과 같은 DATA가 추가되었다.
UTILITY MAIN_EMP SUB1_EMP SUB2_EMP
-------------------- ---------- ---------- ----------
수송설비 8854
그리고 냉방시설의 부관리자와 수송설비의 정관리자는 모두
계약직사원이다.
따라서 계약직 사원 관리TABLE인 EMP_TEMP 에 존재며 EMP에는 없다.
정사원은 7로 시작되며 계약직 사원은 8로 시작된다.
문제2.
우선 MAIN_EMP만의 성명을 읽어올 경우를 생각해 보자.
EMP와 EMP_TEMP 중 하나에 반드시 그 사번과 성명이 존재하는 경우이다.
정직원과 계약직 직원의 구분이 없으므로 양쪽을 다 찾아야한다.
순서는 INDEX에 따라 변경 가능하므로 신경쓰지 않아도 좋다.
결과는 다음과 같다.
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
생각2.
앞서 다루었던 문제와 유사하지만 차이가 있다면 경우에 따라
명칭을 차별적으로 보여줘야 한다는 것이다.
T_MANAGER를 기준으로 EMP와 EMP_TEMP에 OUTER JOIN을 사용함으로하여,
EMP와 EMP_TEMP 두개의 TEABL에 명칭을 찾기위해 ACCESS를
시도 할 것이며,OUTER JOIN이기에 없으면 NULL을,
있으면 명칭을 가져올 것이다.
그래도 다행인것은 두군데중에 한군데는 반드시 있는 경우이다.
어찌하나?.
해법2.
일단 문제1을 참고삼아 다음 문장을 만들어 보자.
SELECT A.UTILITY,
A.MAIN_EMP,
B.ENAME,
C.ENAME
FROM EMP C,
EMP_TEMP B,
T_MANAGER A
WHERE A.MAIN_EMP = B.EMPNO(+)
AND A.MAIN_EMP = C.EMPNO(+)
UTILITY MAIN_EMP ENAME ENAME
-------------------- ---------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
일단 갸져올 수는 있다.
다음 단계에서는 두개로 나뉘어져 읽혀온 명칭을 합쳐주기만 하면된다.
다음처럼 DECODE를 이용해 보자.
SELECT A.UTILITY,
A.MAIN_EMP,
DECODE(C.ENAME,NULL,B.ENAME,C.ENAME) ENAME
FROM EMP C,
EMP_TEMP B,
T_MANAGER A
WHERE A.MAIN_EMP = B.EMPNO(+)
AND A.MAIN_EMP = C.EMPNO(+)
원하는 결과가 나온다.
OUTER JOIN에 DECODE가 왠지 마음에 않들면 INLINE VIEW 도 괜찮다.
다음과 같이 두 테이블을 하나로 UNION 한후 사용하는 방법이다.
SELECT A.UTILITY,
A.MAIN_EMP,
B.ENAME
FROM (SELECT * FROM EMP
UNION ALL
SELECT * FROM EMP_TEMP
) B,
T_MANAGER A
WHERE B.EMPNO = A.MAIN_EMP
뒷풀이2.
여기까지 이해가 되었으면 다음문제를 고민해보자.
해답2에서 제시된 2개의 QUERY는 항상 같은 결과를 RETURN 하는가?..
현재까지의 DATA만 보면 그렇다.
하지만 EMP 와 EMP_TEMP 모두에 존재하지 않는 사번이 있는
경우는 어떠할까?..
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
조명시설 9999
위와 같은 DATA가 추가되었다.
두개의 결과를 비교해보자.
먼저 OUTER-JOIN의 경우는 결과가 다음과 같다.
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
조명시설 9999
8 행이 선택되었습니다
IN-LINE VIEW의 경우는 결과가 다음과 같다.
UTILITY MAIN_EMP ENAME
-------------------- ---------- ----------
전기시설 7499 ALLEN
소방시설 7566 JONES
냉방시설 7566 JONES
저장시설 7698 BLAKE
전산장비 7782 CLARK
수도시설 7876 ADAMS
수송설비 8854 TURNER
7 행이 선택되었습니다
분명한 차이가 있다.
상황에 따라 다르게 쓰여야 한다.
이와같이 내가 원하는 결과가 과연 어느쪽이냐에 따라서
구별 되어 쓰여져야 한다.