코드 11-3은 오류가 난 코드 11-2를 CTE로 변환한 쿼리입니다. 실행해 보니 오류 없이 원하는 데이터가 조회됐습니다. 이 쿼리에서도 dept_mgr과 sal이라는 2개의 서브쿼리를 사용하는데, 특히 sal 서브쿼리의 FROM 절에서 다른 서브쿼리인 dept_mgr을 참조해 사용합니다. 그리고 메인쿼리에서 departments 테이블과 sal 서브쿼리를 조인해 데이터를 조회합니다.
이처럼 CTE를 사용하면 한 서브쿼리 안에서 또 다른 서브쿼리를 참조할 수 있습니다. 서브쿼리끼리 상호 참조할 수 있는 점이 바로 CTE의 대표적인 특징입니다.
또한, 서브쿼리를 먼저 정의하고 메인쿼리를 나중에 작성하기 때문에 여러 테이블에서 데이터를 수집해 원하는 정보를 추출할 때 매우 유용하게 사용할 수 있습니다.
코드 11-4
WITH tmp AS
(SELECT a.dept_no, a.dept_name, COUNT(*) cnt, SUM(c.salary) salary
FROM departments a, dept_emp b, salaries c
WHERE a.dept_no = b.dept_no
AND b.emp_no = c.emp_no
AND SYSDATE() BETWEEN b.from_date AND b.to_date
AND SYSDATE() BETWEEN c.from_date AND c.to_date
GROUP BY a.dept_no, a.dept_name
),
dept_avg AS
(SELECT AVG(salary) avg_sal
FROM tmp
)
SELECT dept_no, dept_name, salary, avg_sal
FROM tmp, dept_avg;
실행결과
