by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

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




계층형 질의와 셀프 조인

계층형 질의

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다.
계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

그림은 사원에 대한 순환관계 데이터 모델을 표현한 것이다.
(2)계층형 구조에서 A의 하위 사원은 B, C이고 B 밑에는 하위 사원이 없고 C의 하위 사원은 D, E가 있다.
계층형 구조를 데이터로 표현한 것이 (3)샘플 데이터이다.

Oracle 계층형 질의
SELECT ...
FROM   TABLE
WHERE  CONDITION AND CONDITION
START WITH CONDITION
CONNECT BY [NOCYCLE] CONDITION AND CONDITION
[ORDER SIBLINGS BY COLUMN, COLUMN......]
  • START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.(액세스)
  • CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.(조인)
  • PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 역방향 전개를 한다.
  • NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
  • ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
  • WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

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

가상 컬럼 설명
LEVEL 루트 데이터이면 1, 그 하위 데이터이면 2이다. 리프(Leaf) 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF 전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0이다.
CONNECT_BY_ISCYCLE 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용할 수 있다.

다음은 그림의 (3)샘플 데이터를 계층형 질의 구문을 이용해서 조회한 것이다. 여기서는 결과 데이터를 들여쓰기 하기 위해서 LPAD 함수를 사용하였다.

SELECT  LEVEL, LPAD(' ', 4 * (LEVEL-1)) || EMPNO 사원, 
        MGR 관리자, CONNECT_BY_ISLEAF ISLEAF 
FROM    EMP
START   WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR; 
LEVEL     EMP            MGR      ISLEAF
------    -------------- -------  ---------
1         7839                    0 
2            7566        7839     0
3               7788     7566     0 
4                  7876  7788     1 

A는 루트 데이터이기 때문에 레벨이 1이다.
A의 하위 데이터인 B, C는 레벨이 2이다.
그리고 C의 하위 데이터인 D, E는 레벨이 3이다.
리프 데이터는 B, D, E이다. 관리자 → 사원 방향을 전개이기 때문에 순방향 전개이다.
그림은 계층형 질의에 대한 논리적인 실행 모습이다.
다음 예제는 사원 'D'로부터 자신의 상위관리자를 찾는 역방향 전개의 예이다.

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || EMPNO사원, 
       MGR 관리자, CONNECT_BY_ISLEAF ISLEAF 
FROM   EMP 
START WITH EMPNO = '7876' 
CONNECT BY PRIOR MGR = EMPNO; 
LEVEL     EMP            MGR      ISLEAF
------    -------------- -------  ---------
1         7876           7788     0 
2            7788        7566     0
3               7566     7839     0 
4                  7839           1 


본 예제는 역방향 전개이기 때문에 하위 데이터에서 상위 데이터로 전개된다. 결과를 보면 내용을 제외하고 표시 형태는 순방향 전개와 동일하다. D는 루트 데이터이기 때문에 레벨이 1이다. D의 상위 데이터인 C는 레벨이 2이다. 그리고 C의 상위 데이터인 A는 레벨이 3이다. 리프 데이터는 A이다. 루트 및 레벨은 전개되는 방향에 따라 반대가 됨을 알 수 있다. [그림 Ⅱ-2-9]는 역방향 전개에 대한 계층형 질의에 대한 논리적인 실행 모습이다.

함수 설명
SYS_CONNECT_BY_PATH 루트 데이터부터 현재 전개할 데이터가지의 경로를 표시한다. 사용법 : SYS_CONNECT_BY_PATH (칼럼, 경로분리자)
CONNECT_BY_ROOT 현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다. 사용법 : CONNECT_BY_ROOT 칼럼
SELECT CONNECT_BY_ROOT(EMPNO) 루트사원, SYS_CONNECT_BY_PATH(EMPNO, '/') 경로, 
       EMPNO 사원, MGR 관리자 
FROM   EMP
START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR; 
루트사원      경로                 사원       관리자
--------    -------------------  ------     ----------
    7839    /7839                7839
    7839    /7839/7566           7566       7839
    7839    /7839/7566/7788      7788       7566
SQL Server 계층형 질의
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 EmployeeID 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)라고 한다.

아래는 재귀적 쿼리의 처리 과정이다.

  • CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.
  • 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.
  • Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행한다.
  • 빈 집합이 반환될 때까지 3단계를 반복한다.
  • 결과 집합을 반환한다. 이것은 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 
************************************************************************** 
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개 행 적용됨)
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개 행 적용됨) 
셀프 조인

셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다. 그리고 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별해줘야 한다. 이외 사항은 조인과 동일하다.
셀프 조인에 대한 기본적인 사용법은 다음과 같다

SELECT E1.EMPNO 사원, E1.MGR 관리자, E2.MGR 차상위_관리자 
FROM EMP E1, EMP E2 
WHERE E1.MGR = E2.EMPNO 
ORDER BY E2.MGR DESC, E1.MGR, E1.EMPNO; 
사원    관리자    차상위_관리자 
-----  -------  ---------------
7566   7839
7689   7839
7782   7839
7788   7566     7839     

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 3월 29, 2013

    기민용 says:

    SQL Server 계층형 질의 는 제목만 있고 내용은 없네? 내일이 발표인데...오늘 채울건가?

    SQL Server 계층형 질의 는 제목만 있고 내용은 없네?

    • 내일이 발표인데...오늘 채울건가?
    1. 3월 29, 2013

      안종식 says:

      네 오늘 마무리 하게요... ^^;;

      네 오늘 마무리 하게요... ^^;;

  2. 3월 29, 2013

    기민용 says:

    (문제) 다음 쿼리의 실행순서로 맞는 것은? ① -- SELECT d.dname, e.ename ② -- FROM emp e, dep...

    (문제) 다음 쿼리의 실행순서로 맞는 것은?

    ① -- SELECT d.dname, e.ename
    ② --   FROM emp e, dept d
    ③ --  WHERE e.deptno = d.deptno
    ④ --    AND d.deptno = 30
    ⑤ --  START WITH e.mgr IS NULL
    ⑥ --  CONNECT BY PRIOR e.empno = emgr ;
    
    • (1) ①>②>③>④>⑤>⑥
    • (2) ②>③>④>⑤>⑥>①
    • (3) ②>⑤>⑥>③>④>①
    • (4) ②>⑤>⑥>④>③>①
    • (5) ②>③>⑤>⑥>④>①
    1. 3월 29, 2013

      임상준 says:

      일단 1번은 아녀보이네요...

      일단 1번은 아녀보이네요...

    2. 3월 29, 2013

      배성환 says:

      remove 뉴룰뻔.. 4번같은데..

      remove 뉴룰뻔..
      4번같은데..

    3. 3월 29, 2013

      하봉래 says:

      저는 2번~!

      저는 2번~!

    4. 4월 04, 2013

      이재현 says:

      좀 애매한게 NL로 풀리나 해쉬로 풀랴 따라 달리질거같은데.. 아니군요 ㅋ 2 5 6 4 3 1 답은 4번 같아요..

      좀 애매한게 NL로 풀리나 해쉬로 풀랴 따라 달리질거같은데.. 아니군요 ㅋ

      2 5 6 4 3 1

      답은 4번 같아요..

    5. 4월 05, 2013

      기민용 says:

      흐미...전열을 재정비 해야긋당... 다들 전투태세가 안되어 있네... 이래가지고 전투(시험)에서 승리(합격)할 수 있긋냐... 정답은 5번 입...

      흐미...전열을 재정비 해야긋당...
      다들 전투태세가 안되어 있네...
      이래가지고 전투(시험)에서 승리(합격)할 수 있긋냐...
      정답은 5번 입니다.
      Where 절 중 조인을 먼저 한 뒤
      계층구조 전개를 하고
      그 결과로부터 Where 절 중 조인조건이 아닌 나머지 필터조건이 수행되어 걸러냅니다.
      조인 > 계층 > 필터 순서로 진행됩니다.

  3. 3월 29, 2013

    배성환 says:

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

    START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.(액세스)
    CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.(조인)
    PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 역방향 전개를 한다.
    NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
    ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
    WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

    요고 해놓고
    설명으로 올바른건?
    형태로 나오기 딱조아보임..