WITH 구문

* WITH 구문 설명

  - WITH 구문 내의 쿼리의 결과(SUB Query)가 여러번 사용할 때 유용하다.

  - SUB Query 블럭에 이름을 지정할 수 있도록 해줌.

  - 오라클 옵티마이저는 Query를 인라인 뷰나 임시 테이블로 여긴다.

  - Oracle 9 이상 지원.

1. WITH Syntax

WITH ALIAS_NAME AS ( SUB Query )

SELECT COLUMNS FROM ALIAS_NAME;

Example

WITH AA AS

     (SELECT ROWNUM, 'TEST1', SYSDATE FROM DUAL

      UNION ALL

      SELECT ROWNUM, 'TEST2', SYSDATE FROM DUAL

      UNION ALL

      SELECT ROWNUM, 'TEST3', SYSDATE FROM DUAL)

SELECT *

  FROM AA;

 

2. WITH Syntax Multi Sub Query

WITH ALIAS_NAME_1 AS ( SUB Query )

   , ALIAS_NAME_2 AS ( SUB Query )

SELECT COLUMNS

  FROM ALIAS_NAME

 WHERE Join Condition;

Example

WITH AA AS

     (SELECT ROWNUM AS SEQ, 'TEST1' AS NAME, SYSDATE FROM DUAL

      UNION ALL

      SELECT ROWNUM AS SEQ, 'TEST2' AS NAME, SYSDATE FROM DUAL

      UNION ALL

      SELECT ROWNUM AS SEQ, 'TEST3' AS NAME, SYSDATE FROM DUAL)

   , BB AS

     (SELECT ROWNUM AS SEQ, 'TEST1' AS NAME, SYSDATE FROM DUAL

      UNION ALL

      SELECT ROWNUM AS SEQ, 'TEST2' AS NAME, SYSDATE FROM DUAL

      UNION ALL

      SELECT ROWNUM AS SEQ, 'TEST3' AS NAME, SYSDATE FROM DUAL)

SELECT *

  FROM AA, BB

 WHERE AA.NAME = BB.NAME;