D-3: 계층형 질의 — 자기 자신을 따라가며 대댓글 트리를 파고들기

안녕하세요, 홍순구 튜터입니다. 지난 시간엔 쿼리 속에 또 다른 쿼리를 중첩하는 서브쿼리를 배웠어요. 단일행 서브쿼리부터 IN·EXISTS, 상관 서브쿼리, 그리고 맨 끝에서 WITH 절까지 다뤘죠. 그때 마지막에 한 가지를 슬쩍 흘려 뒀어요. 자기 자신을 참조해 계층 구조를 파고드는 재귀 CTE 말이에요. 오늘 그 약속을 지킬 시간이에요.

우리 post_comment 표를 떠올려 보세요. 댓글에 답글이 달리고, 그 답글에 또 답글이 달려요. 인스타그램 댓글 창을 열면 "첫 게시물 축하해요!" 아래에 "감사합니다", 그 아래에 또 "별말씀을요" 가 들여쓰기되어 주렁주렁 이어지죠. 이렇게 부모-자식으로 가지를 뻗는 구조를 트리(tree)라고 불러요. 오늘은 이 트리를 위에서 아래로 한 단계씩 따라 내려가는 계층형 질의(hierarchical query)를 배웁니다.

지난 시간 서브쿼리가 "쿼리 속의 쿼리" 였다면, 오늘 계층형은 "자기 자신을 따라가는 쿼리" 예요. 같은 표 안에서 내가 누구의 자식인지, 그 부모는 또 누구의 자식인지를 끝까지 거슬러 가는 거죠. Oracle 은 START WITH 와 CONNECT BY 라는 전용 문법으로 이걸 풀어내요.

 오늘의 여정
   ① 계층 구조란 — 자기 자신을 가리키는 표 (자기참조)
   ② START WITH / CONNECT BY — 루트에서 자식으로 펼치기
   ③ LEVEL — 지금 몇 단계 깊이인가
   ④ SYS_CONNECT_BY_PATH — 루트부터 여기까지의 경로
   ⑤ CONNECT_BY_ISLEAF — 답글이 안 달린 끝 댓글 찾기
   ⑥ ORDER SIBLINGS BY — 트리를 지키며 형제끼리 정렬
   ⑦ 역방향 전개 + NOCYCLE — 자식에서 부모로 거슬러 오르기
   ⑧ 재귀 CTE — 같은 트리를 표준 문법으로

💡 오늘 수업의 핵심 — "같은 표가 자기 자신을 부모로 가리키는 트리를, START WITH 로 뿌리를 잡고 CONNECT BY 로 한 단계씩 따라 내려가 대댓글이 몇 단계까지 이어지는지 끝까지 펼친다"

🎯 학습 목표

• 자기참조 테이블이 만드는 계층(트리) 구조를 이해하고, 평범한 조인으로는 깊이를 알 수 없는 이유를 안다. (SQLD 2과목 'SQL 활용 — 계층형 질의', ★빈출)
• START WITH·CONNECT BY PRIOR·LEVEL·SYS_CONNECT_BY_PATH·CONNECT_BY_ISLEAF·ORDER SIBLINGS BY 로 트리를 순방향·역방향으로 전개한다.
• 순환 데이터를 막는 NOCYCLE 을 이해하고, 같은 계층 질의를 재귀 WITH(CTE)로도 표현해 Oracle 문법과 ANSI 표준을 견준다.

Step 1: "계층 구조란 무엇인가 — 자기 자신을 가리키는 표"

먼저 우리가 다룰 데이터가 어떻게 생겼는지 눈으로 봐요. C-5 에서 댓글 표 post_comment 를 만들 때, 컬럼 하나를 미리 넣어 뒀어요. 바로 parent_comment_id 예요. 이게 오늘의 주인공이에요.

-- sql/queries/D3_hierarchical.sql 의 데이터로 확인
SELECT comment_id, parent_comment_id, content
FROM   post_comment
WHERE  post_id = 1
ORDER  BY comment_id;

1번 게시물에 달린 댓글 7개가 나와요. 그중 parent_comment_id 를 잘 보세요.

 post_comment 표 — parent_comment_id 가 같은 표의 comment_id 를 가리킨다
   comment_id │ parent_comment_id │ content
   ───────────┼───────────────────┼──────────────────────
       1      │      (NULL)       │ 첫 게시물 축하해요!
       2      │      (NULL)       │ 시작이 좋네요 ㅎㅎ
       3      │        1          │ 감사합니다 민지님
       4      │      (NULL)       │ 잘 보고 가요
      16      │        1          │ 민지님 말 맞아요 ㅎㅎ
      17      │        3          │ 별말씀을요 ㅎㅎ
      18      │       17          │ 앞으로 자주 올게요!

parent_comment_id 가 비어 있는(NULL) 댓글은 누구에게도 매달리지 않은 최상위 댓글이에요. 1·2·4번이 그렇죠. 반대로 값이 있는 댓글은 그 번호의 댓글에 달린 답글, 곧 대댓글이에요. 3번은 1번에, 17번은 3번에, 18번은 17번에 달렸죠. 여기서 중요한 점은, 가리키는 대상이 다른 표가 아니라 바로 자기 자신, post_comment 표의 comment_id 라는 거예요. 한 표가 자기 자신을 부모로 참조하는 구조, 이걸 자기참조(self-reference)라고 불러요.

지난 시간이 아니라 그 전, C-5 에서 우리는 follow 표로 회원이 회원을 따르는 관계를 같은 member 표끼리 이어 봤죠. 셀프 조인이라고 불렀고요. 댓글이 댓글을 가리키는 것도 똑같이 자기 자신을 참조하는 구조예요. 다만 그때는 한 다리만 건넜다면, 오늘은 그 다리를 몇 번이고 연달아 건너야 해요.

자, 이 댓글들을 부모-자식 관계대로 그려 보면 이런 트리가 나와요.

 게시물 1 의 대댓글 트리
   [1단계] 첫 게시물 축하해요!  (이민지)
      └─ [2단계] 감사합니다 민지님  (김재훈)
            └─ [3단계] 별말씀을요 ㅎㅎ  (이민지)
                  └─ [4단계] 앞으로 자주 올게요!  (김재훈)
      └─ [2단계] 민지님 말 맞아요 ㅎㅎ  (강지수)
   [1단계] 시작이 좋네요 ㅎㅎ  (강지수)
   [1단계] 잘 보고 가요  (김유나)

"첫 게시물 축하해요!"(1번) 아래로 답글이 두 갈래 달렸어요. 하나는 "감사합니다"(3번)인데, 그 아래로 "별말씀을요"(17번), 또 그 아래로 "앞으로 자주 올게요!"(18번)까지 4단계나 깊게 이어져요. 다른 하나는 "민지님 말 맞아요"(16번)로 거기서 멈추고요. "시작이 좋네요"(2번)와 "잘 보고 가요"(4번)는 답글이 없는 최상위 댓글이에요.

그럼 이 트리를 SQL 로 어떻게 따라갈까요? 우선 C-5 에서 배운 셀프 조인을 떠올려 봐요. 댓글마다 자기 부모 댓글을 옆에 붙여 보는 거예요.

SELECT c.comment_id AS cid, c.parent_comment_id AS pid,
       c.content AS 댓글, p.content AS 부모댓글
FROM   post_comment c
LEFT   JOIN post_comment p ON c.parent_comment_id = p.comment_id
WHERE  c.post_id = 1
ORDER  BY c.comment_id;

같은 표에 별칭을 c(자식)와 p(부모) 둘로 붙여, c 의 parent_comment_id 를 p 의 comment_id 에 맞댔어요. 결과를 보면 18번 "앞으로 자주 올게요!" 옆에 부모인 17번 "별말씀을요" 가 붙어요. 한 단계는 잘 따라왔죠.

그런데 여기서 막혀요. 18번의 부모는 17번이라는 건 알아냈는데, 그럼 17번의 부모는요? 또 그 위는요? 한 단계 더 올라가려면 조인을 한 번 더 해야 하고, 또 한 단계면 또 한 번 더 해야 해요. 우리 데이터는 4단계지만, 만약 댓글이 10단계, 20단계까지 이어진다면요? 조인을 몇 번 적어야 할지 미리 알 수가 없어요. 트리의 깊이는 데이터에 따라 달라지는데, 조인 횟수는 쿼리를 적는 순간 고정돼 버리니까요. 이게 평범한 조인의 한계예요.

💡 한 줄 정리 — 한 표의 컬럼이 같은 표의 기본키를 가리키면(자기참조) 부모-자식 트리가 생기고, 깊이를 모르는 트리는 조인 횟수를 고정해야 하는 평범한 조인으로는 끝까지 따라갈 수 없다.

Step 2: "뿌리에서 자식으로 — START WITH 와 CONNECT BY PRIOR"

이제 트리를 SQL 한 방으로 펼쳐 볼게요. Oracle 의 계층형 질의는 두 부분으로 이뤄져요. 하나는 어디서 시작할지를 정하는 START WITH, 다른 하나는 거기서 어떻게 이어 내려갈지를 정하는 CONNECT BY 예요.

-- sql/queries/D3_hierarchical.sql
SELECT comment_id, parent_comment_id, content
FROM   post_comment
WHERE  post_id = 1
START  WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id;

START WITH parent_comment_id IS NULL 은 "부모가 없는 댓글, 곧 최상위 댓글에서 출발해라" 는 뜻이에요. 우리 데이터에선 1·2·4번이 뿌리(루트)죠. CONNECT BY PRIOR comment_id = parent_comment_id 는 "지금 행의 parent_comment_id 가, 이미 거쳐 온 부모 행의 comment_id 와 같으면 자식으로 이어라" 는 규칙이에요. 결과는 이렇게 나와요.

 comment_id │ parent │ LEVEL │ content
 ───────────┼────────┼───────┼──────────────────────
      1     │ (NULL) │   1   │ 첫 게시물 축하해요!
      3     │   1    │   2   │ 감사합니다 민지님
     17     │   3    │   3   │ 별말씀을요 ㅎㅎ
     18     │  17    │   4   │ 앞으로 자주 올게요!
     16     │   1    │   2   │ 민지님 말 맞아요 ㅎㅎ
      2     │ (NULL) │   1   │ 시작이 좋네요 ㅎㅎ
      4     │ (NULL) │   1   │ 잘 보고 가요

순서를 잘 보세요. 1번 다음에 곧장 1번의 자식 3번이 나오고, 그 자식 17번, 또 그 자식 18번이 끝까지 내려가요. 한 가지가 바닥에 닿으면 그제야 같은 부모의 다른 가지(16번)로 돌아오죠. 이렇게 한 줄기를 끝까지 판 다음 옆 가지로 넘어가는 걸 깊이 우선(depth-first) 전개라고 해요. 데이터베이스가 부모를 만나면 그 자식을, 또 그 자식을, 자식이 없을 때까지 따라 내려간 거예요.

여기서 오늘의 가장 중요한 단어가 PRIOR 예요. PRIOR 는 "이미 알고 있는 쪽", 곧 앞 단계에서 거쳐 온 부모 행을 가리키는 표시예요.

 CONNECT BY PRIOR comment_id = parent_comment_id
                  └─ 부모 쪽 ─┘   └─ 자식(지금 행) 쪽 ─┘
   "부모의 comment_id 와 지금 행의 parent_comment_id 가 같다"
   → 부모에서 자식으로 한 단계 내려간다 (순방향)

PRIOR 가 comment_id 앞에 붙었다는 건, comment_id 쪽이 부모라는 뜻이에요. 그 부모의 comment_id 를, 지금 보고 있는 행의 parent_comment_id 와 맞대니까, "이 댓글의 부모가 누구인가" 를 따라 자식으로 내려가게 되죠. 잠시 뒤 Step 7 에서 이 PRIOR 의 위치를 반대로 옮기면, 똑같은 데이터를 자식에서 부모 쪽으로 거꾸로 거슬러 오르게 돼요. PRIOR 가 어느 컬럼에 붙느냐가 전개 방향을 통째로 뒤집는 거예요.

⚠️ ★ 빈출 함정 — CONNECT BY PRIOR 자식키 = 부모키 처럼 PRIOR 를 반대쪽에 붙이면 방향이 거꾸로 돼요. 시험에서 "이 쿼리는 위로 가는가 아래로 가는가" 를 묻는 단골 포인트예요. PRIOR 가 붙은 컬럼이 "출발점에서 이미 알고 있는 쪽" 이라고 기억하세요.

💡 한 줄 정리 — START WITH 로 전개의 시작점(루트)을 정하고, CONNECT BY PRIOR 부모키 = 자식키 로 부모-자식 연결 규칙을 줘서, 데이터베이스가 트리를 깊이 우선으로 끝까지 펼쳐 준다.

Step 3: "지금 몇 층인가 — LEVEL 의사컬럼"

트리를 펼치긴 했는데, 결과만 봐선 어느 댓글이 몇 단계 깊이인지 한눈에 안 들어와요. 다행히 계층형 질의는 우리가 만들지 않아도 자동으로 생기는 의사컬럼(pseudo-column) 하나를 줘요. 바로 LEVEL 이에요. 루트가 1, 그 자식이 2, 또 그 자식이 3… 이렇게 깊이를 숫자로 알려줘요.

LEVEL 을 LPAD 와 함께 쓰면 깊이만큼 왼쪽에 공백을 줘서, 진짜 댓글 창처럼 들여쓰기된 트리를 그릴 수 있어요. C-2 에서 배운 LPAD 가 여기서 빛을 발하죠.

-- sql/queries/D3_hierarchical.sql
SELECT LEVEL AS lv,
       LPAD(' ', (LEVEL - 1) * 4) || content AS tree
FROM   post_comment
WHERE  post_id = 1
START  WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY comment_id;

LPAD(' ', (LEVEL-1)*4) 는 "(깊이-1)×4 칸만큼 공백을 채워라" 예요. 루트(LEVEL 1)는 0칸, 2단계는 4칸, 3단계는 8칸씩 밀려요. 그 뒤에 || 로 댓글 내용을 이어 붙이면 이런 그림이 나와요.

 LEVEL │ tree
 ──────┼──────────────────────────────
   1   │ 첫 게시물 축하해요!
   2   │     감사합니다 민지님
   3   │         별말씀을요 ㅎㅎ
   4   │             앞으로 자주 올게요!
   2   │     민지님 말 맞아요 ㅎㅎ
   1   │ 시작이 좋네요 ㅎㅎ
   1   │ 잘 보고 가요

들여쓰기만 봐도 트리 구조가 그대로 보이죠. 가장 깊은 "앞으로 자주 올게요!" 가 LEVEL 4 예요. 1번 게시물의 대댓글이 4단계까지 이어진다는 걸 숫자로 확인한 거죠. 댓글 창 UI 가 답글을 점점 안쪽으로 들여쓰는 것도 바로 이 깊이 값을 쓰는 거예요.

⚠️ 주의 — LEVEL 은 계층형 질의(CONNECT BY 가 있는 쿼리) 안에서만 존재하는 의사컬럼이에요. CONNECT BY 없이 그냥 SELECT LEVEL FROM post_comment 라고 쓰면 의미가 없어요. 계층 전개가 만들어 주는 "지금 몇 층" 정보라고 기억하세요.

Step 4: "여기까지 어떻게 왔나 — SYS_CONNECT_BY_PATH 와 CONNECT_BY_ROOT"

깊이는 알았는데, 이번엔 경로가 궁금해요. "앞으로 자주 올게요!"(18번)는 어떤 댓글들을 거쳐 여기까지 왔을까요? 1번 → 3번 → 17번 → 18번 순으로 내려왔죠. 이 경로를 문자열로 한 번에 뽑아 주는 함수가 SYS_CONNECT_BY_PATH 예요.

-- sql/queries/D3_hierarchical.sql
SELECT comment_id,
       LEVEL AS lv,
       CONNECT_BY_ROOT content        AS root_content,
       SYS_CONNECT_BY_PATH(comment_id, '/') AS path
FROM   post_comment
WHERE  post_id = 1
START  WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY comment_id;

SYS_CONNECT_BY_PATH(comment_id, '/') 는 루트부터 지금 행까지 거쳐 온 comment_id 들을 / 로 이어 한 문자열로 만들어 줘요. CONNECT_BY_ROOT content 는 지금 행이 속한 트리의 뿌리 댓글 내용을 알려주고요.

 comment_id │ LEVEL │ root_content        │ path
 ───────────┼───────┼─────────────────────┼──────────────
      1     │   1   │ 첫 게시물 축하해요! │ /1
      3     │   2   │ 첫 게시물 축하해요! │ /1/3
     17     │   3   │ 첫 게시물 축하해요! │ /1/3/17
     18     │   4   │ 첫 게시물 축하해요! │ /1/3/17/18
     16     │   2   │ 첫 게시물 축하해요! │ /1/16
      2     │   1   │ 시작이 좋네요 ㅎㅎ  │ /2
      4     │   1   │ 잘 보고 가요        │ /4

18번의 경로가 /1/3/17/18 로 깔끔하게 찍혔죠. 댓글 하나만 보고도 "이건 1번 댓글 스레드의 3번, 17번을 거쳐 달린 답글" 이라는 족보가 한 줄에 담겨요. CONNECT_BY_ROOT 는 1·3·17·18번이 모두 "첫 게시물 축하해요!" 라는 같은 뿌리에서 갈라져 나왔다는 걸 보여주고요. 게시판에서 "이 답글이 어느 원댓글에 속하는지" 를 묶을 때 자주 쓰는 조합이에요.

Step 5: "답글이 안 달린 끝 댓글 — CONNECT_BY_ISLEAF"

트리에서 더 이상 자식이 없는 맨 끝 노드를 잎(leaf)이라고 불러요. 댓글로 치면 답글이 하나도 안 달린 댓글이죠. "이 댓글이 대화의 끝인가?" 를 알려주는 의사컬럼이 CONNECT_BY_ISLEAF 예요. 잎이면 1, 자식이 있으면 0 을 줘요.

-- sql/queries/D3_hierarchical.sql
SELECT comment_id, CONNECT_BY_ISLEAF AS is_leaf, content
FROM   post_comment
WHERE  post_id = 1
START  WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY comment_id;

 comment_id │ is_leaf │ content
 ───────────┼─────────┼──────────────────────
      1     │    0    │ 첫 게시물 축하해요!
      3     │    0    │ 감사합니다 민지님
     17     │    0    │ 별말씀을요 ㅎㅎ
     18     │    1    │ 앞으로 자주 올게요!
     16     │    1    │ 민지님 말 맞아요 ㅎㅎ
      2     │    1    │ 시작이 좋네요 ㅎㅎ
      4     │    1    │ 잘 보고 가요

is_leaf 가 1인 댓글은 18·16·2·4번, 네 개예요. 18번은 가장 깊은 답글이라 당연히 잎이고, 16번도 자기 밑에 답글이 없으니 잎이죠. 2·4번은 아예 답글이 한 번도 안 달린 최상위 댓글이라 그 자체로 잎이고요. 반대로 1·3·17번은 밑에 답글을 거느리고 있어 0 이에요. 알림 기능에서 "대화의 가장 마지막 답글에만 새 알림을 보내자" 같은 걸 만들 때, 잎 노드만 골라내는 식으로 써요.

💡 한 줄 정리 — CONNECT_BY_ISLEAF 는 자식이 없는 끝 노드(잎)면 1, 아니면 0 을 주는 의사컬럼으로, 답글이 더 안 달린 마지막 댓글을 가려낼 때 쓴다.

Step 6: "트리를 지키며 정렬 — ORDER SIBLINGS BY"

여기서 함정을 하나 짚어요. 트리를 펼친 결과를 작성일 순으로 정렬하고 싶다고, 평소처럼 ORDER BY created_at 을 붙이면 어떻게 될까요?

-- 평범한 ORDER BY — 트리가 깨진다
SELECT LEVEL AS lv, LPAD(' ', (LEVEL - 1) * 4) || content AS tree
FROM   post_comment
WHERE  post_id = 1
START  WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER BY created_at;

 LEVEL │ tree                         (ORDER BY created_at)
 ──────┼──────────────────────────────
   1   │ 첫 게시물 축하해요!            ← 1번 (06-01)
   1   │ 시작이 좋네요 ㅎㅎ             ← 2번 (06-01)
   2   │     감사합니다 민지님          ← 3번 (06-02)
   1   │ 잘 보고 가요                  ← 4번 (06-02)
   3   │         별말씀을요 ㅎㅎ        ← 17번 (06-03)
   2   │     민지님 말 맞아요 ㅎㅎ      ← 16번 (06-04)
   4   │             앞으로 자주 올게요! ← 18번 (06-05)

들여쓰기는 남아 있지만 부모-자식이 뿔뿔이 흩어졌어요. 17번(별말씀을요)이 자기 부모인 3번에서 멀리 떨어졌고, 자식 18번은 맨 아래로 가버렸죠. ORDER BY 는 트리고 뭐고 따지지 않고 결과 전체를 날짜 순으로 줄 세워 버리거든요. 애써 만든 트리 구조가 무너진 거예요.

이럴 때 쓰라고 만든 게 ORDER SIBLINGS BY 예요. 이건 트리 구조는 그대로 둔 채, 같은 부모를 둔 형제들끼리만 정렬해요.

-- sql/queries/D3_hierarchical.sql — 트리를 지키며 형제만 정렬
SELECT LEVEL AS lv, LPAD(' ', (LEVEL - 1) * 4) || content AS tree
FROM   post_comment
WHERE  post_id = 1
START  WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY created_at;

 LEVEL │ tree                         (ORDER SIBLINGS BY created_at)
 ──────┼──────────────────────────────
   1   │ 첫 게시물 축하해요!            ← 06-01
   2   │     감사합니다 민지님          ← 06-02
   3   │         별말씀을요 ㅎㅎ        ← 06-03
   4   │             앞으로 자주 올게요! ← 06-05
   2   │     민지님 말 맞아요 ㅎㅎ      ← 06-04
   1   │ 시작이 좋네요 ㅎㅎ             ← 06-01
   1   │ 잘 보고 가요                  ← 06-02

트리는 멀쩡히 살아 있으면서, 1번 밑의 두 답글(3번 06-02, 16번 06-04)이 작성일 순서대로 놓였어요. 부모와 자식의 관계는 흩뜨리지 않고, 같은 줄에 선 형제들 사이에서만 순서를 매기는 거죠. 댓글 창에서 트리 모양은 유지하면서 답글을 "오래된 순" 이나 "최신 순" 으로 보여줄 때 딱 이거예요.

⚠️ ★ 빈출 함정 — 계층형 질의에서는 일반 ORDER BY 가 트리를 깨뜨려요. 트리 구조를 유지하며 정렬하려면 반드시 ORDER SIBLINGS BY 를 써야 해요. 시험에서 두 결과를 나란히 주고 "어느 쪽이 트리를 보존하는가" 를 자주 물어요.

Step 7: "거꾸로 거슬러 오르기 — 역방향 전개와 NOCYCLE"

지금까지는 루트에서 자식으로 내려갔어요. 이번엔 반대로 가 봐요. 가장 깊은 답글 18번에서 시작해, 이 답글이 어떤 댓글들에 매달려 여기까지 왔는지 부모를 향해 거슬러 오르는 거예요. Step 2 에서 예고한 대로, PRIOR 의 위치만 바꾸면 돼요.

-- sql/queries/D3_hierarchical.sql — 잎에서 루트로 거슬러 오르기
SELECT LEVEL AS up_lv, comment_id, parent_comment_id, content
FROM   post_comment
START  WITH comment_id = 18
CONNECT BY PRIOR parent_comment_id = comment_id;

두 군데가 바뀌었어요. START WITH 가 루트(parent IS NULL)가 아니라 잎인 18번이 됐고, CONNECT BY 의 PRIOR 가 이번엔 parent_comment_id 앞에 붙었어요. "지금 거쳐 온 행의 parent_comment_id 가, 다음 행의 comment_id 와 같은 걸 찾아라" — 곧 내 부모를 찾아 올라가라는 뜻이죠.

 up_lv │ comment_id │ parent │ content
 ──────┼────────────┼────────┼──────────────────────
   1   │     18     │   17   │ 앞으로 자주 올게요!
   2   │     17     │    3   │ 별말씀을요 ㅎㅎ
   3   │      3     │    1   │ 감사합니다 민지님
   4   │      1     │ (NULL) │ 첫 게시물 축하해요!

18번에서 출발해 17번, 3번, 1번까지 한 줄로 거슬러 올랐어요. 부모가 없는 1번에 닿자 멈췄고요. 여기서 LEVEL 은 "출발점에서 몇 단계 올라왔나" 를 세요. 어떤 답글 하나를 두고 "이게 어느 원댓글에서 시작된 대화인지" 추적할 때 쓰는 패턴이에요.

그런데 자기 자신을 따라가는 쿼리에는 한 가지 위험이 숨어 있어요. 만약 데이터가 잘못돼서 A 의 부모가 B 인데 B 의 부모가 다시 A 라면요? 트리가 아니라 고리가 돼버려요. CONNECT BY 는 부모를 따라가다 자기가 이미 지나온 댓글을 또 만나면 영원히 뱅뱅 돌게 되죠. 이때 Oracle 은 무한히 돌지 않고 "사용자 데이터에 CONNECT BY 순환이 있다(ORA-01436)" 는 에러를 내며 멈춰요.

이 순환을 안전하게 건너뛰게 해주는 장치가 NOCYCLE 이에요.

 CONNECT BY NOCYCLE PRIOR comment_id = parent_comment_id
            └─ 순환을 만나면 그 지점에서 멈추고 계속 진행

CONNECT BY 뒤에 NOCYCLE 을 붙이면, 이미 지나온 행을 다시 만나는 순간 그 가지를 더 파고들지 않고 멈춰요. 에러로 죽는 대신 결과를 돌려주죠. 그리고 CONNECT_BY_ISCYCLE 라는 의사컬럼을 같이 쓰면, 바로 그 "고리가 생긴 지점" 의 행을 1 로 표시해 줘서 어디서 순환이 났는지 짚을 수 있어요. 우리 댓글 데이터엔 순환을 일부러 두지 않았지만, 사용자가 자유롭게 부모를 바꿀 수 있는 시스템에선 실수로 순환이 생길 수 있어서 NOCYCLE 이 안전장치로 쓰여요.

⚠️ 주의 — 순방향이든 역방향이든, 자기참조 데이터에 고리가 생기면 CONNECT BY 는 ORA-01436 으로 멈춰요. 데이터 정합성을 100% 보장하기 어렵다면 NOCYCLE 을 붙여 두는 게 안전해요.

Step 8: "같은 트리를 표준 문법으로 — 재귀 CTE"

마지막이에요. 사실 지금까지 한 트리 전개는, 지난 시간 끝에서 살짝 흘려 둔 재귀 CTE 로도 똑같이 할 수 있어요. CONNECT BY 는 Oracle 만의 문법이지만, 재귀 WITH 는 ANSI 표준이라 다른 데이터베이스에서도 통해요. SQLD 가 "이 둘은 같은 일을 한다" 는 걸 즐겨 묻는 대목이죠.

D-2 에서 배운 WITH 가, 자기 자신을 참조하면서 한 단계씩 불어나는 형태로 변하면 재귀가 돼요.

-- sql/queries/D3_hierarchical.sql — CONNECT BY 와 같은 트리를 재귀 WITH 로
WITH comment_tree (comment_id, parent_comment_id, content, lv) AS (
    SELECT comment_id, parent_comment_id, content, 1 AS lv      -- ① 시작점(루트)
    FROM   post_comment
    WHERE  post_id = 1 AND parent_comment_id IS NULL
    UNION ALL
    SELECT c.comment_id, c.parent_comment_id, c.content, ct.lv + 1   -- ② 자식 한 단계
    FROM   post_comment c
    JOIN   comment_tree ct ON c.parent_comment_id = ct.comment_id
    WHERE  c.post_id = 1
)
SEARCH DEPTH FIRST BY comment_id SET ord
SELECT lv, LPAD(' ', (lv - 1) * 4) || content AS tree
FROM   comment_tree
ORDER  BY ord;

구조가 두 부분이에요. UNION ALL 위쪽 ①은 시작점, 곧 루트 댓글이에요. CONNECT BY 의 START WITH 에 해당하죠. 아래쪽 ②가 재귀의 핵심인데, 방금 만든 comment_tree 를 자기 자신과 다시 조인해 자식을 한 단계 붙여요. ct.lv + 1 로 깊이도 하나씩 키우고요. 이 ②가 더 붙일 자식이 없을 때까지 저절로 반복돼요. SEARCH DEPTH FIRST BY comment_id 는 CONNECT BY 처럼 깊이 우선 순서로 맞춰 주는 부분이에요.

결과는 Step 3 의 CONNECT BY 와 완전히 같은 트리예요.

 LEVEL │ tree
 ──────┼──────────────────────────────
   1   │ 첫 게시물 축하해요!
   2   │     감사합니다 민지님
   3   │         별말씀을요 ㅎㅎ
   4   │             앞으로 자주 올게요!
   2   │     민지님 말 맞아요 ㅎㅎ
   1   │ 시작이 좋네요 ㅎㅎ
   1   │ 잘 보고 가요

같은 트리를 두 가지 문법으로 그려 본 거예요. 둘을 짝지어 정리하면 이래요.

 하는 일            │ Oracle (CONNECT BY)        │ ANSI 표준 (재귀 WITH)
 ───────────────────┼────────────────────────────┼────────────────────────
 시작점 정하기      │ START WITH ...             │ UNION ALL 위쪽(앵커) 쿼리
 자식으로 잇기      │ CONNECT BY PRIOR ...       │ UNION ALL 아래쪽 + 자기참조 조인
 깊이               │ LEVEL                      │ 직접 lv + 1 로 키움
 깊이 우선 순서     │ (기본) + ORDER SIBLINGS BY │ SEARCH DEPTH FIRST BY ...

실무에선 Oracle 만 쓴다면 CONNECT BY 가 짧고 읽기 좋아요. 반대로 MySQL·PostgreSQL 같은 다른 데이터베이스로 옮길 가능성이 있다면, 표준인 재귀 WITH 로 짜 두는 게 이식성이 좋고요. 둘이 같은 결과를 낸다는 점, 그리고 CONNECT BY 가 Oracle 고유라는 점은 시험에서 거의 빠지지 않아요.

💡 한 줄 정리 — 계층 전개는 Oracle 의 CONNECT BY 와 ANSI 표준 재귀 WITH(앵커 쿼리 + 자기참조 UNION ALL) 두 가지로 같은 트리를 만들 수 있으며, 방언 구분은 SQLD 빈출 포인트다.

마무리

오늘은 같은 표가 자기 자신을 부모로 가리키는 트리를, 한 쿼리로 끝까지 펼치는 계층형 질의를 배웠어요. 평범한 조인으로는 깊이를 모르는 트리를 따라갈 수 없다는 문제에서 출발해, START WITH 와 CONNECT BY 로 대댓글을 4단계까지 펼쳐 봤죠.

오늘 배운 핵심 정리

• 💡 자기참조와 전개 — parent_comment_id 가 같은 표의 comment_id 를 가리키면 트리가 생기고, START WITH(시작점)와 CONNECT BY PRIOR(연결 규칙)로 깊이 우선 전개한다.
• 💡 트리를 읽는 의사컬럼 — LEVEL(깊이), SYS_CONNECT_BY_PATH(경로), CONNECT_BY_ROOT(뿌리), CONNECT_BY_ISLEAF(끝 댓글)로 트리의 위치 정보를 꺼낸다.
• 💡 정렬·방향·안전·표준 — ORDER SIBLINGS BY 는 트리를 지키며 형제만 정렬하고, PRIOR 위치를 바꾸면 역방향, NOCYCLE 은 순환을 막으며, 같은 일을 재귀 WITH 로도 표준 문법으로 표현한다.

다음 시간 예고

오늘 우리는 트리를 세로로(부모-자식 깊이) 파고들었어요. 다음 시간엔 데이터를 가로로 줄 세워 순위를 매기는 윈도우 함수를 배워요. "좋아요를 가장 많이 받은 게시물 1·2·3등" 처럼 등수를 매기는 RANK·ROW_NUMBER, 그리고 "앞 시점보다 팔로워가 몇 명 늘었나" 처럼 앞뒤 행을 비교하는 LAG 같은 도구죠. 집계가 여러 행을 한 값으로 뭉쳤다면, 윈도우 함수는 각 행을 그대로 두면서 그 옆에 순위나 누적값을 붙여 줘요. 분석 쿼리의 꽃이라 불리는, 또 하나의 SQLD 최빈출 주제예요.

과제

오늘 배운 계층형 질의를 직접 써 보는 과제예요. 결과가 몇 줄 나올지, 어떤 트리 모양일지 먼저 머릿속으로 그려 본 뒤 실행해 맞는지 확인해 보세요.

[기초] START WITH · CONNECT BY · LEVEL 기본 전개

post_comment 표로 다음을 해 보세요. (가) 12번 게시물에 달린 댓글들을 START WITH·CONNECT BY 로 전개해 보세요. 최상위 댓글에서 시작해 자식으로 내려가면 돼요. (나) 거기에 LEVEL 과 LPAD 를 더해 깊이만큼 들여쓴 트리로 출력하고, 12번 게시물 트리의 가장 깊은 단계가 몇인지 적어 보세요. (다) ORDER SIBLINGS BY 를 붙여 형제 댓글을 comment_id 순으로 정렬해 보세요.

[응용] 경로 · 뿌리 · 끝 댓글

1번 게시물의 댓글 트리로 다음을 해 보세요. (가) 각 댓글까지의 경로를 SYS_CONNECT_BY_PATH(comment_id, '/') 로 뽑아, 18번 댓글의 경로가 어떻게 찍히는지 확인해 보세요. (나) CONNECT_BY_ISLEAF 로 답글이 더 안 달린 끝 댓글(잎)만 골라 보세요. 몇 개가 나오나요? (다) CONNECT_BY_ROOT 로 18번 댓글이 어느 최상위 댓글 스레드에서 갈라져 나왔는지 그 내용을 함께 조회해 보세요.

[심화] 역방향 전개와 재귀 CTE

1번 게시물의 댓글 트리로 다음을 해 보세요. (가) 가장 깊은 답글인 18번에서 출발해 부모를 따라 루트까지 거슬러 올라가 보세요. PRIOR 의 위치를 어디에 둬야 하는지 생각하면서요. (나) Step 8 의 재귀 WITH 를 참고해, 1번 게시물 트리를 재귀 CTE 로도 펼쳐 CONNECT BY 결과와 같은지 비교해 보세요. (다) 만약 사용자가 댓글의 부모를 자유롭게 바꿀 수 있는 시스템이라면, 이 역방향 쿼리에 왜 NOCYCLE 을 붙여 두는 게 안전한지 한 줄로 설명해 보세요.

생각해볼 주제

1. 트리를 DB 에서 펼칠까, 애플리케이션에서 펼칠까

대댓글 트리는 오늘처럼 CONNECT BY 로 데이터베이스가 한 번에 펼쳐 줄 수도 있고, 애플리케이션이 부모를 한 단계씩 반복 조회하며 직접 쌓을 수도 있어요. 둘은 같은 트리를 얻지만 데이터베이스를 오가는 횟수와 코드가 사는 위치가 달라요. 어떤 상황에서 계층형 질의 한 방이 유리하고, 어떤 상황에서 애플리케이션 쪽 처리가 나은지 생각해 보세요.

2. CONNECT BY 와 재귀 WITH, 무엇을 고를까

같은 계층 전개를 Oracle 고유의 CONNECT BY 로도, ANSI 표준 재귀 WITH 로도 짤 수 있어요. 한쪽은 짧고 읽기 좋지만 Oracle 에서만 돌고, 다른 쪽은 길지만 다른 데이터베이스로도 옮겨 가죠. 지금 만드는 서비스가 앞으로 다른 데이터베이스로 이사할 가능성, 팀이 익숙한 문법, 쿼리의 가독성을 놓고 무엇을 기준으로 고를지 따져 보세요.

3. 끝없이 깊어지는 트리를 어떻게 다룰까

댓글의 대댓글, 그 대댓글의 답글… 트리는 이론적으로 한없이 깊어질 수 있어요. 그런데 깊이가 깊어질수록 전개 비용도 커지고, 화면에 들여쓰기로 보여주기도 어려워지죠. 실제 서비스가 대댓글 깊이를 일부러 2~3단계로 제한하거나, 깊은 답글을 평평하게 펴서 보여주는 선택을 하는 이유가 무엇일지 생각해 보세요.