CTE 사용법 알아보기

CTE 는 Common Table Expression 의 약자이다. 한국말로는 공통 테이블 표현식 이라 한다.

CTE는 WITH절을 통해 정의하며, Oracle, SQL Server, PostgreSQL, MySQL, MariaDB 등 거의 모든 주요 데이터베이스에서 지원된다. (문법은 조금씩 차이가 있다.) MySQL 에서는 8.0 버전 부터 지원하고 있다.

CTE

CTE의 장점은 가독성이 좋다는 점과, 여러 쿼리에서 동일한 CTE를 참조할 수 있다는 것이다.

대부분의 경우, CTE는 파생 테이블(Derived Table / Subquery)로 대체할 수 있으며 그 반대도 가능하다. 이전에 작성한 파생 테이블 쿼리도 동일하게 CTE 형식으로 나타낼 수 있으며, 대부분의 경우에도 성능이 동일하다.

[Note] 누군가 어떤 조언을 해주더라도 반드시 현재 사용 중인 데이터베이스에서 정확히 확인해야 한다. 최적화는 가정에 기반한다. 따라서, 쿼리가 잘 작동하는지 여부를 증명하는 유일한 방법은 다시 실행 계획을 확인하는 것이다.

CTE를 사용하여 하나의 쿼리에서 여러 CTE를 선언할 수 있고, 이전에 정의한 CTE를 또 다른 CTE에서 참조하여 사용할 수 있다.

CTE에서 정의된 쿼리들은 현재 SQL 실행 문(statement)의 범위에서만 유효하며, 메모리 내에서 일시적으로만 존재한다.

같은 쿼리를 Derived Table 와 CTE 로 비교

댓글이 가장 많은 상위 세 개의 게시물의 제목 조회하는 SQL 예시이다.

Derived Table

SELECT
  p.title AS post_title,
  pc.comment_count AS comment_count
FROM post p
JOIN (
  SELECT
    post_id,
    COUNT(*) as comment_count
  FROM post_comment
  GROUP BY post_id
  ORDER BY comment_count DESC
  LIMIT 3
) pc ON p.id = pc.post_id
ORDER BY comment_count DESC

CTE

WITH TopCommentedPosts AS (
  SELECT
    post_id,
    COUNT(*) AS comment_count
  FROM post_comment
  GROUP BY post_id
  ORDER BY comment_count DESC
  LIMIT 3
)
SELECT
  p.title AS post_title,
  tcp.comment_count AS comment_count
FROM post p
JOIN TopCommentedPosts tcp ON p.id = tcp.post_id
ORDER BY tcp.comment_count DESC;

Recursive CTE (재귀적 CTE)

Recursive CTE 를 사용하면 이론적으로 모든 계산 가능한 문제를 해결할 수 있다. (튜링 완전)

절차적 언어(Java 등)에서 반복문을 이용하는 것과 유사하며, 이를 통해 일반적인 데이터 처리와 계층 구조 데이터를 쉽게 해결할 수 있다.

예시: 연속된 숫자의 합 구하기

WITH RECURSIVE
  consecutive_number_sum (i, consecutive_sum)
AS (
  SELECT 0, 0
  UNION ALL
  SELECT i + 1, (i + 1) + consecutive_sum
  FROM consecutive_number_sum
  WHERE i < :n
)

n 이 5일 때 다음과 같은 결과가 나온다.

구조 이해하기

• Anchor Query: 첫번째 SELECT 문을 의미. 재귀의 시작값(예: i = 0, sum = 0)을 정의.
• UNION ALL: 각 단계마다 새로운 레코드를 결과 집합에 추가
• recursive member: 두번째 SELECT 문을 의미한다. 앞선 결과(i, sum 값)를 기반으로 값을 누적하며 반복 실행(i가 5에 도달할 때까지 등)
  • 재귀 멤버의 WHERE 절로 반복 범위를 제어할 수 있다.

예시: Hierarchical Query - 계층구조 데이터 조회

댓글처럼 트리 형태로 계층이 구성될 때 (ex. 자식 댓글이 부모 댓글을 참조, 대댓글) CTE를 사용하여 데이터를 조회할 수 있다.

WITH RECURSIVE comment_tree(
      id, root_id, post_id, parent_id, review, created_on)
AS (
  SELECT
      id, id, post_id, parent_id, review, created_on
  FROM post_comment
  WHERE post_id = :postId AND parent_id IS NULL
  UNION ALL
  SELECT pc.id, ct.root_id, pc.post_id, pc.parent_id,
      pc.review, pc.created_on
  FROM post_comment pc
  INNER JOIN comment_tree ct ON pc.parent_id = ct.id
)
SELECT id, parent_id, root_id, review, created_on
FROM comment_tree

마무리

CTE(Common Table Expression)는 복잡한 SQL 쿼리를 구조적으로 나누어 가독성과 재사용성을 높여주는 유용한 기능입니다. 일반적인 서브쿼리와 유사하게 사용할 수 있을 뿐만 아니라, 재귀 CTE를 활용하면 계층 구조나 반복적인 계산과 같은 복잡한 데이터 처리도 SQL만으로 간결하게 해결할 수 있습니다.