with문 사용방법

WITH절 이란?

"WITH" 절은 공통 테이블 표현식(Common Table Expression, CTE)을 정의하는 SQL 구문입니다. CTE는 복잡한 쿼리를 더 간결하고 가독성 있게 작성하고, 쿼리의 일부를 재사용하고 가공하는 데 도움이 되는 기능입니다. 

 

WITH절은 다른 DBMS에서도 많이 지원하는 기능이나 오라클에서는 WITH절에 정의된 SQL 문장으로 오라클 공유 메모리에 임시 테이블을 생성하여 반복 재사용이 가능하도록 할 수 있습니다. 그렇게 하면 동일 테이블 접근을 최소화하며 메모리에 생성된 임시 테이블에서 필요한 데이터를 메모리로 접근하기 때문에 디스크 IO로 테이블에 접근하는 것보다 성능을 개선할 수 있습니다.

WITH절을 완전히 숙지하려면 UNION ALL, DECODE/CASE, WITH, ROLLUP/Grouping Sets 등의 사용법을 알아야 합니다. 왜냐하면 위의 문장들은 서로 형태 변환이 가능하여 성능상 매우 유리하게 사용될 수 있기 때문입니다.

 

WITH절을 사용하는 이유?

WITH 절을 사용하는 이유와 이점은 다음과 같습니다:

1. 가독성 향상: CTE를 사용하면 쿼리를 더 읽기 쉽게 만들 수 있습니다. 복잡한 쿼리를 여러 부분으로 나누고 각 부분에 의미 있는 이름을 부여하여 쿼리의 구조를 명확하게 만듭니다.
2. 코드 재사용: CTE를 정의하면 동일한 논리를 여러 쿼리에서 재사용할 수 있습니다. 즉, CTE를 한 번 정의하면 동일한 로직을 다른 쿼리에서도 활용할 수 있어서 코드 중복을 피할 수 있습니다.
3. 복잡한 계산 및 가공: CTE를 사용하면 중간 결과를 계산하고 가공할 수 있습니다. 예를 들어, 중간 결과를 CTE로 정의하고 그 결과를 다른 CTE나 메인 쿼리에서 활용하여 복잡한 계산을 수행할 수 있습니다.
4. 쿼리의 성능 향상: CTE는 최적화된 쿼리 실행 계획을 생성하는 데 도움이 될 수 있습니다. 특히, CTE를 사용하여 필요한 데이터를 사전에 필터링하고 정렬할 수 있어서 쿼리의 성능을 향상시킬 수 있습니다.

WITH절은 복잡한 SQL에서 동일 블록에 대해 반복적으로 SQL문을 사용하는 경우 그 블록에 이름을 부여하여 재사용할 수 있게 함으로써 쿼리 성능을 높일 수 있는데 WITH절을 이용하여 미리 이름을 부여해서 Query Block을 만들 수 있습니다. 자주 실행되는 경우 한 번만 Parsing되고 Plan 계획이 수립되므로 쿼리의 성능 향상에 도움이 됩니다.

 

CTE를 정의하려면 다음과 같은 구문을 사용합니다:

 

WITH cte_name (column1, column2, ...) AS (
    -- CTE의 정의
    SELECT ...
    FROM ...
    WHERE ...
)
-- CTE를 활용한 메인 쿼리
SELECT ...
FROM cte_name
WHERE ...

 

여기서 주요 구성 요소는 다음과 같습니다:

• "WITH" 키워드: CTE 정의를 시작하는 키워드입니다.
• "cte_name": CTE의 이름을 지정합니다. 이 이름은 CTE를 참조하는 데 사용됩니다.
• "(column1, column2, ...)": CTE에서 선택적으로 열 목록을 지정할 수 있습니다. 이 부분은 선택 사항이며, CTE에서 모든 열을 선택할 때는 생략할 수 있습니다.
• "AS": CTE 정의를 시작합니다.
• CTE의 정의: 원하는 쿼리를 CTE 내부에 작성합니다.
• "SELECT" 문: CTE를 활용하여 메인 쿼리를 작성할 수 있습니다. 이 부분에서는 CTE의 결과를 사용하여 다른 작업을 수행할 수 있습니다.

다음은 예제입니다. employees 테이블에서 부서별 직원 수를 계산하는 CTE를 정의하고, 이를 활용하여 부서 이름과 직원 수를 가져오는 메인 쿼리를 작성하는 것입니다:

 

WITH department_employee_counts AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, c.employee_count
FROM departments d
LEFT JOIN department_employee_counts c
ON d.department_id = c.department_id;

 

위 예제에서 "department_employee_counts"라는 CTE를 정의하고, 이를 활용하여 부서 이름과 직원 수를 얻는 메인 쿼리를 작성했습니다. CTE를 사용하면 중복 코드를 피하고 쿼리를 더 읽기 쉽게 만들 수 있습니다.

 

참고: https://grandma-coding.tistory.com/entry/OracleWITH%EC%A0%88