Oracle WITH Clause example

오라클 SQL에서 WITH 절을 사용하는 예

SQL WITH 절

SQL의 WITH 절은 SQL-99의 표준이며 오라클 9.2부터 지원되고 있다

WITH 절은 인라인 뷰(Inline View)로 처리되므로 WITH 절을 사용하지 않은 인라인 뷰와 비교하여 이해하는 것이 쉬울 것이다

개념을 명확히 하기 위해서 상관관계 서브커리(Correlated Subquery)와도 상호 비교해 가면서 차이점을 확인하는 것이 좋을 것이다

WITH절을 사용하지 않은 인라인

FROM 절에 적용된 서브커리를 Inline-View 라고 하며 동적으로 메모리에 생성된 가상의 테이블처럼 이해하면 된다

인라인 뷰로 정의된 데이터는 바깥 커리에서 사용할 수 있으며 아래의 문장에서는 t1이라는 이름으로 인라인 뷰가 정의되어 있고 바깥 커리에서 t1을 사용하고 있는 경우이다

SELECT e.ename, e.deptno, t1.sal_avg
FROM   emp e,
  (
    SELECT deptno, avg(sal) sal_avg
    FROM   emp
    GROUP BY deptno
  )t1
WHERE  e.deptno = t1.deptno
ORDER BY e.deptno;

위의 문장을 WITH 절을 사용한 인라인 뷰로 변경하여 동일한 작업을 하는 경우는 다음과 같다

WITH dept_sal_avg AS
(
  SELECT deptno, avg(sal) sal_avg
  FROM emp
  GROUP BY deptno
)
SELECT e.ename, e.deptno, t1.sal_avg
FROM emp e, dept_sal_avg t1
WHERE e.deptno=t1.deptno
ORDER BY e.deptno;

2개 이상의 인라인 뷰도 WITH 절을 사용하여 선언하고 사용할 수 있다

WITH dept_sal_avg AS
(
  SELECT deptno, avg(sal) sal_avg
  FROM emp
  GROUP BY deptno
),
same_job_cnt AS
(
  SELECT job, count(*) job_cnt
  FROM emp
  GROUP BY job
)
SELECT e.ename, e.deptno, t1.sal_avg, t2.job_cnt
FROM emp e, dept_sal_avg t1, same_job_cnt t2
WHERE e.deptno=t1.deptno AND e.job=t2.job
ORDER BY e.deptno;