CTE?
A common table expression(CTE) is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.
CTE는 단일 쿼리 내부에서 임시로 결과를 저장해 놓고, 해당 쿼리 내에서 반복적으로 사용 가능한 임시 결과 집합(테이블)이다.
즉, 임시로 쿼리 결과를 저장해 놓고, 여러번 참조해서 사용하는 용도로 사용한다.
메인 쿼리 내에서 정의되어 사용된다는 점이 서브 쿼리(파생 테이블)과 비슷하지만 재사용이 가능하다는 점에서 서브 쿼리와 차이가 있다.
CTE와의 비교 대상으로는 VIEW가 있다. 하지만 VIEW의 생성을 위해서는 권한이 필요하고, 이는 사전에 정의 되어야 한다. 반면 CTE는 권한이 필요 없으며 쿼리문이 끝날 때 까지만 지속되는 일회성 테이블이다.
CTE 특징
쿼리의 가독성 향상
재사용성
향상된 성능..?
권한이 필요 없음 (비교 대상인 VIEW와의 차이점)
재귀 쿼리 생성 가능 : 계층적 데이터를 처리할 때 매우 유용
CTE 문법
CTE를 사용하기 위한 문법은 다음과 같다. WITH 키워드와 AS 로 cte_name에 맵핑할 쿼리를 작성한 후 cte_name으로 지정한 테이블을 조회한다.
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의 이름 지정 부분 뒤에 (column_list)가 오는 경우 생성되는 cte의 열(필드) 이름을 지정할 수 있다.
WITH
cte1 (col1, col2) AS (SELECT a,b FROM table1)
SELECT * FROM cte1;CTE 예제
# 한식당만 조회하는 CTE
WITH kor_restaurants AS (
SELECT *
FROM restaurants
WHERE rest_type = 'kor'
)
SELECT rest_name, rest_id FROM kor_restaurants;CTE를 정의하는 부분의 쿼리를 보면 굉장히 간단한 쿼리이다. restaurants 테이블에서 rest_type 필드의 값이 ‘kor’인 식당만 조회하여 kor_restaurants에 저장해놓은 것이다.
정의된 CTE를 이용하는 쿼리를 보면 위에서 정의한 kor_restaurants에서 rest_name과 rest_id 필드의 값만 조회하는 것을 볼 수 있다.
VIEW 대신 CTE
데이터베이스 사용자에게 VIEW를 생성할 권한이 없을 수 있다. CTE는 일반 SELECT와 마찬가지로 특정 권한을 요구하지 않는다.
또한 일반적인 경우 VIEW 대신 CTE를 사용하는 것이 단일 materialization만 필요하고(테이블이 cache처럼 임시로 저장된다는 의미) 생성된 임시 테이블을 메인 쿼리에서 여러 번 참조할 수 있기 때문에 더 빠르고 효율적이다.
단, 이로 인해 CTE를 무분별하게 사용할 경우, query performance가 오히려 더 떨어질 수도 있다.
Query optimizer가 execution plan을 계산할 때, CTE의 최적화를 고려하지 않기 때문에 CTE를 생성할 때 필요한 데이터를 미리 필터링하는 등 최적화를 고려한 쿼리 작성이 필요하다.
Recursive CTE
A recursive common table expression is one having a subquery that refers to its own name.
재귀 CTE는 CTE를 정의하는 서브 쿼리에 자기 자신을 참조하는 구문이 있는 형식으로 정의된다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n+1 FROM cte WHERE n < 5
)
SELECT * FROM cte;
# 결과
+------+
| n |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+쿼리를 분석해보자.
CTE를 정의하는 괄호 안의 쿼리를 서브 쿼리라고 하자.
서브 쿼리의 첫 SELECT 절은 CTE의 첫 row를 생성하고, 이는 자기 자신을 참조하지 않는다.
두 번째 SELECT 절은 첫 row를 제외한 추가 row들을 생성하는데, 이는 FROM 절에 있는 자기 자신(cte)를 참조한다.
이러한 재귀는 두 번째 SELECT 절에서 더 이상 새로운 row를 생성하지 않을 때 까지 반복되다 종료된다.
이때 더 이상 새로운 row를 생성하지 않을 조건은 WHERE 절에 작성된다.
따라서 recursive CTE의 구성은 nonrecursive SELECT 절과 recursive SELECT 절로 이루어져있다.
Recursive CTE 특징
컬럼의 데이터 타입은 nonrecursive SELECT절에 의해 결정된다.
컬럼은 nullable하다.
nonreucursive part와 recursive part를 구분하는 키워드(위의 예시에서는 UNION ALL)에 따라 중복 허용 여부가 결정된다. (UNION DISTINCT 키워드 사용시 중복 제거)
Recursive part의 각 iteration은 오직 이전 iteration에서 생성된 row에 대해서만 동작한다.
참고
https://umanking.github.io/2021/07/13/mysql-cte/
https://dev.mysql.com/doc/refman/8.0/en/with.html
https://jjon.tistory.com/entry/MySQL-80-신기능-CTECommon-Table-Expression-활용
