메뉴 건너뛰기

bysql.net

제3절_계층형_질의와_셀프_조인

2011.10.12 00:19

실천하자 조회 수:11490

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                         

               B            A        

     2           C            A        0 

                          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        

     2           C            A        0 

                                   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 Fulle Andrew NULL 

3 Leverling Janet

4 Peacock Margaret

5 Buchanan Steven

6 Suyama Michael

7 King Robert

8 Callahan Laura

9 Dodsworth Anne

(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)이란 동일 테이블 사이의 조인을 말한다. 
∴ FROM 절에 동일 테이블이 두 번 이상 나타난다. 
  • 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(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

D C

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.사원


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

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

B

C

D C

E C A


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



※ 자료출처 : DBguide.Net

http://www.dbguide.net/db.db?cmd=view&boardUid=148202&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1


  • SQL 개발자 자격증 (bysql.net 2011년 2차 스터디)
  • 작성자: 위충환 (실천하자)
  • 최초작성일: 2011년 10월 11 일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^

번호 제목 글쓴이 날짜 조회 수
47 5 연습문제 RYUM 2014.08.07 9540
46 5 연습문제 RYUM 2014.08.07 7518
45 5_장_요약 balto 2011.11.16 6983
44 SQL 문제 [2] file balto 2011.11.15 26827
43 제3절_조인_수행_원리 노랑배 2011.11.10 6389
42 제1절_옵티마이저와_실행계획 실천하자 2011.11.08 5282
41 제1절 옵티마이저와 실행계획 실천하자 2011.11.07 6002
40 4_연습문제 monsterRachel 2011.11.03 10838
39 4_장_요약 suspace 2011.11.03 6433
38 제6절_윈도우_함수(WINDOW_FUNCTION) suspace 2011.10.27 20538
37 제8절_절차형_SQL monsterRachel 2011.10.27 8688
36 제7절_DCL(DATA_CONTROL_LANGUAGE) file DB지기 2011.10.26 8527
35 제2절_인덱스_기본4 balto 2011.10.24 7709
34 .. balto 2011.10.14 4843
33 제1절_표준_조인(STANDARD_JOIN) 노랑배 2011.10.13 15666
32 3_연습문제 DB지기 2011.10.13 7087
31 3_장_요약 file DB지기 2011.10.13 6077
» 제3절_계층형_질의와_셀프_조인 [1] 실천하자 2011.10.12 11490
29 제2절_집합_연산자(SET_OPERATOR) [1] 실천하자 2011.10.11 13872
28 제5절_그룹_함수(GROUP_FUNCTION) file balto 2011.10.08 11430