SQL - WITH 사용법

1. WITH문의 필요성

  이미 작성된 Mapper에 Query를 살펴보다보면  subquery를 남발하여 작성된 쿼리를 볼 수 있다. 

subquery를 조건식에 한두번씩 사용하는 것이 아니라, 남발하게되면 가독성이 떨어지고 재사용이 불가능하여

같은 쿼리임에도 불구하고 재사용할 수가 없어 동일한 쿼리를 또 작성해놓은 어지러운 쿼리가 탄생하게 된다.

 

이를 해결할 수 있는 좋은 방법으로 WITH문이 존재한다.

 

WITH문은 SQL에서 임시 결과 집합, 보통 이를

공통 테이블식(CTE: Common table Expression)이라 부르는 것을 생성하여 다른 쿼리에서 사용 할 수 있게해주는

아주 유용한 녀석이다.

 

게다가 ANSI SQL 표준을 만족하는 문법이므로 거의 모든 DBMS에서 사용가능하다.

 

2. WITH문 사용법

WITH 구문을 사용하는 기본적인 방법은 아래와 같다.

WITH cte_name (column1, column2, ...) AS (
    -- CTE 쿼리 정의
    SELECT column1, column2, ...
    FROM your_table
    WHERE your_conditions
)
-- 메인 쿼리
SELECT * FROM cte_name;

WITH : CTE를 정의하기 위한 키워드

cte_name :  CTE 공통 테이블명을 지정

AS : CTE 정의를 시작하겠다는 것을 의미하는 키워드

 

다음은 WITH 구문을 사용하지 않고 각 부서의 직원수를 계산하여 부서정보와 직원 정보를 조인하는 쿼리이다.

# Query 01
SELECT
    d.department_id,
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    e.employee_name
FROM
    departments d
LEFT JOIN
    employees e ON d.department_id = e.department_id
GROUP BY
    d.department_id, d.department_name, e.employee_name;

 

위와 동일한 쿼리를 WITH 구문으로 작성하면 아래와 같다.

# Query 02
WITH DepartmentEmployeeCount AS (
    SELECT
        d.department_id,
        d.department_name,
        COUNT(e.employee_id) AS employee_count
    FROM
        departments d
    LEFT JOIN
        employees e ON d.department_id = e.department_id
    GROUP BY
        d.department_id, d.department_name
)
SELECT
    dec.department_id,
    dec.department_name,
    dec.employee_count,
    e.employee_name
FROM
    DepartmentEmployeeCount dec
LEFT JOIN
    employees e ON dec.department_id = e.department_id;

 

물론 위 예제에서 일반적으로 작성한 쿼리가 더 간단해보이지만

 

만약 # Query 01을 여기저기서 서브쿼리로 사용한다면 쿼리가 길어지고 복잡해진다.

그 땐 WITH구문을 사용하게 되면 DepartmentEmployeeCount라는 공통 테이블을 조인하여 사용해주기만 하면된다.