[TIL] MySQL CTE (Common Table Expression)

🧩 What is CTE?

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;

• Common Table Expression
• 하나의 쿼리문 범위 내에서만 존재하며 여러 번 참조될 수 있는 이름이 지정된 일회성 테이블
• VIEW 와는 다르게 생성 권한 불필요
• Non-Recursive CTE, Recursive CTE 두 종류 존재
• 장점
  • 쿼리 내에서 여러번 참조 가능
  • 한 CTE가 다른 CTE 참조 가능

 

 

📌 Non-Recursive CTE (비재귀적 CTE)

• 단순한 WITH절, 서브쿼리 하나로 끝나는 경우

 

 

📌 Recursive CTE (재귀적 CTE)

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 1 AS p, -1 AS q
  UNION ALL
  SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
)
SELECT * FROM cte;


+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+

 

• 계층적, 트리 구조유용하게 사용됨
• 재귀 SELECT 구문 제한
  • 집계 함수 SUM( )
  • 윈도우 함수
  • GROUP BY, ORDER BY, DISTINCT
  • 재귀 SELECT 부분은 CTE를 해당 절에서만 한 번만 참조해야 함
  • CTE가 아닌 다른 테이블을 참조해 CTE와 조인하는 경우, CTE는 LEFT JOIN 되어야 함
• CTE 재귀 제한
  • 폭주하는 재귀적 CTE를 방지하기 위한 개발 기법, 실행 시간에 제한을 두어 강제 종료 가능
  • cte_max_recursion_depth 변수는 재귀 수준 수에 제한 적용
  • max_execution_time 명령문에 대해 시스템 변수는 실행 시간 초과를 적용
  • 최적화 힌트는 힌트가 나타나는 명령문 MAX_EXECUTION_TIME에 대해 쿼리별 실행시간 초과 적용