3. 계층형 질의와 셀프 조인

1. 계층형 질의

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query) 사용.

• 계층형 데이터란?

• 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터
• 예를들면, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재.
• 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생.
• 순환관계 데이터 모델의 예) 조직, 사원, 메뉴 등

    ▼ ［그림 Ⅱ-2-6］: 사원에 대한 순환관계 데이터 모델 표현

(2) 계층형 구조 : A의 하위 사원은 B, C이고 B 밑에는 하위 사원이 없고 C의 하위 사원은 D, E 가 있음

(3) 샘플 데이터 : (2) 계층형 구조를 데이터로 표현한 것

※ 계층형 데이터 조회는 DBMS 벤더와 버전에 따라 다른 방법으로 지원.

가. Oracle 계층형 질의

• Oracle은 계층형 질의를 지원하기 위해서 ［그림 Ⅱ-2-7］과 같은 계층형 질의 구문을 제공

  - 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))||EMPNO||':'||ENAME 사원,MGR 관리자,CONNECT_BY_ISLEAF IsLF

FROM   SCOTT.EMP -- WHERE ENAME <> 'FORD' 

START WITH EMPNO = '7369'  CONNECT BY PRIOR MGR = EMPNO

• Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column) 제공

    ▼ 예제 1 -［그림 Ⅱ-2-6］의 (3)샘플 데이터를 계층형 질의 구문을 이용해서 조회

SQL	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

          결과 데이터를 들여쓰기 하기 위해서 LPAD 함수 사용

  관리자 → 사원 방향을 전개이기 때문에 순방향 전개

  ▼［그림 Ⅱ-2-8］은 계층형 질의에 대한 논리적인 실행 모습 

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

SQL	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

          역방향 전개이기 때문에 하위 데이터에서 상위 데이터로 전개

  리프 데이터는 A이다. 루트 및 레벨은 전개되는 방향에 따라 반대가 됨

  ▼［그림 Ⅱ-2-8］은 계층형 질의에 대한 논리적인 실행 모습 

• Orcle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 ［표 Ⅱ-2-3］과 같은 함수 제공

  ▼ 예제 3 -  SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT를 사용한 예

SQL	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

          루트사원은 모두 A

  ∵ START WITH를 통해 추출된 루트 데이터가 1건 이기 때문

  경로는 루트로부터 현재 데이터까지의 경로 표시

  예를 들어, D의 경로는 A → C → D

    ※ 참고 

     CONNECT_BY_PATH        CONNECT_BY_ROOT        CONNECT_BY_ISLEAF  

     ☞ Oracle 10g 부터 사용 가능 

         (회사가 8i, 9i버전이라 예제 테스트 시 수행되지 않았음... -_-;;;  8i 는 예상했지만..9i까지 안될줄이야...)

나. SQL Server 계층형 질의

• SQL Server 2000 버전까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았음

• 조직도처럼 계층적 구조를 가진 데이터는 
• 저장 프로시저를 재귀 호출하거나 
• While 루프 문에서 임시 테이블을 사용하는 등 (순수한 쿼리가 아닌) 프로그램 방식으로 전개

• SQL Server 2005 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 됨

    ■ 예제. Northwind 데이터베이스에 접속하여 Employees 테이블의 데이터 조회

USE NORTHWIND GO 

SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES GO 

********************************************************************************** 

Emp~ID  LastName FirstName   ReportsTo 

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

1 Davolio Nancy 2 

2 Fulle Andrew NULL 

3 Leverling Janet 2 

4 Peacock Margaret 2 

5 Buchanan Steven 2 

6 Suyama Michael 5 

7 King Robert 5 

8 Callahan Laura 2 

9 Dodsworth Anne 5 

(9개 행 적용됨)

ReportsTo 칼럼 : 상위 사원에 해당하며 EmployeeID 칼럼과 재귀적 관계를 맺고 있음

EmployeeID가 2인 Fuller 사원을 살펴보면, ReportsTo 칼럼 값이 NULL이므로 계층 구조의 최상위에 있음을 알 수 있음

CTE(Common Table Expression)를 재귀 호출함으로써 

Employees 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 결과는 다음과 같음

WITH EMPLOYEES_ANCHOR AS 

( SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL 

 FROM   EMPLOYEES 

 WHERE  REPORTSTO IS NULL /* 재귀 호출의 시작점 */ 

 UNION ALL 

 SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1 

 FROM   EMPLOYEES_ANCHOR A, EMPLOYEES R 

 WHERE  A.EMPLOYEEID = R.REPORTSTO ) 

SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES_ANCHOR GO 

********************************************************************************** 

Level  Emp~ID   LastName       FirstName   ReportsTo 

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

0 2 Fuller Andrew    NULL 

1 1 Davolio Nancy    2 

1 3 Leverling Janet    2 

1 4 Peacock Margaret   2 

1 5 Buchanan Steven    2 

1 8 Callahan Laura    2 

2 6 Suyama Michael    5 

2 7 King Robert    5 

2 9 Dodsworth Anne    5 

(9개 행 적용됨)

WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 쿼리 두 개를 결합 

둘 중 위에 있는 쿼리를 ‘앵커 멤버’(Anchor Member)라고 하고, 

아래에 있는 쿼리를 ‘재귀 멤버’(Recursive Member)라고 함

    ▼ 재귀적 쿼리의 처리 과정 

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

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

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

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

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

   ※ 정리

1. 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인 시작

2. 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면 

   (즉, 더 조인할 수 없으면) 

   지금까지 만들어진 결과 집합을 모두 합하여 리턴

• ▼［그림 Ⅱ-2-10］에 있는 조직도를 쿼리로 출력했을 때, 대부분 사용자는 아래와 같은 결과를 예상

 EmployeeID     ManagerID   (보기 편하도록 각 로우 앞쪽에 자신의 레벨만큼 빈칸 삽입)
-----------------   --------- 

1000 NULL 

    1100 1000 
        1110 1100 
        1120 1100 
            1121 1120 
            1122 1120 
    1200 1000 
        1210 1200 
            1211 1210 
            1212 1210 
        1220 1200 
            1221 1220 
            1222 1220 

    1300 1000

   ▼ 아래에 t_emp 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 그 결과이다.

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

*********************************************************************************************** 

Level EmployeeID    ManagerID 

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

0 1000 NULL 

1 1100 1000 

1 1200 1000 

1 1300 1000 

2 1210 1200 

2 1220 1200 

3 1221 1220 

3 1222 1220 

3 1211 1210

3 1212 1210 

2 1110 1100 

2 1120 1100 

3 1121 1120 

3 1122 1120 

(14개 행 적용됨)

보다시피, 계층 구조를 단순히 하위 방향으로 전개했을 뿐 ［그림 Ⅱ-2-10］에 있는 조직도와는 많이 다른 모습 

앞서 보았듯이, CTE 재귀 호출로 만들어낸 계층 구조는 실제와 다른 모습으로 출력

     ☞ 따라서 조직도와 같은 모습으로 출력하려면 order by 절을 추가해 원하는 순서대로 결과 정렬

   ▼ 실제 조직도와 같은 모습의 결과를 출력하도록, CTE에 Sort라는 정렬용 칼럼을 추가하고 쿼리 마지막에 order by 조건 추가

         (단, 앵커 멤버와 재귀 멤버 양쪽에서 convert 함수 등으로 데이터 형식을 일치시켜야 한다.)

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

CTE 안에서 Sort 칼럼에 사번(=EmployeeID)을 재귀적으로 더해 나가면 정렬 기준으로 삼을 수 있는 값 생성

     ▼ 아래는 Sort 칼럼으로 정렬하여 출력한 결과로, ［그림 Ⅱ-2-10］에 있는 조직도의 모습과 일치

Level Emp~ID   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개 행 적용됨)

※ 정리

가상의 Sort 칼럼을 추가해 정렬하는 게 아쉽기는 하지만, 

SQL Server에서 계층 구조를 실제 모습대로 출력하려면 현재(2005, 2008 버전 기준)로서는 감수해야 함

2. 셀프 조인

• 셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 

• 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias) 사용
• 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별 
• 이외 사항은 조인과 동일

    ■ 셀프 조인에 대한 기본적인 사용법

SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ... 

FROM   테이블1 ALIAS명1, 테이블2 ALIAS명2    =>    FROM   테이블1 ALIAS명1, 테이블1 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

   계층형 질의에서 살펴보았던 사원이라는 테이블 속에는 사원과 관리자가 모두 하나의 사원이라는 개념으로 동일시하여 같이 입력

문제 : “ 자신과 상위, 차상위 관리자를 같은 줄에 표시하라. ” 

    ☞ 이 문제를 해결하기 위해서는 FROM 절에 사원 테이블을 두 번 사용 (셀프조인 사용)

    ▼ 문제 해결

셀프 조인은 동일한 테이블(사원)이지만 

［그림 Ⅱ-2-11］과 같이 개념적으로는 두 개의 서로 다른 테이블(사원, 관리자)을 사용하는 것과 동일

• 동일 테이블을 다른 테이블인 것처럼 처리하기 위해 테이블 별칭 사용 

           ☞ 여기서는 E1(사원), E2(관리자) 테이블 별칭사용

• 차상위 관리자를 구하기 위해서 E1.관리자 = E2.사원 조인 조건 사용

    

    ▼ 셀프 조인을 이용한 SQL문

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 

FROM   사원 E1, 사원 E2 

WHERE  E1.관리자 = E2.사원 

ORDER  BY E1.사원

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

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

B A 

C  A 

D C A 

E C A

• 자신과 자신의 직속 관리자는 동일한 행에서 데이터를 구할 수 있으나 차상위 관리자는 바로 구할 수 없음 
• 차상위 관리자를 구하기 위해서는 자신의 직속 관리자를 기준으로 사원 테이블과 한번 더 조인(셀프 조인) 수행 필요
• 결과 표시를 위해 SELECT절에 2개의 ‘관리자’ 칼럼 사용
• 한 명은 자신의 직속 관리자(E1.관리자)이고 다른 한 명은 자신의 차상위 관리자(E2.관리자)
• 결과를 보면, B와 C의 관리자는 A이고 차상위 관리자는 없음 
• D와 E의 관리자는 C이고 차상위 관리자는 A
• 결과에서 A에 대한 정보는 누락
• 내부 조인(Inner Join)을 사용할 경우 자신의 관리자가 존재하지 않는 경우, 
• 관리자(E2) 테이블에서 조인할 대상이 존재하지 않기 때문에 해당 데이터는 결과에서 누락

 ☞ 이를 방지하기 위해서는 아우터 조인 사용

    ▼ 아우터 조인을 추가한 SQL문

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

    ☞ 아우터 조인을 사용해서 관리자가 존재하지 않는 데이터까지 모두 결과 표시