오라클 SQL, 서브쿼리, WITH 구문(서브쿼리 팩토링)

 

오라클 SQL, 서브쿼리, WITH 구문(서브쿼리 팩토링)

WITH 구문은 복잡한 SQL문을 단순화 시키고  반복을 줄이기 위해 사용합니다. 

위 쿼리문에서 박스 영역은 동일하게 반복되는 SQL문장 입니다. 이를 WITH 구문으로 정의해서 공통으로 사용할 수 있습니다.

쿼리문이 단순해지고 반복도 즐었습니다. 물론 성능도 좋아집니다.^^

오라클9i R2 이후 사용 가능한 WITH 구문은 인라인 뷰로 처리되거나 임시 테이블로 해석 될 수 있습니다. 복잡한 SQL에서 동일 쿼리블록을 반복 사용한다면 해당 블록에 이름을 부여해 정의하고 재사용하는 편이 낫습니다. 쿼리문 코딩양도 줄이고 성능도 향상시킬 수 있기 때문입니다. 자주 실행되는 SQL 구문이라면 한 번만 파싱parsing되어 처리되므로 수행 성능상 장접도 있습니다. SQL문에서 서브 쿼리에 대한 반복적 인 참조가 매번 다시 쿼리되는 것이 아니라 WITH문으로 정의한 임시 테이블에서 쉽게 검색되므로 더 효율적일 수 있다는 것입니다.

WITH구문을 서브 쿼리 분해절(sub query factoring clause)이라고도 합니다.

<사용형식>

WITH query_name AS (   sql_query ) SELECT * FROM query_name query_name : sql_query문의 이름,임시테이블 이름. sql_query : WITH문으로 정의될 SQL 쿼리.

WITH문을 다중으로 사용할 수 있으며 형식은 다음과 같습니다.

WITH query_name1 AS (   sql_query1 ) ,           query_name2 AS (   sql_query2 ) ,           query_name3 AS (   sql_query3 )  SELECT ...

직원 목록과 해당 부서원 수를 출력하는 예문을 만들겠습니다. 인라인뷰, WITH 구문을 사용합니다.  FROM절 내의 SELECT 쿼리문이 한곳에서만 사용된다면 인라인 뷰나 WITH문 어느것을 사용해도 관계없겠지만 반복된다면 WITH문을 사용 하는것이 좋습니다.

5.7.1 직원 목록과 해당 부서원 수 출력하기 : 인라인뷰

EMP 테이블에서 직원 목록을 출력하면서 그직원이 속한 부서의 부서원 수를 같이 출력합시다. 인라인뷰를 이용하여 작성하세요.

SELECT E.ENAME              , EMPS.EMP_COUNT  FROM    EMP E,          ( ❶ SELECT DEPTNO                        , COUNT(*) AS EMP_COUNT           FROM    EMP           GROUP BY DEPTNO) EMPS WHERE  E.DEPTNO = EMPS.DEPTNO ❷;

<실행 결과>

	ENAME	EMP_COUNT
1	SMITH	5
2	ALLEN	6
...	...	...
13	MILLER	2

❶ 인라인 뷰에서 부서별로 GROUP BY한 결과를 EMPS라고 했습니다. 

❷ EMPS.DETNO와 EMP.DEPTNO를 조인하여 부서별 사원수를 조회 합니다.

조인은 양쪽다 일치하는 데이터만 조회되므로 부서코드값이 NULL인 “KING” 직원은 출력되지 않습니다.

5.7.1 직원 목록과 해당 부서원 수 출력하기 : WITH 구문

EMP 테이블에서 직원 목록을 출력하면서 그직원이 속한 부서의 부서원 수를 같이 출력합시다. WITH 구문으로 작성하세요.

WITH EMPS AS (   SELECT DEPTNO, COUNT(*) AS EMP_COUNT ❶   FROM    EMP   GROUP BY DEPTNO ) SELECT E.ENAME, EMPS.EMP_COUNT  FROM     EMP E,  ❷ EMPS  WHERE  E.DEPTNO = EMPS.DEPTNO;

<실행 결과>

	ENAME	EMP_COUNT
1	SMITH	5
2	ALLEN	6
...	...	...
13	MILLER	2

❶ WITH문에서 DEPTNO별로 GROUP BY한 결과를 EMPS라고 칭합니다. ❷ FROM 절의 테이블명 이 오는 자리에 WITH로 정의한 임시테이블의 이름을 기술하여 쿼리를 작성 합니다.​

 

#오라클 #서브쿼리 #SQL #WITH #WITH구문 #서브쿼리리팩토링