WITH 절 (CTE, Common Table Expression) + RECURSIVE 재귀문

WITH 구문은 SQL에서 공통 테이블 표현식(CTE, Common Table Expression)을 정의하는 데 사용한다.

 

공통 테이블 표현식(CTE, Common Table Expression) :

CTE는 SQL에서 임시로 정의된 결과 집합을 의미하며, 일시적이고, 쿼리 실행 동안에만 존재한다.

CTE는 복잡한 쿼리를 간결하게 작성할 수 있도록 도와주며, 동일한 쿼리에서 여러번 사용되는 일시적인 데이터를 효율적으로 처리하는 데 유용하다.

-> 동일한 쿼리에서 여러 번 사용되는 중복 데이터 집합을 CTE로 한 번 정의하고 재사용할 수 있다.

 

 

 

WITH 구문은 특히 재귀적 쿼리와 복잡한 서브쿼리를 간소화하는 데 유용하게 사용된다.

 

 

 

 

1. 복잡한 서브쿼리 간소화 (기본 CTE 구조)

WITH CTE_Name (column1, column2, ...) AS (
    -- CTE 쿼리 정의
    SELECT column1, column2, ...
    FROM some_table
    WHERE condition
)
-- CTE를 활용한 메인 쿼리
SELECT *
FROM CTE_Name
WHERE some_condition;

 

WITH 뒤에 CTE의 이름을 지정해주고 AS 뒤 ( ) 안에 CTE쿼리를 작성한다.

CTE_Name 뒤, AS 앞 ( ) 안에 CTE 쿼리 결과의 열 이름을 지정해 줄 수도 있다.

(물론 SELECT 절에서 AS 로 별칭을 지정해줘도 된다.)

이후 메인쿼리에 어디서든 CTE_Name 쿼리를 사용해줄 수 있다.

 

->  동일한 CTE를 여러 번 참조할 수 있으며, 복잡한 쿼리를 여러 단계로 나누어 더 읽기 쉽게 만들어준다.

 

 

 

 

2. RECURSIVE 재귀문 (재귀적 CTE)

재귀적 CTE의 기본 구조

WITH RECURSIVE cte_name AS (
    -- 앵커 멤버: 초기 데이터 세트를 정의
    SELECT initial_query
    UNION ALL
    -- 재귀 멤버: 이전 결과를 사용하여 새로운 결과를 생성
    SELECT recursive_query
    FROM cte_name
    WHERE termination_condition
)
SELECT * FROM cte_name;

 

• 앵커 멤버 (Anchor Member): 재귀적 쿼리의 시작점을 정의. 기본 데이터 세트를 제공한다.
• 재귀 멤버 (Recursive Member): 앵커 멤버와 이전 반복 결과를 참조하여 재귀적으로 데이터를 생성한다.
• 이 두 멤버는 UNION ALL 또는 UNION을 사용하여 결합된다. 재귀적 멤버는 종료 조건을 만족할 때까지 계속해서 실행된다.

 

재귀 쿼리 예시

 

WITH RECURSIVE Numbers AS (
    -- 앵커 멤버: 초기 값 1
    SELECT 1 AS Number
    UNION ALL
    -- 재귀 멤버: 이전 결과에서 1을 더한 값을 추가
    SELECT Number + 1
    FROM Numbers
    WHERE Number < 10
)
SELECT * FROM Numbers

 

1. 앵커 멤버 실행 :

SELECT 1 AS Number가 실행되어 Number 칼럼에 1이 포함된 결과가 생성

 

2. 재귀 멤버 실행:

 

• 첫 번째 재귀: 'SELECT Number + 1 FROM Numbers WHERE Number < 10'에서 'Number'의 현재 값(1)에서 1을 더하여 2가 생성.
• 두 번째 재귀: 이전 결과 (2)에 1을 더하여 3이 생성.
• 이 과정은 Number가 10이 될 때까지 반복된다.

3. 종료 조건:

WHERE Number < 10 조건을 통해 재귀가 10에서 종료된다.

 

 

-> 재귀적 CTE를 사용하면 이렇게 반복적인 데이터를 손쉽게 생성할 수 있으며, 특히 계층 구조 데이터를 처리하거나 반복적인 계산을 수행할 때 유용하다.