[SQL] WITH문

  Query을 작성하다보면 SubQuery를 사용하는 경우가 많다. 한두개만 사용하면 괜찮지만 그 수가 많아질 수록 가독성이 떨어지며, 재사용할 수가 없어서 매번 같은 Query를 작성해줘야 한다. 그래서, 이번 포스트에서는 이 문제점을 해소하는데 도움이 되는 WITH문에 대해서 알아보도록 하겠다.

 

 

1. WITH문이란?

   1-1. 이름을 가진 SubQuery를 정의한 후 사용하는 구문.

 

   1-2. Query의 전체적인 가독성을 높이고, 재사용할 수 있는 장점이 있음.

 

   1-3. 대부분의 DBMS에서 지원함.

 

   1-4. 계층형쿼리를 구현할 수 있음. 

         - WITH순환절.

         - 오라클은 11g 버전부터 계층형쿼리를 위한 CONNECT BY절을 지원함.   

 

   1-5. 오라클에서는 한번만 사용되면 Inline View, 두번이상 사용되면 Materialize View로 처리함.

         - 한번도 사용하지 않으면 [ORA-01762] 오류가 발생함.

         - /*+ Materialize */ 힌트로 Inline View를 Materialize View로 만들 수 있음.

 

   1-6. 모든 DML에서 사용할 수 있음.

 

 

2. 기본 구조.

WITH [ 별명1 ] [ (컬럼명1 [,컬럼명2]) ] AS (
    SUB QUERY
)[, 별명2 AS ... ]
MAIN QUERY

   - WITH [ 별명 ] AS ( SUB QUERY )

   - 컬럼명은 생략할 수 있음.

   - 쉼표(,)로 구분하여 여러개를 정의할 수 있음.

   - 먼저 생성된 SubQuery는 나중에 생성하는 SubQuery에서 사용할 수 있음.

     예) [ 별명2 ]에서 [ 별명1 ]을 사용할 수 있음.

 

 

3. 간단한 예제.

   3-1. DEPT테이블에서 부서번호와 근무지를 출력하시오.

/** SUB QUERY를 이용한 방법 **/
SELECT T1.*
  FROM (
       SELECT A.DEPTNO
            , A.LOC
         FROM SCOTT.DEPT A
       ) T1
 WHERE 1=1
;
 
/** WITH문을 이용한 방법 **/
WITH DEPT_LOC ( DEPTNO, LOC ) AS (
    SELECT A.DEPTNO
         , A.LOC
      FROM SCOTT.DEPT A
)
SELECT T1.*
  FROM DEPT_LOC
 WHERE 1=1
;

         - 12~16 ln: WITH문으로 DEPT_LOC를 생성.

 

   3-2. EMP테이블에서 사원번호, 사원명, 부서명, 부서의 인원이 몇명인지 출력하시오.

/** SUB QUERY를 이용한 방법 **/
SELECT T1.EMPNO
     , T1.ENAME
     , T2.DNAME
     , T2.DCNT
  FROM SCOTT.EMP T1
  LEFT OUTER JOIN (
          SELECT A.DEPTNO
             , MAX(A.DNAME) AS DNAME
             , COUNT(B.EMPNO) AS DCNT
          FROM SCOTT.DEPT A
          LEFT OUTER JOIN SCOTT.EMP B
            ON B.DEPTNO = A.DEPTNO
         WHERE 1=1
         GROUP BY A.DEPTNO
  ) T2
    ON T2.DEPTNO = T1.DEPTNO
 WHERE 1=1
;
 
/** WITH문을 이용한 방법 **/
WITH DEPT_CNT AS (
    SELECT A.DEPTNO
         , MAX(A.DNAME) AS DNAME
         , COUNT(B.EMPNO) AS DCNT
      FROM SCOTT.DEPT A
      LEFT OUTER JOIN SCOTT.EMP B
        ON B.DEPTNO = A.DEPTNO
     WHERE 1=1
     GROUP BY A.DEPTNO
)
SELECT T1.EMPNO
     , T1.ENAME
     , T2.DNAME
     , T2.DCNT
  FROM SCOTT.EMP T1
  LEFT OUTER JOIN DEPT_CNT T2
    ON T2.DEPTNO = T1.DEPTNO
 WHERE 1=1
;

         - 22~31 ln: WITH문으로 DEPT_CNT를 생성.

 

 

5. 마치며

   - WITH문은 가독성이나 재사용성 때문에 많이 사용되는 것도 있지만, 계층형 쿼리를 만들기 위해서도 반드시 필요하다. 오라클 11g부터는 CONNECT BY절로 계층형 쿼리를 구현할 수 있지만, 그 이전 버전이나 다른 DBMS에서는 WITH문으로 구현할 수 있으니 꼭 숙지해두길 바란다.