[SQL] WITH절 사용하기

1. WITH ~ AS

동일한 SQL이 반복되어 사용될 때 성능을 높이기 위해 WITH절을 사용한다.

WITH TABLE_NAME AS ()

SELECT COL1, COL2, ...
FROM TABLE_NAME
...

검색 시간이 오래걸리는 SQL이 하나의 SQL 내에서 반복되어 사용될 때 성능을 높이기 위한 방법으로 WITH절을 사용한다. WITH절 이후에 테이블 이름을 지정하고, AS 이후에 임시 저장영역(Temporary Tablespace)을 정의 해 저장한다

EX ) 직업과 직업별 토탈 월급을 출력하는데 직업별 토탈 월급들의 평균값보다 더 큰 값만 출력

-- WITH절 없이 조회
SELECT JOB, SUM(SAL) AS 월급총합
FROM EMP
GROUP BY JOB
HAVING SAL > (SELECT AVG(SAL)
		FROM EMP 
		GROUP BY JOB);

-- WITH절을 사용한 조회
WITH JOB_SUMSAL AS (SELECT JOB, SUM(SAL) AS 월급총합
			FROM EMP
			GROUP BY JOB)

SELECT JOB, 월급총합
FROM JOB_SUMSAL
WHERE 월급총합 > (SELECT AVG(월급총합) FROM JOB_SUMSAL);

테이블의 데이터가 대용량이어서 출력하는데 시간이 많이 걸려 20분이 걸린다고 하면 WITH절 없이 조회할 경우 동일한 테이블을 두번이나 사용했으므로 40분이 걸리게 된다. 그러나 WITH절로 고쳐 작성하게 되면 20분 걸려서 얻은 데이터를 임시저장 영역에 저장하고 그 데이터를 테이블 이름으로 불러오기만 하면 되기 때문에 시간은 절반으로 줄어든다. 단 WITH절에서 사용한 TEMP 테이블은 WITH절 내에서만 사용 가능하다.

 

반응형

728x90

 

2. SUBQUERY FACTORING

WITH절 쿼리의 결과를 임시 테이블로 생성하는 것을 SUBQERY FACTORING이라고 한다. 서브쿼리 2개가 데이터를 참고할 수 있게 WITH절을 사용한다.

 

EX ) 직업별 월급의 평균값에 3000을 더한 값보다 더 큰 부서 번호별 월급의 총합을 출력하라

이 문제를 해결하기 위해서는 직업별 월급의 평균값 테이블과 부서 번호별 월급의 총합 테이블의 비교가 필요하다. 따라서 FROM절의 서브쿼리를 사용하려고 할 수 있다.

-- FROM절의 서브쿼리 예시
SELECT DEPTNO, SUM(SAL)
FROM (SELECT JOB, SUM(SAL) 월급총합
      FROM EMP
      GROUP BY JOB) AS JOB_SUMSAL,
     (SELECT DEPTNO, SUM(SAL) 월급총합
      FROM EMP
      GROUP BY DEPTNO
      HAVING SUM(SAL) > (SELECT AVG(월급총합) + 3000
			  FROM JOB_SUMSAL) 
     ) AS DEPTNO_SUMSAL

하지만 FROM 절의 서브쿼리에서 정의한 테이블을 다른 서브쿼리에서 사용하는 것이 불가능하기때문에 위 SQL을 실행하면 명령어가 올바르게 종료되지 않았다는 에러가 발생한다. 이와 같은 문제를 해결하기 위해 WITH절을 사용한다

--WITH절을 이용 예시

WITH JOB_SUMSAL AS (SELECT JOB, SUM(SAL) 월급총합
		     FROM EMP
		     GROUP BY JOB),
     DEPTNO_SUMSAL AS (SELECT DEPTNO, SUM(SAL) 월급총합
                       FROM EMP
                       GROUP BY DEPTNO
                       HAVING SUM(SAL) > (SELECT AVG(월급총합) + 3000
                                          FROM JOB_SUMSAL)
                       )

SELECT DEPTNO, 월급총합
FROM DEPTNO_SUMSAL;

FROM절의 서브쿼리로는 불가능하지만 WITH절을 이용하면 임시 저장 영역에 임시 테이블을 생성하므로 참조가 가능해진다.