반응형
[테이블: 부서]
| 부서코드(PK) | 부서명 | 상위부서코드(FK) |
|---|---|---|
| 100 | 아시아부 | NULL |
| 110 | 한국지사 | 100 |
| 111 | 서울지점 | 110 |
| 112 | 부산지점 | 110 |
| 120 | 일본지사 | 100 |
| 121 | 도쿄지점 | 120 |
| 122 | 오사카지점 | 120 |
| 130 | 중국지사 | 100 |
| 131 | 베이징지점 | 130 |
| 132 | 상하이지점 | 130 |
| 200 | 남유럽지부 | NULL |
| 210 | 스페인지사 | 200 |
| 211 | 마드리드지점 | 210 |
| 212 | 그라나다지점 | 210 |
| 220 | 포르투갈지사 | 200 |
| 221 | 리스본지점 | 220 |
| 222 | 포르투지점 | 220 |
[테이블: 매출]
| 부서코드 | 매출액 |
|---|---|
| 111 | 1000 |
| 112 | 2000 |
| 121 | 1500 |
| 122 | 1000 |
| 131 | 1500 |
| 132 | 2000 |
| 211 | 2000 |
| 212 | 1500 |
| 221 | 1000 |
| 222 | 2000 |
테이블 생성
CREATE TABLE booseo(
code CHAR(3),
name VARCHAR(18),
upcode CHAR(3),
CONSTRAINT boose_pk PRIMARY KEY (code),
CONSTRAINT booseo_fk FOREIGN KEY (upcode) REFERENCES booseo (code)
);
INSERT INTO booseo VALUES ('100', '아시아부', NULL);
INSERT INTO booseo VALUES ('110', '한국지사', '100');
INSERT INTO booseo VALUES ('111', '서울지점', '110');
INSERT INTO booseo VALUES ('112', '부산지점', '110');
INSERT INTO booseo VALUES ('120', '일본지사', '100');
INSERT INTO booseo VALUES ('121', '도쿄지점', '120');
INSERT INTO booseo VALUES ('122', '오사카지점', '120');
INSERT INTO booseo VALUES ('130', '중국지사', '100');
INSERT INTO booseo VALUES ('131', '베이징지점', '130');
INSERT INTO booseo VALUES ('132', '상하이지점', '130');
INSERT INTO booseo VALUES ('200', '남유럽지부', NULL);
INSERT INTO booseo VALUES ('210', '스페인지사', '200');
INSERT INTO booseo VALUES ('211', '마드리드지점', '210');
INSERT INTO booseo VALUES ('212', '그라나다지점', '210');
INSERT INTO booseo VALUES ('220', '포르투갈지사', '200');
INSERT INTO booseo VALUES ('221', '리스본지점', '220');
INSERT INTO booseo VALUES ('222', '포르투지점', '220');
CREATE TABLE maechool(
code CHAR(3),
amount NUMBER
);
INSERT INTO maechool VALUES ('111', 1000);
INSERT INTO maechool VALUES ('112', 2000);
INSERT INTO maechool VALUES ('121', 1500);
INSERT INTO maechool VALUES ('122', 1000);
INSERT INTO maechool VALUES ('131', 1500);
INSERT INTO maechool VALUES ('132', 2000);
INSERT INTO maechool VALUES ('211', 2000);
INSERT INTO maechool VALUES ('212', 1500);
INSERT INTO maechool VALUES ('222', 2000);
위와 같은 부서와 매출 테이블에 대해 아래와 같은 SQL 문장들을 실행했을 때의 결과를 예측해보자.
(1번)
SELECT A.code, A.name, A.upcode, B.amount, LVL
FROM (SELECT code, name, upcode, LEVEL AS LVL --- (1)번 SELECT 문
FROM booseo
START WITH code = '120'
CONNECT BY PRIOR upcode = code
UNION
SELECT code, name, upcode, LEVEL AS LVL --- (2)번 SELECT 문
FROM booseo
START WITH code = '120'
CONNECT BY upcode = PRIOR code) A LEFT OUTER JOIN maechool B ON (A.code = B.code)
ORDER BY A.code;
먼저, (1)번 SELECT 문은
- 부서 테이블에서, (FROM booseo)
- 부서코드가 120인 일본지사 노드를 루트 노드로 생성하고, (START WITH code = '120'),
- 자식노드로 일본지사의 상위 부서 노드를 선택한다 (상위 부서 노드는 한개밖에 없다).
그리고 그 자식 노드에 대해 이 과정을 반복한다. (CONNECT BY PRIOR upcode = code)
부서코드가 100인 아시아부는 상위부서코드가 NULL이므로 여기서 재귀적인 호출을 종료한다.
참고로, 이와 같은 PRIOR 부모 = 자식 형태를 사용하면 자식 데이터 -> 부모데이터로 전개하는 역방향 전개를 한다.
- 이제 선택된 노드들을 SELECT 문을 이용해 테이블을 만들면,
CODE (부서코드) NAME (부서명) UPCODE (상위부서코드) LEVEL 100 아시아부 NULL 2 120 일본지사 100 1
그다음, (2)번 SELECT 문은
- 부서 테이블에서, (FROM booseo)
- 부서코드가 120인 일본지사 노드를 루트 노드로 생성하고, (START WITH code = '120'),
- 자식노드로 일본지사의 자식 부서 노드들을 선택한다.
그리고 그 자식 부서 노드들에 대해 이 과정을 반복한다.(CONNECT BY upcode = PRIOR code / 아까와는 PRIOR의 위치가 반대)
(1)번 SELECT문과 달리, PRIOR 자식 = 부모 형태를 사용했고, 부모 데이터 -> 자식 데이터로 전개하는 순방향 전개를 한다.
- 이제 선택된 노드들을 SELECT 문을 이용해 테이블을 만들면,
CODE (부서코드) NAME (부서명) UPCODE (상위부서코드) LEVEL 120 일본지사 100 1 121 도쿄지점 120 2 122 오사카지점 120 2
이제, (1)과 (2)의 결과를 UNION(UNION은 중복 제거, UNION ALL이 중복 제거 없음)하면,
| CODE (부서코드) | NAME (부서명) | UPCODE (상위부서코드) | LEVEL |
|---|---|---|---|
| 100 | 아시아부 | NULL | 2 |
| 120 | 일본지사 | 100 | 1 |
| 121 | 도쿄지점 | 120 | 2 |
| 122 | 오사카지점 | 120 | 2 |
이 테이블을 매출 테이블과 LEFT OUTER JOIN하면,
(결과)
CODE NAME UPCODE AMOUNT LVL
------ ------------------------------------ ------ ---------- ----------
100 아시아부 2
120 일본지사 100 1
121 도쿄지점 120 1500 2
122 오사카지점 120 1000 2
위와 같은 결과가 나온다.
(2번)
SELECT A.code, A.name, A.upcode, B.amount, LVL
FROM (SELECT code, name, upcode, LEVEL AS LVL
FROM booseo
START WITH code = '100'
CONNECT BY upcode = PRIOR code) A LEFT OUTER JOIN maechool B
ON (A.code = B.code)
ORDER BY A.code;
원리는 (1번)과 같으므로 간단히 그림만 그려보도록 하겠다. (이번 글의 메인은 4번 문제다. 좀만 기다리도록 하자.)
부모 데이터 -> 자식 데이터로 전개하니깐 순방향 전개!
(결과)
CODE NAME UPCODE AMOUNT LVL
------ ------------------------------------ ------ ---------- ----------
100 아시아부 1
110 한국지사 100 2
111 서울지점 110 1000 3
112 부산지점 110 2000 3
120 일본지사 100 2
121 도쿄지점 120 1500 3
122 오사카지점 120 1000 3
130 중국지사 100 2
131 베이징지점 130 1500 3
132 상하이지점 130 2000 3
(3번)
SELECT A.code, A.name, A.upcode, B.amount, LVL
FROM (SELECT code, name, upcode, LEVEL AS LVL
FROM booseo
START WITH code = '121'
CONNECT BY PRIOR upcode = code) A LEFT OUTER JOIN maechool B
ON (A.code = B.code)
ORDER BY A.code;
3번 역시 1번, 2번과 똑같다. 그림만 보고 넘어가자.
자식 데이터 -> 부모 데이터로 전개하니깐 역방향 전개!
(결과)
CODE NAME UPCODE AMOUNT LVL
------ ------------------------------------ ------ ---------- ----------
100 아시아부 3
120 일본지사 100 2
121 도쿄지점 120 1500 1
(4번)
SELECT A.code, A.name, A.upcode, B.amount, LVL --- (3)번 SELECT 문
FROM (SELECT code, name, upcode, LEVEL AS LVL --- (2)번 SELECT 문
FROM booseo
START WITH code = (SELECT code --- (1)번 SELECT 문
FROM booseo
WHERE upcode IS NULL
START WITH code = '120'
CONNECT BY PRIOR upcode = code)
CONNECT BY upcode = PRIOR code) A LEFT OUTER JOIN maechool B
ON (A.code = B.code)
ORDER BY A.code;
결과가 어떻게 나올까??
먼저, 내가 처음에 생각했던 로직대로 쿼리문을 수행해보겠다.
첫째로, 가장 안쪽의 (1)번 SELECT문부터 수행해보면,
- 부서 테이블에서, (FROM booseo)
- 상위부서코드가 NULL인 것만을 남기고, (WHERE upcode IS NULL)
- 부서코드가 120인 행을 루트 노드로 잡으려고 보니깐...???? 그런 행이 없다...
SELECT code FROM booseo WHERE upcode IS NULL;
CODE
------
100
200
그래서 최종 SQL 수행 결과가 에러가 나거나, 선택된 레코드가 없을 줄 알았는데...
(결과)
CODE NAME UPCODE AMOUNT LVL
------ ------------------------------------ ------ ---------- ----------
100 아시아부 1
110 한국지사 100 2
111 서울지점 110 1000 3
112 부산지점 110 2000 3
120 일본지사 100 2
121 도쿄지점 120 1500 3
122 오사카지점 120 1000 3
130 중국지사 100 2
131 베이징지점 130 1500 3
132 상하이지점 130 2000 3
실행해보니, 잘 출력된다.
그래서 계층형 쿼리 처리하는 순서를 구글링해보니
START WITH 절·CONNECT BY 절을 처리 한 후에 WHERE 절을 처리하는 것 같다.
그럼 START WITH랑 CONNECT BY를 먼저 처리해 보자.
다시 (1)번 SELECT 문을 수행해 보면,
- 부서 테이블에서, (FROM booseo)
- 부서코드가 120인 행을 루트노드로 잡고, (START WITH code = '120')
CODE (부서코드) NAME (부서명) UPCODE (상위부서코드) LEVEL 120 일본지사 100 1
- 120의 상위부서를 자식 노드로 붙여준다. (CONNECT BY PRIOR upcode = code)
반복해서 붙이다 보면, 100은 상위부서가 없어서 100에서 끝난다.
이것을 테이블로 나타내면,
CODE (부서코드) NAME (부서명) UPCODE (상위부서코드) LEVEL 120 일본지사 100 1 100 아시아부 NULL 2 - 위 테이블에다가 WHERE 절 조건을 처리해 주면 (WHERE upcode IS NULL),
결국엔 아시아부만 남는다. 그래서 (2)번 SELECT문의 START WITH code 조건으로 ' = 100'이 들어가게 된다.
CODE (부서코드) NAME (부서명) UPCODE (상위부서코드) LEVEL 100 아시아부 NULL 2
이제 (2)번 SELECT문을 실행하려고 보니,,,
SELECT A.code, A.name, A.upcode, B.amount, LVL --- (3)번 SELECT 문
FROM (SELECT code, name, upcode, LEVEL AS LVL --- (2)번 SELECT 문
FROM booseo
START WITH code = '100'
CONNECT BY upcode = PRIOR code) A LEFT OUTER JOIN maechool B
ON (A.code = B.code)
ORDER BY A.code;
문제 (2번)과 완전히 똑같은 쿼리문이 됐다!!
따라서, 결과 역시 (2번)과 같이 나오는 것을 알 수 있다.
그래서 이번 포스팅을 정리하면,
START WITH 절·CONNECT BY 절을 처리 한 후에 WHERE 절을 처리하자!
반응형
'데이터베이스 > SQLD' 카테고리의 다른 글
| SQLD 시험 결과 (0) | 2021.04.16 |
|---|---|
| [SQLD 과목2 1장-7절] GROUP BY, HAVING 절 (0) | 2021.03.10 |
| [SQLD 과목2 1장-6절] 함수 (Function) (0) | 2021.03.10 |
| [SQLD 과목2 1장-5절] WHERE 절 (0) | 2021.03.10 |
| [SQLD 과목2 1장-4절] TCL (Transaction Control Language) (0) | 2021.03.09 |
댓글