[SQL] WITH절과 순환 서브쿼리

WITH 절

별칭으로 사용한 SELECT문의 FROM절에 다른 SELECT구문의 별칭 참조가 가능하다.

WITH 별칭1 AS (서브쿼리)
     ,별칭2 AS(서브쿼리)
...
SELECT
FROM    별칭1, 별칭2...

WITH B2 AS (SELECT  PERIOD
                    , REGION
                    , SUM(LOAN_JAN_AMT) JAN_AMT
            FROM    KOR_LOAN_STATUS
            GROUP BY PERIOD, REGION)
    , C AS (SELECT  B2.PERIOD
                    , MAX(B2.JAN_AMT) MAX_JAN_AMT
            FROM    B2
                    , ( SELECT  MAX(PERIOD) MAX_MONTH
                        FROM    KOR_LOAN_STATUS
                        GROUP BY SUBSTR(PERIOD, 1, 4) ) A
            WHERE   B2.PERIOD = A.MAX_MONTH
            GROUP BY B2.PERIOD)
SELECT  B2.*
FROM    B2, C
WHERE   B2.PERIOD = C.PERIOD
AND     B2.JAN_AMT = C.MAX_JAN_AMT
ORDER BY 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;

위 쿼리는 아래 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
         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_NAME SET ORDER_SEQ
SELECT  DEPARTMENT_ID, LPAD(' ', 3*(LVL-1)) || DEPARTMENT_NAME, LVL
FROM    RECUR;

WHERE  PARENT_ID IS NULL  은 START WITH PARENT_ID IS NULL 과 같고, WHERE  A.PARENT_ID = B.DEPARTMENT_ID 은 CONNECT BY PRIOR DEPARTMENT_ID = PARENT_ID 와 같다.

SEARCH 구문

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

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

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

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