[MySQL] WITH절 사용하여 CTE 생성하기

CTE(Common Table Expressions)란?

CTE(a common table expression)는 하나의 SQL 문 내에서 일시적으로 사용되는 결과 집합(result set)이다. 이 결과 집합에 이름을 붙여 해당 SQL 문에서 여러 번 참조할 수 있다. 

 

 

WITH절 사용하여 CTE 생성하기

CTE를 명시하기 위해서는 WITH 절을 사용한다. WITH 절에서 한 개 혹은 여러 개의 CTE를 정의할 수 있다. 각 CTE는 쉼표로 구분된 하위 절(subclauses)로 표현되며, 각 하위 절은 서브쿼리를 포함하고 있다.(즉, 여러 개의 CTE를 정의할 때는 콤마로 구분하여 작성하면 됨!) 각 하위 절에서는 CTE의 서브쿼리를 통해 결과 집합을 생성하고, 그것을 지칭하는 이름을 지정해주어야 한다. 

 

 

1. 일반적인 CTE

-- 구조
WITH
    cte_name [(col_name [, col_name] ...)] AS (subquery)
 [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

- 각 CTE 이름은 해당하는 CTE 결과 집합에 접근하기 위해 참조될 수 있다. CTE 이름은 다른 CTE에서 참조될 수 있으며, 이를 통해 CTE는 다른 CTE를 기반으로 정의될 수 있다.

- AS (subquery)의 'subquery' 부분은 "CTE의 서브쿼리"라고 하며, 이는 CTE 결과 집합을 생성한다. CTE의 서브쿼리는 AS 뒤에 꼭 괄호와 함께 작성해야한다.

 

 

-- 예제
WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
  
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

위 예제는 WITH 절에서 cte1과 cte2라는 이름의 CTE를 정의하고, WITH 절 다음에 오는 최상위 SELECT 문에서 이를 참조하는 방법을 나타내는 코드.

 

 

2. 재귀 CTE (Recursive CTE)

: 자신의 이름을 참조하여 생성하는 CTE

 

-- 구조
WITH RECURSIVE cte_name AS (
    SELECT ...      -- return initial row set(비재귀 SELECT 부분)
    UNION ALL
    SELECT ...      -- return additional row sets(재귀 SELECT 부분)
    FROM cte_name
    WHERE ...
    )

SELECT * FROM cte_name;

• 재귀 CTE란, cte_name이라는 CTE를 정의하고 그 안에서 자신의 이름을 참조하여 데이터를 생성하는 것이다.
• 재귀 CTE 서브쿼리는 UNION ALL 또는 UNION [DISTINCT]을 기준으로 하여 두 부분으로 구성된다.

 

첫 번째 SELECT	CTE의 초기 행을 생성하며 CTE 이름을 참조하지 않는다 ⇒ 비재귀 SELECT 부분
두 번째 SELECT	추가 행을 생성하며 FROM 절에서 CTE 이름을 참조하여 재귀를 수행한다. 재귀는 이 부분에서 새로운 행을 생성하지 않을 때 끝난다.  ⇒ 재귀 SELECT 부분

 

• 각 SELECT 부분은 자체적으로 여러 SELECT 문을 결합한 UNION 일 수 있다.
• CTE 결과 열의 타입은 비재귀 SELECT 부분의 열 타입으로만 유추되며, 모든 열은 nullable(널 값을 허용)로 설정된다. (타입 결정 시, 재귀 SELECT 부분은 무시된다.)
• 비재귀 부분과 재귀 부분이 'UNION DISTINCT' 로 구분된 경우, 중복 행이 제거된다. 이는 쿼리가 동일한 행을 반복적으로 처리하지 않게 하여 무한 루프에 빠지지 않도록 도와준다. 
• 재귀 부분의 각 반복은 이전 반복에서 생성된 행에 대해서만 작동한다. 재귀 부분에 여러 쿼리 블록이 있는 경우, 각 쿼리 블록의 반복 순서는 지정되지 않으며, 각 쿼리 블록은 이전 반복 또는 다른 쿼리 블록이 이전 반복 종료 후 생성한 행에 대해 작동한다.

 

 

 

 

✏️ 예제 1

-- 코드 작성
WITH RECURSIVE cte AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
  )
  
SELECT * FROM cte;

-- 결과
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

두번째 SELECT 재귀 부분에서 각 반복시 이전 행 집합의 n 값보다 +1인 새로운 값을 가진 행을 생성한다.

-> 첫 번째 반복은 초기 행 집합인 1을 기준으로 작동하여 1+1 = 2 생성.

-> 두 번째 반복은 첫 번째 반복의 결과인 행 집합 2을 기준으로 작동하여 2+1 = 3 생성.

-> 이런 방식으로 n이 5보다 작지 않을 때까지 계속 진행.

 

 

✏️ 예제 2

-- 코드 작성 (Before: 비재귀적 부분을 제대로 작성하지 않음)

WITH RECURSIVE cte AS (
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
  )
  
SELECT * FROM cte;


-- 결과
+------+------+
| n    | str  |
+------+------+
|    1 | abc  |
|    2 | abc  |
|    3 | abc  |
+------+------+

재귀 SELECT 부분에서 CONCAT함수를 사용하여 str 열의 값인 'abc'를 연결했지만, CTE 결과 str 열의 값이 모두 'abc' 로만 들어갔다. 재귀 SELECT 부분에서 생성된 str 열의 값이 비재귀 SELECT 부분에서의 열의 값 너비보다 길어서 잘린 것이다. 이는 비재귀 SELECT 부분이 CTE 결과의 열 너비를 결정하기 때문이다.

 

 

-- 코드 작성 (After: 비재귀적 부분을 재귀 부분에 맞춰 작성함)

WITH RECURSIVE cte AS (
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str -- str의 너비를 재귀 부분에 맞춰 늘려줘야한다.
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
  )
  
SELECT * FROM cte;


-- 결과
+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+

이처럼 결과의 열 너비는 비재귀 부분에서 결정되므로, CTE의 재귀 부분에서 생성된 값의 길이가 비재귀 부분보다 더 길게 생성되는 경우 초기 비재귀 부분에서 재귀 부분에 맞춰 열을 더 넓게 만들어야 데이터 잘림을 방지할 수 있다.

 

 

 

 

[참고 자료]

MySQL Documentation > WITH

https://dev.mysql.com/doc/refman/9.0/en/with.html

MySQL :: MySQL 9.0 Reference Manual :: 15.2.20 WITH (Common Table Expressions)