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

1) 공통 테이블 표현식 (Common Table Expressrions, CTE)

• 공통 테이블 표현식(CTE)이란?

  • CTE ⇒ 임시로 만든 테이블
  • 한 번의 쿼리 안에서만 유효
  • 나중에 반복해서 재사용 가능

• CTE 사용법

  • CTE를 지정하려면 하나 이상의 하위구문을 포함 하는 WITH 절 사용
  • 각 하위구문은 결과 집합을 생성하는 서브쿼리 제공 → 이 서브쿼리에 이름 연결

  # WITH절에서 cte1, cte2 정의
  # WITH절 뒤에 나오는 최상위 SELECT문에서 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;
  

• 명시적 JOIN, 묵시적 JOIN

• 구문 설명

  WITH [RECURSIVE]
  	cte_name [(col_name [, col_name] ...)] AS (subquery)
  	[, cte_name [(col_name [, col_name]...)] AS (subquery)] ...
  

  • WITH : CTE를 정의할 때 항상 사용되는 키워드
  • RECURSIVE : 재귀적인 CTE를 정의할 때만 추가로 사용되는 키워드
  • cte_name : CTE의 이름, 나중에 메인 쿼리에서 이 이름으로 데이터 참조
  • col_name : (선택) CTE의 열 이름을 정의
  • AS (subquert) : CTE에서 사용할 데이터를 생성하는 서브쿼리 작성

• CTE의 열 이름 결정 방식

  • CTE 이름 뒤에 괄호로 묶인 이름 목록이 있는 경우

    → 해당 목록에 지정된 이름들이 열 이름이 됨

    WITH cte (col1, col2) AS
    (
      SELECT 1, 2
      UNION ALL
      SELECT 3, 4
    )
    SELECT col1, col2 FROM cte;
    

  • CTE 이름 뒤에 이름 목록이 없는 경우 → AS (subquery) 부분의 첫 번째 SELECT문의 SELECT목록에서 열 이름을 가져옴

    WITH cte AS
    (
      SELECT 1 AS col1, 2 AS col2
      UNION ALL
      SELECT 3, 4
    )
    SELECT col1, col2 FROM cte;
    

2) Recursive CTE

• Recursive CTE

  • 자신의 이름을 참조하는 서브쿼리를 포함하는 CTE

    # 예시
    WITH RECURSIVE cte(n) AS
    (
    	SELECT 1
    	UNION ALL
    	SELECT n + 1
    	FROM cte
    	WHERE n < 5
    )
    SELECT * FROM cte;
    

    • 결과
• Recursive CTE의 구조

  • WITH절

    • WITH RECURSIVE 로 시작

  • 서브쿼리 부분

    • 초기 SELECT
      • 초기 결과 집합 반환
      • CTE 이름을 참조하지 않음
    • 재귀 SELECT
      • 추가 행 반환
      • FROM 절에서 CTE 이름 참조
      • 재귀는 새로운 행이 생성되지 않을 때 종료

    SELECT ...
    UNION ALL
    SELECT ...
    

    <aside>

    ✅ 참고

    • UNION DISTICT 를 사용하면 중복 행 제거 </aside>