[SQL] - MySQL CTE란?

MySQL CTE란? (+ 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

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

 

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

CTE가 자신을 참조하는 재귀인 경우 위 예시와 같이 WITH 다음 '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 CHER(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 하위 쿼리에 대한 제약 조건 등이 있기 때문에 자세한 내용은 아래 공식 문서를 참고하면

좋을거 같다.

 

<My SQL 공식 문서>

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

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

 

<참고 자료>

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

MySQL 8.0 신기능 CTE(Common Table Expression) 활용