[SQLD : Ⅳ. SQL 활용] 3. 계층형 질의와 셀프 조인

* 계층형 질의

 - 테이블에 계층형 데이터 존재 시 데이터를 조회하기 위해 계층형 질의 사용

 - 계층형 데이터는 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

 - 엔티티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생

 - 순환관계 데이터 모델의 예로 조직, 사원, 메뉴가 있음

 - 계층형 구조에서 A읭 하위 사원은 B,C 이고 B 하위사원은 없음

 

* 오라클 계층형 질의

 - START WITH절 : 계층 구조 전개의 시작 위치를 지정하는 구문. 즉, 루트 데이터를 지정한다.(액세스)

 - CONNECT BY절 : 다음에 전개될 자식 데이터를 지정하는 구문.

                          자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 함.(조인)

 - PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다.

              PRIOR 자식 = 부모 형태를 사용하면 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개, 순방향 전개

              PRIOR 부모 = 자식 형태를 사용하면 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개, 역방향 전개

 - NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 사이클(Cycle)

                  사이클이 발생한 데이터는 런타임 오류가 발생

                  NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.

 - ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

 - WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF 
FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자;

LEVEL 사원 관리자 ISLEAF

----- -------- ----- ------

1 A 0 2

B A 1 2

C A 0 3

D C 1 3

E C 1

사원 'D'로부터 자신의 상위관리자를 찾는 역방향 전개

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF 
FROM 사원 START WITH 사원 = 'D' CONNECT BY PRIOR 관리자 = 사원;

LEVEL 사원 관리자 ISLEAF

----- --------- ----- -----

1 D C 0

2 C A 0

3 A 1

SELECT CONNECT_BY_ROOT 사원 루트사원, SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자
FROM 사원 START WITH 관리자 IS NULL CONNECT BY PRIOR 사원 = 관리자

루트사원 경로 사원 관리자

------- ------- ---- -----

A /A A

A /A/B B A

A /A/C C A

A /A/C/D D C

A /A/C/E E C

START WITH를 통해 추출된 루트 데이터가 1건 이기 때문에 루트사원은 모두 A

 

* SQL server 계층형 질의

 1. CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.

 2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.

 3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행한다.

 4. 빈 집합이 반환될 때까지 3단계를 반복한다.

 5. 결과 집합을 반환한다. 이것은 T0에서 Tn까지의 UNION ALL이다.

WITH T_EMP_ANCHOR AS 
     ( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL 
       FROM T_EMP 
       WHERE MANAGERID IS NULL 
       /* 재귀 호출의 시작점 */ 
       UNION ALL SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1 
                 FROM T_EMP_ANCHOR A, T_EMP R 
                 WHERE A.EMPLOYEEID = R.MANAGERID ) 
SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID 
FROM T_EMP_ANCHOR GO 

3 1212 1210 2 1110 1100 2 1120 1100 3 1121 1120 3 1122 1120 (14개 행 적용됨

CTE 재귀 호출로 만들어낸 계층 구조는 실제와 다른 모습으로 출력

CTE에 Sort라는 정렬용 칼럼을 추가하고 쿼리 마지막에 order by 조건을 추가

WITH T_EMP_ANCHOR AS
       ( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL, 
                CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT 
         FROM T_EMP 
         WHERE MANAGERID IS NULL 
         /* 재귀 호출의 시작점 */ 
         UNION ALL SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1,
                          CONVERT(VARCHAR(1000), A.SORT + '/' + R.EMPLOYEEID) AS SORT 
                   FROM T_EMP_ANCHOR A, T_EMP R WHERE A.EMPLOYEEID = R.MANAGERID )
SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID, SORT 
FROM T_EMP_ANCHOR 
ORDER BY SORT GO

Level EmployeeID ManagerID Sort

---- -------- -------- -------------

0 1000 NULL 1000

1 1100 1000 1000/1100

2 1110 1100 1000/1100/1110

2 1120 1100 1000/1100/1120

3 1121 1120 1000/1100/1120/1121

3 1122 1120 1000/1100/1120/1122

1 1200 1000 1000/1200

2 1210 1200 1000/1200/1210

3 1211 1210 1000/1200/1210/1211

3 1212 1210 1000/1200/1210/1212

2 1220 1200 1000/1200/1220

3 1221 1220 1000/1200/1220/1221

3 1222 1220 1000/1200/1220/1222

1 1300 1000 1000/1300 (14개 행 적용됨)

 

* 셀프 조인

 - 동일 테이블 사이의 조인. FROM 절에 동일 테이블이 두 번 이상 나타난다

 - 식별을 위해 반드시 테이블 별칭(Alias)를 사용

SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ... 
FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2 
WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

 

SELECT WORKER.ID 사원번호, WORKER.NAME 사원명, MANAGER.NAME 관리자명 
FROM EMP WORKER, EMP MANAGER 
WHERE WORKER.MGR = MANAGER.ID;

자신과 상위, 차상위 관리자를 같은 줄에 표시하라

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1, 사원 E2 
WHERE E1.관리자 = E2.사원 ORDER BY E1.사원;

사원 관리자 차상위_관리자

---- ------ ----------

B A C

A D C

A E C

A

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1 LEFT OUTER JOIN 사원 E2 ON (E1.관리자 = E2.사원) ORDER BY E1.사원;

사원 관리자 차상위_관리자

---- ----- ----------

A B A

C A D

C A E

C A