[Oracle] WITH절 (WITH AS) 임시테이블 생성 및 문법 사용

1. WITH절이란

 - 오라클9 이후 버전부터 사용이 가능
 - 이름이 부여된 서브쿼리라고 생각하면 이해하기 편함
 - WITH 절을 이용하여 여러개의 임시테이블을 생성하는 경우 콤마(,)를 사용하여 다중생성 가능함
 - 임시테이블을 만든다는 관점에서본다면 VIEW와 쓰임새가 비슷

 

※ VIEW와 WITH절의 차이점 

VIEW  -  한번 생성하면  DROP할때까지 계속 존재 
WITH절 -  단일 실행할 쿼리문내에 정의되어 있을경우 해당 쿼리문안에서만 실행됨       

 

2. 언제 WITH절을 사용하는가?

- 많은 테이블을 조인해서 쿼리를 알아보기 힘든 경우
- 서브쿼리를 과하게 사용해서 쿼리의 성능이 저하되는 경우

 

3. 왜 WITH절을 사용하는가?

- 임시테이블을 생성하여 사용함으로써 가독성이 좋아짐
- 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 
  그 블록에 이름을 부여하여 재사용 할 수 있게 함으로서 쿼리 성능 향상
- WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있음
- 자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립되므로 쿼리의 성능향상에 도움
- 일반 Subquery는 서로 참조할 수 없지만 WITH문은 서로 참조 가능

 

★★

간단하게 말하면, 반복해서 동일한 쿼리를 작성하게 될 때 조건 등을 추가하게 되면

A1에도 수정하고 A2에서도 수정해야하는 번거로움이 발생하는데, 이때 사용하기 딱 좋음!

아래의 예시를 보고 이해를 해보자

 

SELECT E.DEPTNO
      ,E.ENAME
      ,D.DNAME
FROM   EMP E
INNER  JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE  E.ENUM = 1
UNION ALL
SELECT E.DEPTNO
      ,E.ENAME
      ,D.DNAME
FROM   EMP E
INNER  JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE  E.ENUM = 2

위 UNION ALL 쿼리에 DEPTNO = 30인 것만 출력하려면 

 

SELECT E.DEPTNO
      ,E.ENAME
      ,D.DNAME
FROM   EMP E
INNER  JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE  E.ENUM = 1
AND    E.DEPTNO = 30 -- 각각 추가
UNION ALL
SELECT E.DEPTNO
      ,E.ENAME
      ,D.DNAME
FROM   EMP E
INNER  JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE  E.ENUM = 2
AND    E.DEPTNO = 30 -- 각각 추가

 

각각  'AND  E.DEPTNO = 30' 을 추가해야 하는 번거로움이 발생한다.

만약 UNION ALL 해서 만든 쿼리가 100개라면 100개의 쿼리에 똑같이 넣어한다.

하지만 100개 추가했는데 다음날 DEPTNO = 10으로 변경해달라고 한다면

다시 100개의 쿼리를 수정해야하는 대참사가 발생하게된다.

 

WITH TEST_WITH AS
	(SELECT E.DEPTNO
		,E.ENAME
		,D.DNAME
		FROM   EMP E
		INNER  JOIN DEPT D ON E.DEPTNO = D.DEPTNO
		WHERE  E.ENUM = 1
		UNION ALL
		SELECT E.DEPTNO
		      ,E.ENAME
		      ,D.DNAME
		FROM   EMP E
		INNER  JOIN DEPT D ON E.DEPTNO = D.DEPTNO
		WHERE  E.ENUM = 2)
SELECT *
FROM   TEST_WITH
WHERE  DEPTNO = 30 -- 여기서 한번만 변경하면 번거로움이 사라짐

▲ WITH절을 사용하면 위처럼 한번만 수정하면 된다. (WHERE  DEPTNO = 30)

 

 

▼ 물론, 아래처럼 쿼리문을 만들어도 된다.
적재적소에 맞는 쿼리를 짜야하는 게 중요하니
다양한 구문을 알아두면 쿼리짜는데 도움이 많이 될 것 같다!

 

SELECT E.DEPTNO
      ,E.ENAME
      ,D.DNAME
FROM   EMP E
INNER  JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE  E.ENUM IN('1','2') AND E.DEPTNO = 30

 

 

4. WITH절 사용법

-- <WITH절의 기본 구조>
 
WITH 임시테이블명 AS 
(SUB QUERY -(SELECT절)) -- 임시테이블에 들어갈 값
SELECT 컬럼명,[컬럼명, ...]
FROM 임시테이블명;

WITH절의 기본 구조은 위와 같다.

 

 

예시1)

 

WITH tech_stack AS 
                (SELECT '자바' AS 기술스택
                 FROM   dual)
SELECT *
FROM   tech_stack;

예시2)

WITH tech_stack
     AS (SELECT '자바' AS 기술스택
         FROM   dual
         UNION ALL
         SELECT 'C#'
         FROM   dual)
SELECT *
FROM   tech_stack;

 

5. 다중 WITH문

-  WITH 절을 이용하여 여러개의 임시테이블을 생성하는 경우 콤마(,)를 사용하여 다중생성 가능함
-  WITH 절 안에서 다른 WITH을 참조가능

 

-- <다중 with절의 기본 구조> 

WITH 
	임시테이블명1 AS 
       (sub query문(select절)), 
        임시테이블명2 AS 
       (sub query문(select절))
SELECT 컬럼 ,[ 컬럼 ,.. . ]
FROM   임시테이블명1 ,
       임시테이블명2

 

예시1)

WITH TECH_STACK AS
	(SELECT '자바' AS 기술스택
		FROM   DUAL
		UNION ALL
		SELECT 'C#'
		FROM   DUAL),
TECH_JOB AS
	(SELECT 'DB엔지니어' AS 기술직업
		FROM   DUAL
		UNION ALL
		SELECT 기술스택
		FROM   TECH_STACK)

SELECT *
FROM   TECH_JOB;

 

6. WITH절에 구문을 여러번 참조하는 쿼리를 만들수록 그 효과가 배로 증가

- WITH절에 정의된 내용을 한번만 사용한다면 서브쿼리를 사용하는것과 크게 성능차이가 나지 않음
- WITH문의 가장 큰장점은 한번 WITH절의 내용을 한번에 올려놓고 계속 재사용한다는것 

 

WITH IDNUM AS
    (SELECT *
    FROM   EMP)

SELECT *
FROM   IDNUM
WHERE  IDNUM.ENUM = 1
UNION ALL
SELECT *
FROM   IDNUM
WHERE  IDNUM.ENUM = 4
UNION ALL
SELECT *
FROM   IDNUM
WHERE  IDNUM.ENUM = 2

 

 

---

 

아래의 링크 포스팅을 보고 한번에 이해가 되었다. 정말 도움이 많이 되었던 글이라 꼭 확인 해 봤으면 좋겠다. 

REFERENCE : https://royzero.tistory.com/50