[ORACLE] with절 : 서브쿼리를 미리 정의하여, 임시 테이블로 사용

 

개념

• 서브쿼리를 미리 정의하여 복잡한 쿼리를 간단하게 만드는 방법
• WITH절을 이용하여 하나 이상의 임시 테이블을 정의하고, 이를 SELECT절에서 임시 테이블로 사용할 수 있음
• 이를 통해서 쿼리의 가독성을 높이고, 성능을 향상할 수 있음
• WITH절은 CTE(Common Table Expression)라고도 함

---

 📌 CTE

• WITH 절을 이용하여 하나 이상의 임시 테이블을 정의할 때 사용하는 구문
• 이를 통해 복잡한 쿼리를 더 간단하게 작성할 수 있으며,
• 가독성과 유지보수성을 높일 수 있음

---

 

사용 시 장점

1. 시스템 부하 절약

• 메인 쿼리에서 서브 쿼리를 반복적으로 사용해야 하는 경우에도 시스템 부하를 줄일 수 있음

2. 성능 향상

• 서브쿼리를 실행할 때마다 같은 데이터를 다시 읽어오는 것이 아니라,
• WITH절을 사용하여 임시 테이블에 저장해두고 필요할 때마다 호출하기 때문

3. 가독성

• 복잡한 쿼리를 작성할 때, 서브쿼리를 WITH절로 정의하면 가독성이 향상

4. 재사용성

• WITH절에서 정의한 서브쿼리는 해당 쿼리뿐만 아니라, 다른 쿼리에서도 재 사용이 가능

 

 

WITH절의 구조와 수행 순서

1. WITH절의 구조

• WITH절로 만든 서브쿼리를 SELECT절에서 테이블로 사용할 수 있음

WITH 서브쿼리로 만들어지는 임시 테이블 이름 AS (
	 서브쿼리
)

SELECT *
FROM 서브쿼리로 만들어지는 임시 테이블 이름

 

2. WITH절 수행 순서

• 📌  WITH절로 만든 서브쿼리 실행 → 임시 테이블 생성 → 메인 쿼리 수행

1. WITH절의 서브쿼리들을 실행하여 임시 테이블을 생성
2. 임시 테이블을 사용하여 메인 쿼리를 실행

 

 

 

[ 예제 ]

1. 입사일의 연도별, 월별, 월급 평균별로 WITH절 만들어, 임시 테이블로 사용하기

WITH HIREDETE_BY_YYMM AS (
    SELECT
        EXTRACT(YEAR FROM HIREDATE) AS YEAR,
        EXTRACT(MONTH FROM HIREDATE) AS MONTH,
        AVG(SAL) AS AVG_SAL
    FROM EMP
    GROUP BY 
        EXTRACT(YEAR FROM HIREDATE),
        EXTRACT(MONTH FROM HIREDATE)            
)

SELECT *
FROM 
    HIREDETE_BY_YYMM
ORDER BY 
    YEAR ASC, 
    MONTH ASC;

 

[ 출력 결과 ]

 

 

2. 입사일의 연도별, 월별, 월급 평균별 WITH절 만들어, 임시 테이블로 사용하기

• 임시 테이블에서 평균 월급이 2000 이상인 레코드 조회

WITH HIREDETE_BY_YYMM AS (
    SELECT
        EXTRACT(YEAR FROM HIREDATE) AS YEAR,
        EXTRACT(MONTH FROM HIREDATE) AS MONTH,
        AVG(SAL) AS AVG_SAL
    FROM EMP
    GROUP BY 
        EXTRACT(YEAR FROM HIREDATE),
        EXTRACT(MONTH FROM HIREDATE)            
)

SELECT *
FROM 
    HIREDETE_BY_YYMM
WHERE 
    AVG_SAL > 2000
ORDER BY 
    YEAR ASC, 
    MONTH ASC;

 

[ 출력 결과 ]

 

 

 

 

 

 

 

 

출처

[DB/Postgres] 서브쿼리(Subquery) / WITH 절 이해하기 -1