[SQL] WITH 문법 (feat. recursive)

 

WITH를 사용하여 복잡한 쿼리를 간결하게 표현하고, 임시 결과를 생성하여 가독성을 향상시킬 수 있다.

 

 

 

SQL WITH절은 임시적인 결과 집합을 생성하여 쿼리 내에서 사용할 수 있게 해주는 기능이다.

주로 서브커리의 가독성 향상이나 동일한 서브쿼리를 여러번 사용 하는 등의 경우에 유용하게 사용할 수 있다. 

 

일반적인 사용 예시 >

WITH cte_name (column1, column2, ...) AS (
    -- 임시 결과 집합을 생성하는 쿼리
    SELECT column1, column2, ...
    FROM your_table
    WHERE your_conditions
)
-- 기본 쿼리에서 WITH 절에 정의된 임시 결과를 활용
SELECT *
FROM cte_name;

 

위의 쿼리에서 'cte_name'은 임시 결과 집합의 이름이며, 필요한 경우 해당 결과 집합의 컬럼도 정의할 수 있다.

즉, 서브쿼리와 매우 유사한 개념으로 WITH절 안에서 SELECT로 조회한 컬럼들은 임시 결과로 본 쿼리에서 사용할 수 있다.

 

 

부서별 평균 급여를 계산하는 쿼리 >

WITH AvgSalaryByDept AS (
	SELECT dept_id, AVG(salary) AS avg_salary
    FROM emp
    GROUP BY dept_id
)
SELECT e.emp_id, e.salary, a.avg_salary
FROM emp e
JOIN AvgSalaryByDept a
ON e.dept_id = a.dept_id

 

부서별 평균 급여를 계산하는 쿼리는 AvgSalaryByDept 라는 임시 결과 집합을 생성하여 부서별 평균 급여를 계산하여 만든 임시 집합 데이터를 활용하여 직원의 부서별 급여와 부서별 평균 급여를 조회한다.

 

 

Recursive 쿼리

Recursive 쿼리는 WITH Recursive를 사용하여 정의되며, 자기 자신을 참조하는 재귀적인 구조를 가지고 있다.

주로 계층 구조 데이터를 쿼리하거나 계층 구조를 가진 테이블에서 작업할 때 활용된다. 

 

 

WITH RECURSIVE 사용 예시 >

WITH RECURSIVE OrgHierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.manager_id, e.employee_name, oh.level + 1
    FROM employees e
    JOIN OrgHierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM OrgHierarchy;

 

위의 쿼리는 조직 구조를 재귀적으로 조회하는 방법이다.

첫번째 SELECT에서 최상위 관리자(manager_id가 없는 사람)를 선택하고, 그 다음 SELECT 부터는 자기 자신(OrgHierarchy)을 참조(상사의 id = 부하의 상사 id) 하여 부하직원을 찾아내는 식으로 계속 진행된다.

 

이러한 방식으로 WITH 절을 Recursive와 같이 사용하면 재귀적인 쿼리로 계층 구조를 쉽게 조회할 수 있다.

 

결론:
WITH는 서브쿼리와 같은 개념으로 성능상의 차이는 없지만 가독성을 향상시키고 중복 코드를 줄이는데에 효과적이다. + RECURSIVE와 함께 사용하여 재귀적으로 사용할 수 있다.
(WITH절을 사용하면 동일한 서브쿼리를 반복해서 작성할 필요 없이 한 번 정의된 임시 결과 집합을 해당 쿼리에 내에서 자유롭게 사용할 수 있다)  

 

 

 

 

- 끝 -

 

 

reference.

https://mariadb.com/kb/en/recursive-common-table-expressions-overview/

https://www.boardinfinity.com/blog/sql-with-clause-tutorial-and-examples/