MySQL CTE란? (+ WITH RECURSIVE CTE)

MySQL CTE(Common Table Expression)란?

WITH RECURSIVE CTE

최근 SQL 문에서 'WITH RECURSIVE CTE'라는 구문이 사용되는 것을 보고 공식 문서를 참고하여 관련된 기본적인 내용을 정리하게 되었습니다.

 

---

CTE란?

'CTE(Common Table Expression)'는 하나의 쿼리문 범위 내에서만 존재하며 여러 번 참조될 수 있는 이름이 지정된 일회성 테이블(정확하게는 결과 데이터)입니다.

CTE에는 '비재귀적(Non-Recursive) CTE'와 '재귀적(Recursive) CTE' 두 가지가 있으며, 아래 내용을 통해 각각에 대해 예시를 포함하여 자세하게 살펴보도록 하겠습니다.

 

* CTE는 MySQL 8.0.1 버전에서 도입되었으며, ANSI-SQL99 표준에서부터 나온 것이기 때문에 MySQL 뿐만 아니라 특정 버전 이상의 Oracle, MSSQL 등에서도 사용할 수 있습니다.

 

---

비재귀적(Non-Recursive) CTE

-- CTE 정의 부분
WITH cte (col1, col2) AS   
(
    SELECT 1, 2
    UNION ALL
    SELECT 3, 4
)
-- 정의된 CTE를 참조하여 사용하는 부분
SELECT col1, col2 FROM cte;

/* 결과
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
*/

(간단한 예시)

 

WITH 절을 통해 'col1', 'col2'라는 컬럼의 이름을 지정하고, 해당 컬럼에 들어갈 데이터를 입력한 'cte'라는 임시 테이블(임시로 사용할 결과 데이터)을 생성합니다.

 

* CTE를 선언할 때는 'WITH'이라는 키워드가 사용된다는 특징이 있습니다.

 

 

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;

 

다음 예시와 같이 하위 쿼리로부터 CTE를 만들 수도 있으며, 이때 여러 개의 CTE를 정의하는 경우라면 콤마 ', '를 통해 구분하게 됩니다.

 

***

해당 예시의 경우 '그냥 table1, table2를 조인하면 되는 거 아닌가?' 하는 의문이 들 수 있는데요.

단순한 사용 방법에 대한 예시이기 때문에 실제로 복잡한 쿼리에서는 적절하게 사용된 CTE를 통해 코드의 가독성을 높여줄 수 있으며, 정의된 CTE를 재사용할 수 있다는 장점이 있습니다.

또한 CTE를 통해 이어서 살펴볼 '재귀 쿼리'를 사용할 수 있다는 점도 큰 장점입니다.

 

 

CTE 장점 정리

1. 적절하게 사용될 경우 복잡한 쿼리문에서 코드의 가독성을 높여준다.

2. 해당 쿼리 안에서 여러 번 참조할 수 있다.

3. 재귀 쿼리를 활용할 수 있다.

 

 

---

재귀적(Recursive) CTE

-- RECURSIVE CTE
WITH RECURSIVE cte (...) AS 
(
   SELECT ...   -- Non Recursive 부분
   UNION ALL 
   SELECT ...  -- Recursive 부분
)
SELECT * FROM cte;

(기본적인 구조)

 

재귀적 CTE는 기본적으로 위와 같은 구조로 되어있으며, 계층적 또는 트리 구조의 데이터 순회에 유용하게 사용됩니다.

CTE가 자신을 참조하는 재귀인 경우 위 예시와 같이 WITH 다음 'RECURSIVE' 키워드를 포함해야 합니다.

(자신을 참조하지 않는 경우에는 RECURSIVE 키워드가 없어도 됩니다.)

 

이어서 위 구조에 대해 설명하자면 Recursive CTE의 경우 UNION ALL(또는 UNION)을 기준으로 '1. 초기 로우가 되는 Non Recursive 부분'과 '2. 추가 로우를 생성하고 순환하는 Recursive 부분' 두 부분으로 나뉜다는 특징이 있습니다.

 

 

-- 문제가 발생할 수 있는 경우
WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'test' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

 

Recursive CTE 사용에서는 주의할 점이 있는데요.

다음 예시와 같이 재귀 부분이 비재귀 부분보다 컬럼에 대한 크기가 더 큰 값을 생성하는 경우, 결과 데이터가 잘리거나 또는 Data too long for column 'str'과 같은 오류가 발생할 수 있습니다.

 

 

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

 

위에서 발생한 문제를 해결하기 위해서는 다음과 같이 'CAST()'를 사용하여 컬럼의 크기를 지정할 수 있습니다.

 

 

여기까지 공식 문서를 참고한 CTE에 대한 기본적인 내용이며, 특히 재귀 CTE의 경우 무한 재귀 방지(cte_max_recursion_depth 변수) 및 CTE 하위 쿼리에 대한 제약 조건 등이 있기 때문에 자세한 내용은 아래 공식 문서를 참고하시면 좋을 것 같습니다.

 

 

 

< MySQL 공식 문서 >

https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

 

< 다른 참고 자료 >

https://jjon.tistory.com/entry/MySQL-80-%EC%8B%A0%EA%B8%B0%EB%8A%A5-CTECommon-Table-Expression-%ED%99%9C%EC%9A%A9