공통 테이블 식(CTE)

이름을 붙여 위에서 아래로

2.4 의 파생 테이블은 괄호 안에 조회를 통째로 넣는 방식이라, 중첩이 깊어지면 안쪽부터 읽어야 뜻이 잡힙니다. WITH 를 사용하면 같은 것을 위에서 아래로 풀어 적을 수 있습니다.

WITH 사람별글수 AS (
    SELECT U.num, U.nickname, COUNT(*) AS 글수
    FROM Board.POSTS AS P
        INNER JOIN Member.USERS AS U ON U.num = P.user_num
    GROUP BY U.num, U.nickname
)
SELECT AVG(글수) AS 평균, MAX(글수) AS 최다
FROM 사람별글수;

2.4 의 파생 테이블과 결과도 속도도 같습니다. 달라진 것은 읽는 차례뿐입니다. "사람별 글수를 이렇게 구하고, 그것으로 평균과 최다를 낸다" 로 위에서 아래로 읽힙니다.

WITH 는 바로 뒤에 오는 문장 하나에서만 사용할 수 있습니다. 이름을 붙였다고 테이블이 만들어지는 것이 아니라, 그 문장 안에서만 통하는 별명입니다.

같은 것을 두 번 참조하기

파생 테이블은 사용할 때마다 통째로 다시 적어야 했습니다. CTE 는 이름으로 여러 번 부를 수 있습니다.

WITH 사람별글수 AS (
    SELECT U.nickname, COUNT(*) AS 글수
    FROM Board.POSTS AS P
        INNER JOIN Member.USERS AS U ON U.num = P.user_num
    GROUP BY U.nickname
)
SELECT nickname, 글수
FROM 사람별글수
WHERE 글수 > (SELECT AVG(글수) FROM 사람별글수)
ORDER BY 글수 DESC;

이름을 두 번 적었을 뿐 안쪽 조회는 한 번만 정의했습니다. 파생 테이블로 같은 일을 하려면 같은 조회를 두 번 적어야 하고, 고칠 때 한쪽만 고치는 실수가 생깁니다.

다만 실행이 한 번으로 줄어드는 것은 아닙니다. 데이터베이스는 CTE 를 이름으로 펼쳐 넣을 뿐이라, 두 번 참조하면 두 번 셈할 수 있습니다. 결과를 실제로 한 번만 만들어 재사용하려면 임시 테이블이 필요합니다(4.1).

여러 개를 이어 붙이기

쉼표로 이으면 앞의 것을 뒤에서 사용할 수 있습니다.

WITH 게시판별 AS (
    SELECT board_num, COUNT(*) AS 글수
    FROM Board.POSTS GROUP BY board_num
),
이름붙이기 AS (
    SELECT B.name, G.글수
    FROM 게시판별 AS G
        INNER JOIN Board.BOARDS AS B ON B.num = G.board_num
)
SELECT * FROM 이름붙이기 ORDER BY 글수 DESC;

WITH 는 맨 앞에 한 번만 적고 뒤는 쉼표로 잇습니다. 두 번째부터 WITH 를 또 적으면 오류입니다.

자기 자신을 참조하기 — 재귀 CTE

CTE 가 파생 테이블로 대신할 수 없는 일이 하나 있습니다. 자기 자신을 참조해 계층을 따라 내려가는 것입니다.

1.2 에서 답글이 세 단으로 달린 덩어리를 보았습니다(6 → 352 → 456). 그 관계를 parent_num 만으로 따라가 봅니다.

WITH 계층 AS (
    -- 1) 출발점: 원글 하나
    SELECT num, parent_num, depth, title,
           CAST(title AS NVARCHAR(200)) AS 경로
    FROM Board.POSTS
    WHERE num = 6

    UNION ALL

    -- 2) 반복: 앞에서 찾은 것의 자식들
    SELECT C.num, C.parent_num, C.depth, C.title,
           CAST(P.경로 + N' > ' + C.title AS NVARCHAR(200))
    FROM Board.POSTS AS C
        INNER JOIN 계층 AS P ON P.num = C.parent_num
)
SELECT num, parent_num, depth, 경로 FROM 계층 ORDER BY depth;

어떻게 도는가

재귀 CTE 는 UNION ALL 을 사이에 두고 두 부분 으로 이루어집니다.

1. 고정 부분 — 자기를 참조하지 않습니다. 출발점입니다.
2. 재귀 부분 — CTE 이름을 참조합니다. 새로 찾은 행이 없을 때까지 되풀이됩니다.

위 문장이 실제로 도는 모습입니다.

경로 열이 회차마다 조금씩 길어진 것을 보십시오. 앞 회차의 값을 가져다 사용할 수 있다는 것이 재귀 CTE 의 핵심입니다.

CAST(… AS NVARCHAR(200)) 로 감싼 것은 두 부분의 형식을 맞추기 위해서입니다. 고정 부분의 title 은 nvarchar(200) 인데 재귀 부분에서 이어 붙이면 더 길어질 수 있어, 그대로 두면 형식이 다르다는 오류가 납니다.

끝없이 돌지 않도록

재귀는 잘못 적으면 멈추지 않습니다. SQL Server 는 100회에서 스스로 끊고 오류를 냅니다.

WITH 숫자 AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM 숫자 WHERE n < 200
)
SELECT COUNT(*) FROM 숫자;

이 제한이 여러분을 구합니다. 조건을 잘못 적어 끝없이 도는 문장이 서버를 붙들고 있는 대신 100회에서 끊깁니다.

실제로 더 깊이 들어가야 한다면 OPTION (MAXRECURSION n) 으로 늘립니다. 0 은 제한 없음입니다.

WITH 숫자 AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM 숫자 WHERE n < 200
)
SELECT COUNT(*) AS 개수 FROM 숫자 OPTION (MAXRECURSION 0);

MAXRECURSION 0 은 멈출 조건이 확실할 때만 사용하십시오. 위 문장은 n < 200 이 있어 안전하지만, 자료에 순환이 있으면(A 의 부모가 B, B 의 부모가 A) 영원히 돕니다.

실습 데이터를 만든 lab-setup.sql 도 이 문법을 사용합니다 — 답글 관계를 따라 내려가며 경로를 만들고, 그 경로로 줄을 세워 sort_no 를 매겼습니다. 1.7 에서 목록을 정렬하던 그 열입니다.

직접 해보기