[ORACLE] WITH 절

오라클 WITH 절은 오라클 9 이후 버전 부터 사용 가능하며 이름을 정의한 서브쿼리라 생각하면 된다.

테이블을 임시로 만드는 VIEW와 쓰임이 비슷하지만
VIEW는 DROP해야 사라지지만 WITH절은 쿼리 안에서만 실행됨.

 

* WITH절 장점

WITH절은 공유메모리에 임시 테이블을 생성하여 재사용이 가능하도록 함.
서브쿼리는 서로 참조할 수 없지만, WITH절은 참조 가능.
이름을 부여하여 재사용할 수 있게 함으로 전체적인 가독성 및 쿼리 성능 향상에 도움이 됨.

 

- 기본 사용 법

WITH EXAMPLE AS
(
SELECT 'PARK' AS NAME
FROM DUAL
)
SELECT * FROM EXAMPLE;

 

- WITH절 참조

WITH PEOPLE AS -- 첫번째 WITH
(
SELECT 'PARK' AS NAME FROM DUAL
UNION ALL
SELECT 'ROH' AS NAME FROM DUAL
),
PEOPLE2 AS  -- 두번째 WITH
(
SELECT 'KIM' AS NAME FROM DUAL
UNION ALL
SELECT NAME FROM PEOPLE  -- 첫번째 WITH 참조
)
SELECT * FROM PEOPLE2;