WITH 절

WITH 절 이해하기 좋은 예제

SELECT e.name AS employee_name,

          dc1.dept_count AS emp_dept_count,

          m.name AS manager_name,

          dc2.dept_count AS mgr_dept_count

FROM   employee e,

          (SELECT dept_id, COUNT(*) AS dept_count

           FROM   employee

           GROUP BY dept_id) dc1,

          employee m,

          (SELECT dept_id, COUNT(*) AS dept_count

           FROM   employee

           GROUP BY dept_id) dc2   

WHERE  e.dept_id =  dc1.dept_id

AND      e.manager_id = m.id

AND      m.dept_id = dc2.dept_id

위 쿼리를 아래 쿼리처럼 사용 할 수 있음.

또한 쿼리 수행도 1회 줄어 듬.

WITH dept_count as (

           SELECT dept_id, COUNT(*) AS dept_count

           FROM   employee

           GROUP BY dept_id)

SELECT e.name AS employee_name,

          dc1.dept_count AS emp_dept_count,

          m.name AS manager_name,

          dc2.dept_count AS mgr_dept_count

FROM   employee e,

          dept_count dc1,

          employee m,

          dept_count dc2   

WHERE  e.dept_id =  dc1.dept_id

AND      e.manager_id = m.id

AND      m.dept_id = dc2.dept_id

또한 A 모집단, B 모집단을 구한 다음에 쿼리할 때도 사용.

WITH dept as

               (select id, name from department),

      emp as 

               (select e.id, e.name, e.dept_id, e.manager_id, e.job_id, d.name dname

                from   employee e, dept d

                where e.dept_id = d.id)

SELECT e.id, e.name, e.manager_id, e.job_id, j.title, j.subtitle

FROM   emp e, job j

WHERE  e.job_id = j.id;