[MySQL] WITH - 임시 테이블 생성

CTE

Common Table Expression

단일 문의 범위 내에 존재하고 나중에 해당 문 내에서 여러 번 참조할 수 있는 명명된 임시 결과 집합

 

하나의 쿼리문이 끝날 때까지 유지되는 일회성 테이블로, 서브 쿼리로 사용되는 파생 테이블과 비슷한 개념으로 사용된다. 복잡한 쿼리에서 코드의 가독성과 재사용성을 위해 파생 테이블 대신 사용할 수 있다. 또한 SELECT, UPDATE, DELETE 문은 CTE를 참조할 수 있다.

생성에 권한이 필요하고 사전에 정의해야 하는 VIEW와는 다르게, CTE는 권한이 필요하지 않다.

MySQL 8.0.1부터 도입되었다.

 

 

WITH

CTE는 WITH 절을 이용하여 정의할 수 있으면 여러 테이블을 만들 때에는 ,로 구분하여 작성한다.

WITH [RECURSIVE]
	cte_name [(col_name [, col_name] ...)] AS (subquery)]
	[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...
        
-- 예시
WITH cte AS (
	SELECT * 
	FROM country 
	WHERE continent='Europe'
)

 

 

WITH RECURSIVE

CTE는 자신 또는 다른 CTE를 참조할 수 있다. 그 중 자체 참조 CTE는 RECURSIVE 키워드를 사용하여 정의할 수 있다.

 

용법

WITH RECURSIVE cte_name AS (
    initial_query
    UNION ALL
    recursive_query
)

- initial_query  : 기본 결과 집합을 형성한다. anchor member라고 한다.

- recursive_query : CTE를 참조한다. recursive member라고 한다.

 

실행 순서

1. 멤버를 anchor와 recursive로 분리한다.
2. 기본 결과 집합을 구성하기 위해 anchor member를 실행한다. 그리고 다음 반복에 기본 결과 집합(R0)을 사용한다.
3. 이전 결과 집합(Ri)을 입력으로 하여 recursive member를 실행하고 출력으로 결과 집합(Ri+1)을 만든다.
4. recursive member가 빈 결과 집합을 반환할 때까지, 즉 종료 조건을 만족할 때까지 3번을 반복한다.
5. 모든 결과 집합을 UNION ALL로 결합한다.

 

예시

WITH RECURSIVE cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)

 

생성한 cte 테이블을 조회한 결과는 아래와 같다.

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

 

 

참고 문서

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

https://www.percona.com/blog/2020/02/10/introduction-to-mysql-8-0-common-table-expressions-part-1/

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

https://www.mysqltutorial.org/mysql-recursive-cte/