09 WITH

SELECT *
FROM (SELECT department_id, count(*) cnt
        FROM hr.employees
        GROUP BY department_id) d, (SELECT max(cnt) FROM d) m;
-- InlineView에서 생성한 가상 테이블을 다시 호출하면 오류 발생한다.
↓

■ WITH 문(9i)
- 두번이상 반복되는 SELECT문을 QUERY BLOCK(가상테이블)을 만들어서 사용한다.
- 성능을 향상시킬 수 있다.

WITH
    가상테이블1(QUERY BLOCK) AS (SUBQUERY),
    가상테이블2(QUERY BLOCK) AS (SUBQUERY),
    가상테이블3(QUERY BLOCK) AS (SELECT * FROM 가상테이블1),
    ...,
    가상테이블N(QUERY BLOCK) AS (SUBQUERY) -- 콤마없이 마감
    
    SELECT *
    FROM 가상테이블N, ...
    ...;

 

WITH
dept_cnt AS (SELECT department_id, count(*) cnt
                FROM hr.employees
                GROUP BY department_id),
dept_cnt_max AS (SELECT max(cnt) max_cnt FROM dept_cnt)

SELECT *
FROM dept_cnt_max;

 

WITH
dept_cnt AS (SELECT department_id, count(*) cnt
                FROM hr.employees
                GROUP BY department_id),
dept_cnt_max AS (SELECT max(cnt) max_cnt FROM dept_cnt)

SELECT d2.*, l.city, d1.cnt
FROM dept_cnt d1, hr.departments d2, hr.locations l
WHERE d1.department_id = d2.department_id
AND d2.location_id = l.location_id
AND d1.cnt = (SELECT max_cnt FROM dept_cnt_max);