With Clause(with as)
with절은 인라인뷰 또는 임시테이블 역할을 수행할 수 있는 기능을 제공합니다.
특히 사용을 고려할만한 곳은 반복되는 서브쿼리 구문이 많은 뷰입니다.
예를 들면 EMP 테이블과 DEPT 테이블을 조인한 결과를 여러번 사용한다고 가정했을 때입니다. 다음과 같은 예제를 보여드리겠습니다.
with 절 없이 예시 자료를 union all로 구현한다면 다음과 같을 것입니다.
뷰의 중복을 확인하실 수 있습니다.
SELECT EMPNO,
ENAME,
JOB,
EMP.DEPTNO,
DNAME,
LOC
FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO AND EMP.DEPTNO = '10'
UNION ALL
SELECT EMPNO,
ENAME,
JOB,
EMP.DEPTNO,
DNAME,
LOC
FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'CHICAGO'
UNION ALL
SELECT EMPNO,
ENAME,
JOB,
EMP.DEPTNO,
DNAME,
LOC
FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO AND DNAME = 'RESEARCH';
EMPNO ENAME JOB DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- ---------------------------- --------------------------
7782 CLARK MANAGER 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 10 ACCOUNTING NEW YORK
7698 BLAKE MANAGER 30 SALES CHICAGO
7566 JONES MANAGER 20 RESEARCH DALLAS
7788 SCOTT ANALYST 20 RESEARCH DALLAS
7902 FORD ANALYST 20 RESEARCH DALLAS
7369 SMITH CLERK 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 30 SALES CHICAGO
7521 WARD SALESMAN 30 SALES CHICAGO
7654 MARTIN SALESMAN 30 SALES CHICAGO
7844 TURNER SALESMAN 30 SALES CHICAGO
7876 ADAMS CLERK 20 RESEARCH DALLAS
7900 JAMES CLERK 30 SALES CHICAGO
14 rows selected.중복되는 뷰를 WITH AS로 다음과 같이 통합할 수 있습니다.
WITH TAB
AS (SELECT EMPNO,
ENAME,
JOB,
EMP.DEPTNO,
DNAME,
LOC
FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO)
SELECT *
FROM TAB
WHERE DEPTNO = '10'
UNION ALL
SELECT *
FROM TAB
WHERE LOC = 'CHICAGO'
UNION ALL
SELECT *
FROM TAB
WHERE DNAME = 'RESEARCH';
EMPNO ENAME JOB DEPTNO DNAME LOC
---------- -------------------- ------------------ ---------- ---------------------------- --------------------------
7934 MILLER CLERK 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 10 ACCOUNTING NEW YORK
7900 JAMES CLERK 30 SALES CHICAGO
7844 TURNER SALESMAN 30 SALES CHICAGO
7654 MARTIN SALESMAN 30 SALES CHICAGO
7521 WARD SALESMAN 30 SALES CHICAGO
7698 BLAKE MANAGER 30 SALES CHICAGO
7499 ALLEN SALESMAN 30 SALES CHICAGO
7902 FORD ANALYST 20 RESEARCH DALLAS
7876 ADAMS CLERK 20 RESEARCH DALLAS
7788 SCOTT ANALYST 20 RESEARCH DALLAS
7566 JONES MANAGER 20 RESEARCH DALLAS
7369 SMITH CLERK 20 RESEARCH DALLAS
14 rows selected.with as 에서 뷰를 단수로 사용한다면 다음과 같이 사용합니다.
WITH TA AS (SELECT * FROM EMP)
SELECT *
FROM TA;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 91/01/05 2850 30
7782 CLARK MANAGER 7839 99/09/06 2450 10
7566 JONES MANAGER 7839 01/02/04 2975 20
7788 SCOTT ANALYST 7566 03/06/17 3000 20
7902 FORD ANALYST 7566 81/03/12 3000 20
7369 SMITH CLERK 7902 07/12/01 800 20
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
7521 WARD SALESMAN 7698 81/02/22 1250 500 30
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
7844 TURNER SALESMAN 7698 81/08/09 1500 0 30
7876 ADAMS CLERK 7788 87/05/23 1100 20
7900 JAMES CLERK 7698 81/03/12 950 30
7934 MILLER CLERK 7782 03/01/23 1300 10
7839 KING PRESIDENT 96/11/17 5000 10
14 rows selected.WITH AS 여러개 사용 방법
with as 에서 뷰를 복수로 사용하면 콤마(,)로 구분하시면 됩니다.
WITH TA AS (SELECT * FROM EMP),
TB AS (SELECT * FROM DEPT)
SELECT *
FROM TA LEFT JOIN TB ON TB.DEPTNO = TA.DEPTNO;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAMLOC
---------- -------------------- ------------------ ---------- -------- ---------- ---------- ---------- ---------- ---------------------------- --------------------------
7839 KING PRESIDENT 96/11/17 5000 10 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 03/01/23 1300 10 10 ACCOUNTING NEW YORK
7782 CLARK MANAGER 7839 99/09/06 2450 10 10 ACCOUNTING NEW YORK
7876 ADAMS CLERK 7788 87/05/23 1100 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 07/12/01 800 20 20 RESEARCH DALLAS
7902 FORD ANALYST 7566 81/03/12 3000 20 20 RESEARCH DALLAS
7788 SCOTT ANALYST 7566 03/06/17 3000 20 20 RESEARCH DALLAS
7566 JONES MANAGER 7839 01/02/04 2975 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 81/03/12 950 30 30 SALECHICAGO
7844 TURNER SALESMAN 7698 81/08/09 1500 0 30 30 SALECHICAGO
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 30 SALECHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 30 SALECHICAGO
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 30 SALECHICAGO
7698 BLAKE MANAGER 7839 91/01/05 2850 30 30 SALECHICAGO
14 rows selected.다음은 오라클 테스트 테이블(demobld.sq)을 with as로 조회해 보는 예시입니다.
WITH EMP
AS (SELECT 7369 EMPNO, 'SMITH' ENAME, 'CLERK' JOB, 7902 MGR, TO_DATE ('19801217') HIREDATE, 800 SAL, NULL COMM, 20 DEPTNO FROM DUAL UNION ALL
SELECT 7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE ('19810220'), 1600, 300, 30 FROM DUAL UNION ALL
SELECT 7521, 'WARD', 'SALESMAN', 7698, TO_DATE ('19810222'), 1250, 500, 30 FROM DUAL UNION ALL
SELECT 7566, 'JONES', 'MANAGER', 7839, TO_DATE ('19810402'), 2975, NULL, 20 FROM DUAL UNION ALL
SELECT 7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE ('19810928'), 1250, 1400, 30 FROM DUAL UNION ALL
SELECT 7698, 'BLAKE', 'MANAGER', 7839, TO_DATE ('19810501'), 2850, NULL, 30 FROM DUAL UNION ALL
SELECT 7782, 'CLARK', 'MANAGER', 7839, TO_DATE ('19810609'), 2450, NULL, 10 FROM DUAL UNION ALL
SELECT 7788, 'SCOTT', 'ANALYST', 7566, TO_DATE ('19821209'), 3000, NULL, 20 FROM DUAL UNION ALL
SELECT 7839, 'KING', 'PRESIDENT', NULL, TO_DATE ('19811117'), 5000, NULL, 10 FROM DUAL UNION ALL
SELECT 7844, 'TURNER', 'SALESMAN', 7698, TO_DATE ('19810908'), 1500, 0, 30 FROM DUAL UNION ALL
SELECT 7876, 'ADAMS', 'CLERK', 7788, TO_DATE ('19830112'), 1100, NULL, 20 FROM DUAL UNION ALL
SELECT 7900, 'JAMES', 'CLERK', 7698, TO_DATE ('19811203'), 950, NULL, 30 FROM DUAL UNION ALL
SELECT 7902, 'FORD', 'ANALYST', 7566, TO_DATE ('19811203'), 3000, NULL, 20 FROM DUAL UNION ALL
SELECT 7934, 'MILLER', 'CLERK', 7782, TO_DATE ('19820123'), 1300, NULL, 10 FROM DUAL),
DEPT
AS (SELECT 10 DEPTNO, 'ACCOUNTING' DNAME, 'NEW YORK' LOC FROM DUAL UNION ALL
SELECT 20, 'RESEARCH', 'DALLAS' FROM DUAL UNION ALL
SELECT 30, 'SALES', 'CHICAGO' FROM DUAL UNION ALL
SELECT 40, 'OPERATIONS', 'BOSTON' FROM DUAL),
SALGRADE
AS (SELECT 1 GRADE, 700 LOSAL, 1200 HISAL FROM DUAL UNION ALL
SELECT 2, 1201, 1400 FROM DUAL UNION ALL
SELECT 3, 1401, 2000 FROM DUAL UNION ALL
SELECT 4, 2001, 3000 FROM DUAL UNION ALL
SELECT 5, 3001, 9999 FROM DUAL)
SELECT * FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
---------- ------------ ------------------ ---------- -------- ---------- ---------- ---------- ---------- -------------------- ----------------
7369 SMITH CLERK 7902 80/12/17 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 81/04/02 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 81/06/09 2450 10 10 ACCOUNTING NEW YORK
7788 SCOTT ANALYST 7566 82/12/09 3000 20 20 RESEARCH DALLAS
7839 KING PRESIDENT 81/11/17 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 30 SALES CHICAGO
7876 ADAMS CLERK 7788 83/01/12 1100 20 20 RESEARCH DALLAS
7900 JAMES CLERK 7698 81/12/03 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 81/12/03 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 82/01/23 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.With As + Insert
INSERT INTO EMP (EMPNO, ENAME)
WITH TAB AS (SELECT '9999', 'NAME' FROM DUAL)
SELECT *
FROM TAB;with 절 다음에는 select 키워드가 따라와야 합니다. 그리하여 with as + update 는 대상 테이블의 컬럼을 지정하거나 뷰를 가공하여 활용합니다.
대신 insert 구문은 손쉽게 insert into [statement] with as 로 간단히 사용할 수 있습니다.
예제 생성 시 활용자료로 이용하려 만들어 보았습니다.
with as hint, /*+ MATERIALIZE */ /*+ INLINE */
가끔 볼수 수 있는 힌트입니다! materialize 힌트는 글로벌 템프 테이블을 만들라는 의미이고, inline 힌트는 인라인뷰에서 실행하라는 의미입니다. 오라클데이터베이스가 발전할수록 굳이 지정할 필요 없이, 알아서 퍼포먼스를 관리하기 때문에 별도로 지정하실 필요는 없어보입니다. 이러한 힌트도 있다는 것만 보고 넘어가시면 돼요.
WITH AS 를 SELECT 중간에 실행하기
WITH AS 절은 가독성을 위해 가장 먼저 배치하는 편이기는 한데, 경우에 따라 위치를 변경할 수도 있어요. 다음과 같이 할 수도 있습니다.
SELECT *
FROM (WITH VW AS (SELECT * FROM EMP)
SELECT DUAL.*, VW.*
FROM DUAL LEFT JOIN VW ON 0 = 0);
DU EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-- ---------- -------------------- ------------------ ---------- -------- ---------- ---------- ----------
X 7698 BLAKE MANAGER 7839 91/01/05 2850 30
X 7782 CLARK MANAGER 7839 99/09/06 2450 10
X 7566 JONES MANAGER 7839 01/02/04 2975 20
X 7788 SCOTT ANALYST 7566 03/06/17 3000 20
X 7902 FORD ANALYST 7566 81/03/12 3000 20
X 7369 SMITH CLERK 7902 07/12/01 800 20
X 7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30
X 7521 WARD SALESMAN 7698 81/02/22 1250 500 30
X 7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30
X 7844 TURNER SALESMAN 7698 81/08/09 1500 0 30
X 7876 ADAMS CLERK 7788 87/05/23 1100 20
X 7900 JAMES CLERK 7698 81/03/12 950 30
X 7934 MILLER CLERK 7782 03/01/23 1300 10
X 7839 KING PRESIDENT 96/11/17 5000 10
14 rows selected.