간단한 쿼리를 만들어 보면서 CTE의 작동 방법을 알아보죠.
코드 11-1
USE mywork;
WITH mng AS
(SELECT b.dept_no, b.emp_no, c.first_name, c.last_name
FROM dept_manager b, employees c
WHERE b.emp_no = c.emp_no
AND SYSDATE() BETWEEN b.from_date AND b.to_date
)
SELECT a.dept_no, a.dept_name, b.emp_no, b.first_name, b.last_name
FROM departments a, mng b
WHERE a.dept_no = b.dept_no
ORDER BY 1;
실행결과
