Programming/Database

MySQL CTE란? (+ WITH RECURSIVE CTE)

Jan92 2024. 1. 27. 22:18
반응형

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

반응형