더북(TheBook)

계층형 쿼리

계층형 쿼리의 구문은 다음과 같다.

    SELECT expr1, expr2, ...
      FROM 테이블
     WHERE 조건
     START WITH[최상위 조건]
    CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건];

START WITH 조건: 계층형 구조에서 최상위 계층의 로우를 식별하는 조건을 명시한다. START WITH가 시작한다는 의미이므로, 이 조건에 맞는 로우부터 시작해 계층형 구조를 풀어 나간다.

CONNECT BY 조건: 계층형 구조가 어떤 식으로 연결되는지를 기술하는 부분이다. 부서 테이블은 parent_id에 상위 부서 정보를 갖고 있는데, 이를 표현하려면 ‘CONNECT BY PRIOR department_id = parent_id’로 기술해야 한다. PRIOR는 계층형 쿼리에서만 사용할 수 있는 연산자로 ‘앞서의, 직전의’란 뜻이 있으므로, “이전 department_id = parent_id”라고 알아두면 이해하기 쉬울 것이다. 또한 ‘CONNECT BY parent_id = PRIOR department_id’처럼 PRIOR의 위치를 바꿀 수 있다.

[그림 7-1]의 결과처럼 나오는 계층형 쿼리를 작성해 보면 다음과 같다.

입력

    SELECT department_id, LPAD(' ' , 3 * (LEVEL-1)) || department_name, LEVEL
      FROM departments
     START WITH parent_id IS NULL
      CONNECT BY PRIOR department_id  = parent_id;
그림 7-3 계층형 부서 정보와 레벨

부서 테이블에서 가장 상위 부서는 parent_id 값이 NULL이므로 이를 START WITH 절에 명시했고, CONNECT BY 절에 계층 구조 조건을 넣었다. 세 번째 컬럼으로는 LEVEL을 명시했는데, 이는 계층형 쿼리에서만 사용할 수 있는 의사 컬럼으로 계층형 구조에 따른 레벨 값을 자동으로 반환한다. 또한 각 부서별 계층 구조가 한 눈에 들어오도록 LPAD 함수를 사용해서 LEVEL 값에 따라 공백 한 문자를 왼쪽에 붙여 레벨에 따른 부서 명칭을 들여 쓰기한 효과를 주었다.

계층형 쿼리에서도 다양한 조건을 줄 수 있고 조인도 가능하다. 사원 테이블에 있는 manager_id는 해당 사원의 매니저 사번이 들어 있다. 이를 이용해 사원별 계층 구조를 표현하고, 더불어 부서 테이블과 조인을 해서 부서명까지 조회해 보자.

입력

    SELECT a.employee_id, LPAD(' ' , 3 * (LEVEL-1)) || a.emp_name,
           LEVEL,
           b.department_name
      FROM employees a,
           departments b
     WHERE a.department_id = b.department_id
      START WITH a.manager_id IS NULL
     CONNECT BY PRIOR a.employee_id = a.manager_id;
그림 7-4 조인 조건을 포함한 계층형 쿼리

사원 테이블에 있는 매니저와 사원 간의 계층 구조가 [그림 7-4]에 펼쳐져 있다. 그룹 쿼리에서 일반 조건은 WHERE 절에, 그룹핑한 결과에 대한 필터는 HAVING 절에 기술했듯이 계층형 쿼리에서도 일반 조건은 WHERE 절에, 계층형 쿼리에 대한 필터 조건은 CONNECT BY 절에 기술한다. 또 다른 두 개의 문장을 살펴 보자.

입력

    SELECT a.employee_id, LPAD(' ' , 3 * (LEVEL-1)) || a.emp_name,
           LEVEL,
           b.department_name, a.DEPARTMENT_ID
      FROM employees a,
           departments b
     WHERE a.department_id = b.department_id
       AND a.department_id = 30
     START WITH a.manager_id IS NULL
     CONNECT BY NOCYCLE PRIOR a.employee_id = a.manager_id;
그림 7-5 WHERE 조건

입력

    SELECT a.employee_id, LPAD(' ' , 3 * (LEVEL-1)) || a.emp_name,
           LEVEL,
           b.department_name, a.DEPARTMENT_ID
      FROM employees a,
           departments b
     WHERE a.department_id = b.department_id
     START WITH a.manager_id IS NULL
     CONNECT BY NOCYCLE PRIOR a.employee_id = a.manager_id
         AND a.department_id = 30;
그림 7-6 CONNECT BY 조건

두 쿼리 모두 부서 번호가 30인 건을 조회하고 있는데 첫 번째는 WHERE 절에, 두 번째는 CONNECT BY 절에 조건을 주었다. 첫 번째 쿼리와는 다르게 두 번째 쿼리에서는 부서번호가 90인 최상위 부서도 조회된 것을 알 수 있는데, 이 결과를 바탕으로 계층형 쿼리의 내부적 처리 절차를 정리해 보면 다음과 같다.

❶ 조인이 있으면 조인을 먼저 처리한다.

❷ START WITH 절을 참조해 최상위 계층 로우를 선택한다.

❸ CONNECT BY 절에 명시된 구문에 따라 계층형 관계(부모-자식 관계)를 파악해 자식 로우를 차례로 선택한다. 최상위 로우를 기준으로 자식 로우를 선택하고, 이 자식 로우에 대한 또 다른 자식 로우가 있으면 선택하는 식으로 계속 조건에 맞는 로우를 찾는다.

❹ 자식 로우 찾기가 끝나면 조인을 제외한 WHERE 조건에 해당하는 로우를 걸러내는데, 로우별로 조건에 맞지 않는 건을 걸러낸다.

CONNECT BY 절은 계층 간 구조, 즉 부모-자식 관계를 식별해서 자식 로우를 찾아내는 역할을 하므로 두 번째 쿼리에서는 최상위 로우까지 조회됐지만, 첫 번째 쿼리처럼 WHERE 절에 조건을 명시하면 부모-자식 관계를 모두 풀어 헤친 다음 모든 로우에 대해 개별적으로 조건 검색을 해서 부서번호가 30인 건만 걸러내므로 최상위 로우가 제외된 것이다.

신간 소식 구독하기
뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.