순환 서브 쿼리

WITH 절은 표준 SQL에 포함된 내용으로 다른 종류의 DBMS에서도 사용할 수 있으며 일명 순환 서브 쿼리(Recursive Subquery) 기능을 지원하고 있는데, 기존 오라클에서는 이 기능을 쓰지 못했다. 하지만 오라클 11gR2 버전부터는 WITH 절로 순환 쿼리 사용이 가능하다. 순환 서브 쿼리란 계층형 쿼리와 개념이 같다. 먼저 계층형 쿼리를 살펴 보자.

입력

    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;

WITH 절을 사용해 위 계층형 쿼리와 동일한 결과를 뽑아낼 수 있다.

입력

    WITH recur ( department_id, parent_id, department_name, lvl)
            AS ( SELECT department_id, parent_id, department_name, 1 AS lvl
                   FROM departments
                  WHERE parent_id IS NULL→START WITH parent_id IS NULL과 같음 
                  UNION ALL
                 SELECT a.department_id, a.parent_id, a.department_name, b.lvl + 1
                   FROM departments a, recur b
                  WHERE a.parent_id = b.department_id
                           →CONNECT BY PRIOR department_id = parent_id와 같음
                  )
    SELECT department_id, LPAD(' ' , 3 * (lvl-1)) || department_name, lvl
     FROM recur;

레벨별로 조회되긴 했는데 출력 결과의 순서가 좀 이상하다. 계층형 쿼리에서는 자동으로 레벨에 따라 계층별로 조회되었지만, 순환 서브 쿼리에서는 단순히 레벨 순으로만 조회되었다. 뭔가 조치를 취해야 하는데 어떻게 해야 할까? 단순히 ORDER BY 절을 명시해서 해결될 문제는 아니고, 계층형 쿼리에서 사용했던 ORDER SIBLINGS BY 절과 같은 기능이 필요하다. 순환 서브 쿼리에서는 이 대신 SEARCH 구문을 사용한다. SERACH 구문의 용법은 다음과 같다.

• DEPTH FIRST BY: 같은 노드에 있는 로우, 즉 형제(sibling)로우 보다 자식 로우가 먼저 조회된다.

• BREADTH FIRST BY: 자식 로우보다 형제 로우가 먼저 조회된다.

• 같은 레벨에 있는 형제 로우일 때는 BY 다음에 명시한 컬럼 순으로 조회된다.

• SET 다음에는 가상 컬럼 형태로 최종 SELECT 절에서 사용할 수 있다.

위 내용에 맞게 쿼리를 다시 작성해 보자.

입력

    WITH recur ( department_id, parent_id, department_name, lvl)
            AS ( SELECT department_id, parent_id, department_name, 1 AS lvl
                   FROM departments
                  WHERE parent_id IS NULL
                  UNION ALL
                 SELECT a.department_id, a.parent_id, a.department_name, b.lvl + 1
                   FROM departments a, recur b
                  WHERE a.parent_id = b.department_id
                  )
    SEARCH DEPTH FIRST BY department_id SET order_seq
    SELECT department_id, LPAD(' ' , 3 * (lvl-1)) || department_name, lvl, order_seq
     FROM recur;

원하던 대로 결과가 조회되었다. 만약 SEARCH DEPTH FIRST BY 대신 SEARCH BREADTH FIRST BY를 사용하면 형제 로우, 즉 레벨 순서대로 조회된다. 또한 BY 다음에 department_name을 명시하면 같은 형제 로우끼리는 부서 명칭별로 순서가 바뀌어 조회된다. 컬럼을 바꿔가며 위 쿼리를 실행해서 그 차이점을 확실히 이해하자.