[Oracle] With문에 대해

Query를 작성하다보면 SubQuery를 사용하는 경우가 많은데, 많은 SubQuery를 사용할 경우 가독성이 떨어지며, 재사용할 수 없어 매번 같은 Query를 작성해야 한다. 따라서, 이 경우 도움이 되는 With문을 사용하면 편리하다.

✏️ With문이란?

• 이름을 가진 SubQuery를 정의한 후 사용하는 구문
• Query의 전체적인 가독성을 높이고, 재사용할 수 있는 장점이 있음
• 대부분의 DBMS에서 지원함
• 계층형쿼리를 구현할 수 있음
  - With 순환절
  - 오라클은 11g 버전부터 계층형쿼리를 위한 CONNECT BY 절을 지원함
• 오라클에서는 한번 사용시 Inline View, 두번이상 사용시 Materialize View로 처리
  - 한번도 사용하지 않으면 오류 발생
  - Inline View : SubQuery가 FROM 절 안에서 사용되는 경우의 해당 서브쿼리
• 모든 DML에서 사용할 수 있음

기본 구조

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

• WITH [ 별명 ] AS ( SUB QUERY )
• 컬럼명 생략 가능
• 쉼표(,)로 구분하여 여러개 정의 가능
• 먼저 생성된 SubQuery는 나중에 생성하는 SubQuery에서 사용할 수 있음
  ex) [ 별명2 ] 에서 [ 별명1 ] 사용 가능

📖 예제

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
WHERE 1=1

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
;