계층형 쿼리 CONNECT BY

계층형 쿼리

계층적인 데이터 구조에서 부모-자식 관계를 가진 데이터를 검색하거나 조작하는 쿼리

주로 조직도, 트리 구조 데이터 등을 처리할 때 사용됩니다.

문법

SELECT [표현식들]
FROM [테이블명]
START WITH [시작 조건]
CONNECT BY [재귀 조건];
SQL
복사

기본 구성 요소

구문	설명
SELECT [표현식들]	조회할 열을 지정합니다. 계층 구조를 표현하기 위한 열과 추가적인 열이 포함될 수 있습니다.
FROM [테이블명]	계층형 쿼리를 수행할 대상 테이블을 지정합니다.
START WITH [시작 조건]	계층 구조의 시작점을 정의합니다. 지정된 조건을 만족하는 행이 시작점이 됩니다.
CONNECT BY [재귀 조건]	재귀적인 관계를 정의합니다. 지정된 조건을 만족하는 행이 이전 행과 연결되어 계층 구조를 형성합니다.
PRIOR	CONNECT BY 절과 함께 사용되며, 이전 행을 의미하는 키워드이다. PRIOR 자식 = 부모 (정방향) : 부모 자식 PRIOR 부모 = 자식 (역방향) : 자식 부모

CONNECT BY 키워드

키워드 및 함수	설명
CONNECT BY	계층적 쿼리를 정의하기 위한 키워드로, 부모-자식 간의 관계를 지정합니다.
LEVEL	현재 행의 계층 레벨을 나타내는 의사 열입니다.
CONNECT_BY_ROOT	계층 구조에서 최상위 부모를 나타내는 의사 열입니다.
CONNECT_BY_ISLEAF	현재 행이 리프(말단) 노드인지 여부를 판별하는 의사 열입니다.
SYS_CONNECT_BY_PATH	계층적 쿼리에서 행의 경로를 나타내는 함수입니다.
NOCYCLE	순환(cycle)을 방지하기 위한 옵션입니다.
CONNECT_BY_ISCYCLE	현재 행이 순환 구조에 있는지 여부를 판별하는 의사 열입니다.
ORDER BY SIBLINGS BY 컬럼	같은 LEVEL 노드 사이에서 정렬을 수행합니다.

계층형 쿼리로 조직도를 출력하는 예시 코드

SELECT LEVEL
    , employee_id
    , first_name
    , last_name
    , job_id
    , manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
SQL
복사

•

START WITH manager_id IS NULL: 최상위 부서의 직원들을 선택합니다. 여기서는 최상위 직원이 매니저가 없는 직원들입니다.

•

CONNECT BY PRIOR employee_id = manager_id: 각 행을 이전 행과 연결합니다. 이전 행(PRIOR)의 employee_id가 현재 행의 manager_id와 같은 경우, 계층적으로 연결됩니다.

•

LEVEL: 각 행의 계층 레벨을 나타냅니다.

◦

1 : 대표

◦

2 : 부서장 (상사)

◦

3 : 직원 (부하)

CONNECT BY를 활용한 대댓글 구조 예시

대댓글 테이블 생성 및 샘플 데이터

-- DDL
CREATE TABLE board (
  no NUMBER NOT NULL,
  title varchar2(100) NOT NULL,
  writer varchar2(100) NOT NULL,
  content varchar2(1000),
  reg_date DATE DEFAULT sysdate NOT NULL,
  upd_date DATE  DEFAULT sysdate NOT NULL,
  views NUMBER  DEFAULT 0 NOT NULL,
  PRIMARY KEY (no)
);


CREATE TABLE comments (
  comment_id NUMBER NOT NULL,
  board_no NUMBER NOT NULL,
  parent_comment_id NUMBER,
  writer VARCHAR2(100) NOT NULL,
  content VARCHAR2(1000) NOT NULL,
  reg_date DATE DEFAULT sysdate NOT NULL,
  PRIMARY KEY (comment_id),
  FOREIGN KEY (parent_comment_id) REFERENCES comments(comment_id)
);

-- 샘플 데이터
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (1, 1, NULL, '사용자1', '첫 번째 댓글입니다.');
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (2, 1, 1, '사용자2', '첫 번째 댓글에 대한 대댓글입니다.');
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (3, 1, 1, '사용자3', '첫 번째 댓글에 대한 또 다른 대댓글입니다.');
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (4, 1, 2, '사용자4', '대댓글에 대한 대댓글입니다.');
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (5, 1, NULL, '사용자5', '두 번째 댓글입니다.');
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (6, 1, 5, '사용자6', '두 번째 댓글에 대한 대댓글입니다.');
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (7, 1, NULL, '사용자7', '세 번째 댓글입니다.');
INSERT INTO comments (comment_id, board_no, parent_comment_id, writer, content) VALUES (8, 1, 3, '사용자8', '첫 번째 댓글의 대댓글에 대한 답글입니다.');
SQL
복사

계층형 쿼리로 대댓글 구조 조회

SELECT LEVEL,
       LPAD(' ', (LEVEL-1)*4) || writer AS writer,
       comment_id,
       parent_comment_id,
       content,
       reg_date
FROM comments
WHERE board_no = 1
START WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY comment_id;
SQL
복사

•

START WITH parent_comment_id IS NULL: 최상위 댓글(부모 댓글이 없는 댓글)을 시작점으로 지정합니다.

•

CONNECT BY PRIOR comment_id = parent_comment_id: 이전 행의 comment_id가 현재 행의 parent_comment_id와 같은 경우 계층적으로 연결됩니다.

•

LPAD(' ', (LEVEL-1)*4): 계층 레벨에 따라 들여쓰기를 적용하여 대댓글 구조를 시각적으로 표현합니다.

•

ORDER SIBLINGS BY comment_id: 같은 레벨의 댓글들을 comment_id 순서로 정렬합니다.

결과 예시

LEVEL	WRITER	COMMENT_ID	PARENT_COMMENT_ID	CONTENT
1	사용자1	1	NULL	첫 번째 댓글입니다.
2	사용자2	2	1	첫 번째 댓글에 대한 대댓글입니다.
3	사용자4	4	2	대댓글에 대한 대댓글입니다.
2	사용자3	3	1	첫 번째 댓글에 대한 또 다른 대댓글입니다.
3	사용자8	8	3	첫 번째 댓글의 대댓글에 대한 답글입니다.
1	사용자5	5	NULL	두 번째 댓글입니다.
2	사용자6	6	5	두 번째 댓글에 대한 대댓글입니다.
1	사용자7	7	NULL	세 번째 댓글입니다.

경로 표시하기 (SYS_CONNECT_BY_PATH 사용)

SELECT LEVEL,
       comment_id,
       parent_comment_id,
       writer,
       content,
       SYS_CONNECT_BY_PATH(writer, ' → ') AS path
FROM comments
WHERE board_no = 1
START WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY comment_id;
SQL
복사

•

SYS_CONNECT_BY_PATH(writer, ' → '): 계층 구조의 경로를 표시합니다. 최상위 댓글부터 현재 댓글까지의 작성자를 화살표로 연결하여 보여줍니다.

루트 댓글 정보 가져오기 (CONNECT_BY_ROOT 사용)

SELECT LEVEL,
       comment_id,
       parent_comment_id,
       writer,
       content,
       CONNECT_BY_ROOT comment_id AS root_comment_id,
       CONNECT_BY_ROOT writer AS root_writer
FROM comments
WHERE board_no = 1
START WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY comment_id;
SQL
복사

•

CONNECT_BY_ROOT comment_id: 해당 댓글이 속한 최상위 댓글의 ID를 반환합니다.

•

CONNECT_BY_ROOT writer: 해당 댓글이 속한 최상위 댓글의 작성자를 반환합니다.

말단 댓글 확인하기 (CONNECT_BY_ISLEAF 사용)

SELECT LEVEL,
       comment_id,
       parent_comment_id,
       writer,
       content,
       CONNECT_BY_ISLEAF AS is_leaf
FROM comments
WHERE board_no = 1
START WITH parent_comment_id IS NULL
CONNECT BY PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY comment_id;
SQL
복사

•

CONNECT_BY_ISLEAF: 현재 댓글이 말단 댓글(자식이 없는 댓글)인 경우 1을, 그렇지 않으면 0을 반환합니다.