계층 쿼리와 Top-N - 재귀 CTE 와 CONNECT BY 비교
이 장에서 배우는 것
온라인 서점은 도서를 대분류·중분류·소분류로 나누어 관리한다. 이런 분류 체계는 부모 카테고리를 가리키는 자기 참조(self-reference) 구조로 저장되는데, 일반적인 SELECT 로는 몇 단계까지 내려가야 할지 미리 알 수 없다. 이 장에서는 이런 계층 구조를 한 번에 풀어내는 재귀 쿼리와, 그룹마다 상위 몇 건만 골라내는 Top-N 기법을 다룬다.
- 재귀 공통 테이블 표현식(recursive CTE)으로 자기 참조 테이블의 트리를 끝까지 펼치는 방법을 익힌다.
- Oracle의 CONNECT BY와 LEVEL이 재귀 CTE의 어느 부분에 대응하는지 비교한다.
- 순환 참조가 섞인 데이터에서 쿼리가 끝나지 않는 상황을 막는 방법을 안다.
- ROWNUM, FETCH FIRST, LIMIT, ROW_NUMBER의 차이를 구분해 전체 Top-N과 그룹별 Top-N을 정확히 뽑는다.
문제 상황
서점 관리자는 도서 카테고리를 국내도서 > 소설 > 한국소설처럼 3단계로 운영한다. 화면 왼쪽에 트리 메뉴를 그리려면 카테고리 테이블 하나로 전체 단계를 한 번에 읽어야 하는데, 단계 수가 고정되어 있지 않아 JOIN 을 여러 번 이어 붙이는 방식은 카테고리 구조가 바뀔 때마다 쿼리를 고쳐야 한다. 한편 마케팅팀은 "카테고리마다 판매량 상위 2권만 골라 배너에 노출해 달라"고 요청한다. 이 두 요구는 각각 재귀 구조 전개와 그룹별 순위 추출이라는, 서로 다르지만 자주 함께 등장하는 문제다.
이 장에서는 다음 두 테이블을 사용한다. category 는 parent_id 로 자기 자신을 가리키는 자기 참조 테이블이고, book 은 각 도서가 속한 category_id 와 판매 수량 sold_qty 를 가진다.
| category_id | category_name | parent_id |
|---|---|---|
| 1 | 국내도서 | NULL |
| 2 | 소설 | 1 |
| 5 | IT전문서 | 1 |
| 6 | 데이터베이스 | 5 |
| book_id | title | category_id | sold_qty |
|---|---|---|---|
| 101 | SQL 자격시험 대비서 | 6 | 120 |
| 102 | 데이터 모델링 실무 | 6 | 95 |
| 106 | 달빛 아래 서점 | 3 | 200 |
재귀 CTE로 계층 풀어내기
재귀 CTE는 두 부분으로 이루어진다. 처음 한 번만 실행되는 앵커 멤버(anchor member)와, 그 결과를 이어받아 반복 실행되는 재귀 멤버(recursive member)다. 둘은 UNION ALL 로 연결한다. 앵커 멤버는 루트 행, 즉 parent_id 가 NULL 인 행을 고른다. 재귀 멤버는 이전 단계에서 만들어진 결과(ct)와 category 테이블을 parent_id = category_id 조건으로 조인해 한 단계 아래 자식을 찾는다. 이 과정은 더 이상 자식이 없을 때까지 자동으로 반복된다.
MySQL 8 공식 문서는 이 구문을 Common Table Expressions 항목에서 설명한다. WITH RECURSIVE 는 표준 SQL 구문이며 PostgreSQL, SQL Server 에서도 같은 방식으로 동작한다.
Oracle CONNECT BY와 LEVEL
Oracle 은 같은 문제를 CONNECT BY 절로 푼다. START WITH 는 앵커 멤버, CONNECT BY PRIOR 는 재귀 멤버 역할을 한다. PRIOR 가 붙은 쪽이 "이전 단계에서 만들어진 행"을 가리키므로, PRIOR category_id = parent_id 는 "이전 단계의 category_id 가 지금 행의 parent_id 와 같다"는 뜻이 되어 부모에서 자식으로 내려간다. 깊이는 별도 컬럼을 계산할 필요 없이 의사컬럼(pseudo column) LEVEL 이 자동으로 채워준다. 자세한 구문은 Oracle SQL Language Reference의 CONNECT BY Condition 항목에 나온다.
| 구분 | MySQL 8 (WITH RECURSIVE) | Oracle (CONNECT BY) | 비고 |
|---|---|---|---|
| 시작 조건 | 앵커 멤버의 WHERE parent_id IS NULL | START WITH parent_id IS NULL | 둘 다 루트 행을 고정 |
| 재귀 연결 | UNION ALL 뒤 JOIN c.parent_id = ct.category_id | CONNECT BY PRIOR category_id = parent_id | PRIOR 위치가 방향을 결정 |
| 깊이 표현 | 재귀 멤버에서 직접 계산한 lvl 컬럼 | 내장 의사컬럼 LEVEL | Oracle은 컬럼 정의가 불필요 |
| 순환 방지 | 종료조건 또는 경로 검사 | CONNECT BY NOCYCLE | 다음 절에서 다룬다 |
순환 참조 방지
category 테이블은 논리적으로 트리 구조지만, 데이터 입력 실수로 자식의 parent_id 가 자기 조상 쪽을 다시 가리키면 순환(cycle)이 생긴다. 예를 들어 카테고리 6의 parent_id 가 실수로 7로, 7의 parent_id 가 다시 6으로 저장되면 재귀 멤버는 6 → 7 → 6 → 7 을 끝없이 반복하며 메모리를 소진한다. 표준 SQL 은 이런 상황을 막는 CYCLE 절을 정의하고 있지만 구현체마다 지원 범위가 다르므로, 실무에서는 다음 두 가지 방법을 함께 쓴다.
첫째, 재귀 멤버에 깊이 상한을 둔다. WHERE ct.lvl < 10 처럼 현실적으로 나올 수 없는 깊이를 조건으로 넣으면 데이터가 잘못돼도 쿼리는 반드시 멈춘다. 둘째, 지금까지의 경로에 같은 category_id 가 이미 나왔는지 확인해 그 지점에서 재귀를 끊는다. Oracle 은 이 두 번째 방식을 CONNECT BY NOCYCLE 로 언어 차원에서 지원한다.
ROWNUM·FETCH FIRST와 그룹별 Top-N
전체 Top-N 과 그룹별 Top-N 은 다른 문제다. 전체 Top-N 은 ORDER BY 로 정렬한 결과에서 앞의 몇 건만 자르면 되지만, DBMS 마다 자르는 구문이 다르다. Oracle 의 ROWNUM 은 정렬이 끝나기 전에 각 행에 번호를 매기므로, ORDER BY 없이 ROWNUM <= 3 을 걸면 "정렬 전 임의의 3건"이 나온다. 정렬된 결과에 ROWNUM 을 적용하려면 정렬을 먼저 끝낸 서브쿼리를 감싸야 한다. FETCH FIRST n ROWS ONLY(Oracle 12c 이상, PostgreSQL, SQL Server)는 ORDER BY 뒤에 붙어 정렬이 끝난 결과에 적용되므로 이런 함정이 없다. MySQL 은 FETCH FIRST 표준 구문 대신 LIMIT n 을 쓰며, 역시 ORDER BY 이후에 적용된다.
그룹별 Top-N, 즉 "카테고리마다 상위 2건"은 LIMIT 이나 FETCH FIRST 로는 표현할 수 없다. 이 구문들은 결과 전체를 자를 뿐 그룹 단위로 자르지 못하기 때문이다. 이때는 앞 장에서 다룬 윈도 함수 중 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 를 서브쿼리로 감싸고 바깥에서 순번을 필터링한다.
| 구분 | 정렬 후 적용 여부 | 지원 DBMS | 특징 |
|---|---|---|---|
| ROWNUM | 아니오(정렬 전에 부여) | Oracle | 서브쿼리로 먼저 정렬해야 안전 |
| FETCH FIRST n ROWS ONLY | 예 | Oracle 12c+, SQL Server, PostgreSQL | ORDER BY 바로 뒤에 붙는 표준 구문 |
| LIMIT n | 예 | MySQL, PostgreSQL | ORDER BY 결과에 그대로 적용 |
| ROW_NUMBER() PARTITION BY | 예 | 표준 SQL 전반 | 그룹별 Top-N에 필수 |
완성 코드
-- 카테고리와 도서 테이블 준비
CREATE TABLE category (
category_id INT PRIMARY KEY,
category_name VARCHAR(30) NOT NULL,
parent_id INT NULL,
FOREIGN KEY (parent_id) REFERENCES category(category_id)
);
CREATE TABLE book (
book_id INT PRIMARY KEY,
title VARCHAR(50) NOT NULL,
category_id INT NOT NULL,
price INT NOT NULL,
sold_qty INT NOT NULL,
FOREIGN KEY (category_id) REFERENCES category(category_id)
);
INSERT INTO category (category_id, category_name, parent_id) VALUES
(1, '국내도서', NULL),
(2, '소설', 1),
(3, '한국소설', 2),
(4, '외국소설', 2),
(5, 'IT전문서', 1),
(6, '데이터베이스', 5),
(7, '프로그래밍언어', 5);
INSERT INTO book (book_id, title, category_id, price, sold_qty) VALUES
(101, 'SQL 자격시험 대비서', 6, 28000, 120),
(102, '데이터 모델링 실무', 6, 32000, 95),
(103, '인덱스 튜닝 가이드', 6, 27000, 60),
(104, '파이썬 기초', 7, 21000, 150),
(105, '자바 완전정복', 7, 25000, 80),
(106, '달빛 아래 서점', 3, 14000, 200),
(107, '골목의 밤', 3, 13500, 90),
(108, '먼 바다의 노래', 4, 15000, 70);
-- 1) 재귀 CTE로 카테고리 전체 계층을 경로와 함께 펼친다
WITH RECURSIVE category_tree AS (
SELECT
category_id,
category_name,
parent_id,
1 AS lvl,
CAST(category_name AS CHAR(200)) AS path,
LPAD(category_id, 3, '0') AS sort_key
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT
c.category_id,
c.category_name,
c.parent_id,
ct.lvl + 1,
CONCAT(ct.path, ' > ', c.category_name),
CONCAT(ct.sort_key, '-', LPAD(c.category_id, 3, '0'))
FROM category c
JOIN category_tree ct ON c.parent_id = ct.category_id
WHERE ct.lvl < 10
)
SELECT category_id, lvl, path
FROM category_tree
ORDER BY sort_key;
-- 2) 카테고리별 판매량 상위 2권만 뽑는다
SELECT category_id, title, sold_qty, rnk
FROM (
SELECT
category_id,
title,
sold_qty,
ROW_NUMBER() OVER (
PARTITION BY category_id
ORDER BY sold_qty DESC, book_id
) AS rnk
FROM book
) ranked
WHERE rnk <= 2
ORDER BY category_id, rnk;
줄별 해설
category 테이블은 parent_id 로 자기 자신(category_id)을 참조하는 외래키를 건다. 루트 카테고리는 parent_id 가 NULL 이라 이 제약을 위반하지 않는다.
재귀 CTE 의 앵커 멤버는 CAST(category_name AS CHAR(200)) 로 path 컬럼의 길이를 넉넉하게 지정한다. 이 캐스팅을 빼면 MySQL 은 앵커 멤버의 category_name(VARCHAR(30)) 길이로 path 컬럼 타입을 고정해 버려, 계층이 깊어질수록 CONCAT 결과가 30자를 넘는 순간 뒷부분이 잘리는 경고가 발생한다.
sort_key 는 category_id 를 3자리로 0 채움(LPAD)한 뒤 부모의 sort_key 뒤에 이어 붙인 문자열이다. 부모 경로가 자식 경로의 접두어가 되므로 ORDER BY sort_key 하나로 트리 순서대로 출력된다. 한글 카테고리명으로 정렬하면 문자 정렬 기준(collation)에 따라 순서가 흔들릴 수 있어, sort_key 로 정렬 결과를 고정했다.
WHERE ct.lvl < 10 은 실제로는 3단계까지만 있는 데이터에서 크게 의미가 없어 보이지만, 데이터 입력 실수로 순환이 생겨도 재귀가 10단계에서 강제로 멈추도록 만드는 안전장치다.
두 번째 쿼리는 ROW_NUMBER() 를 카테고리별로 나눠(PARTITION BY category_id) 판매량 내림차순으로 번호를 매긴 뒤, 바깥 쿼리에서 rnk <= 2 조건으로 카테고리마다 2건만 남긴다. ORDER BY 에 book_id 를 더해 판매량이 같을 때도 순번이 흔들리지 않게 했다.
실행 결과
SELECT category_id, lvl, path FROM category_tree ORDER BY sort_key;
category_id | lvl | path
------------+-----+-------------------------------------------
1 | 1 | 국내도서
2 | 2 | 국내도서 > 소설
3 | 3 | 국내도서 > 소설 > 한국소설
4 | 3 | 국내도서 > 소설 > 외국소설
5 | 2 | 국내도서 > IT전문서
6 | 3 | 국내도서 > IT전문서 > 데이터베이스
7 | 3 | 국내도서 > IT전문서 > 프로그래밍언어
SELECT category_id, title, sold_qty, rnk FROM (...) ranked WHERE rnk <= 2 ORDER BY category_id, rnk;
category_id | title | sold_qty | rnk
------------+----------------------+----------+----
3 | 달빛 아래 서점 | 200 | 1
3 | 골목의 밤 | 90 | 2
4 | 먼 바다의 노래 | 70 | 1
6 | SQL 자격시험 대비서 | 120 | 1
6 | 데이터 모델링 실무 | 95 | 2
7 | 파이썬 기초 | 150 | 1
7 | 자바 완전정복 | 80 | 2
실무에서 자주 틀리는 것
재귀 CTE의 문자열 길이를 앵커 멤버에 맞추지 않는다
-- 잘못된 코드: path 타입이 category_name(VARCHAR(30))에 묶인다
WITH RECURSIVE category_tree AS (
SELECT category_id, category_name AS path, parent_id, 1 AS lvl
FROM category WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, CONCAT(ct.path, ' > ', c.category_name), c.parent_id, ct.lvl + 1
FROM category c JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;
-- 고친 코드: 앵커 멤버에서 미리 넉넉한 길이로 캐스팅
WITH RECURSIVE category_tree AS (
SELECT category_id, CAST(category_name AS CHAR(200)) AS path, parent_id, 1 AS lvl
FROM category WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, CONCAT(ct.path, ' > ', c.category_name), c.parent_id, ct.lvl + 1
FROM category c JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;
ROWNUM을 정렬 전에 걸어 엉뚱한 행을 뽑는다(Oracle)
-- 잘못된 코드: ROWNUM이 정렬 전에 매겨진다
SELECT * FROM book WHERE ROWNUM <= 3 ORDER BY sold_qty DESC;
-- 고친 코드: 정렬을 먼저 끝낸 결과에 ROWNUM을 적용
SELECT * FROM (
SELECT * FROM book ORDER BY sold_qty DESC
) WHERE ROWNUM <= 3;
재귀 CTE에 종료 조건을 두지 않는다
-- 잘못된 코드: parent_id가 순환되면 끝나지 않는다
WITH RECURSIVE category_tree AS (
SELECT category_id, parent_id, 1 AS lvl FROM category WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, c.parent_id, ct.lvl + 1
FROM category c JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;
-- 고친 코드: 현실적으로 나올 수 없는 깊이에서 강제로 멈춘다
WITH RECURSIVE category_tree AS (
SELECT category_id, parent_id, 1 AS lvl FROM category WHERE parent_id IS NULL
UNION ALL
SELECT c.category_id, c.parent_id, ct.lvl + 1
FROM category c JOIN category_tree ct ON c.parent_id = ct.category_id
WHERE ct.lvl < 10
)
SELECT * FROM category_tree;
RANK()로 그룹별 Top-N을 뽑아 개수가 어긋난다
-- 잘못된 코드: 동점이 있으면 카테고리당 2건보다 많이 나올 수 있다
SELECT category_id, title, sold_qty, rnk
FROM (
SELECT category_id, title, sold_qty,
RANK() OVER (PARTITION BY category_id ORDER BY sold_qty DESC) AS rnk
FROM book
) t
WHERE rnk <= 2;
-- 고친 코드: 순번이 겹치지 않는 ROW_NUMBER를 쓰고 동점은 book_id로 정리한다
SELECT category_id, title, sold_qty, rnk
FROM (
SELECT category_id, title, sold_qty,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sold_qty DESC, book_id) AS rnk
FROM book
) t
WHERE rnk <= 2;
한눈에 보기
| 구분 | MySQL 8 | Oracle | 핵심 주의점 |
|---|---|---|---|
| 계층 전개 | WITH RECURSIVE ... UNION ALL | START WITH ... CONNECT BY PRIOR | 방향과 종료조건을 함께 확인 |
| 깊이 표현 | 재귀 멤버에서 lvl 계산 | LEVEL 의사컬럼 | Oracle은 자동 제공 |
| 순환 방지 | WHERE lvl < n 또는 경로 검사 | CONNECT BY NOCYCLE | 데이터 품질을 무조건 믿지 않는다 |
| Top-N | LIMIT n 또는 ROW_NUMBER() | FETCH FIRST n / ROWNUM 서브쿼리 | 그룹별은 반드시 윈도 함수 |
연습 문제
- 재귀 CTE를 이용해 category_id = 5(IT전문서) 이하의 모든 하위 카테고리만 lvl 과 함께 조회하는 쿼리를 작성하라.
- book 테이블에서 카테고리별 판매량 1위 도서만, 동점이면 book_id 가 작은 쪽을 선택해 정확히 한 권씩 조회하는 쿼리를 작성하라.
- Oracle 환경이라 가정하고, category 테이블을 CONNECT BY 와 LEVEL 로 조회하되 순환 데이터가 있어도 안전하도록 NOCYCLE 을 포함한 쿼리를 작성하라.
- book 테이블 전체에서 판매량 상위 3권을 ROWNUM 방식과 FETCH FIRST 방식으로 각각 작성하고, 두 방식이 언제 다른 결과를 낼 수 있는지 한 문장으로 설명하라.
정답과 해설
1번 정답
WITH RECURSIVE sub_category AS (
SELECT category_id, category_name, parent_id, 1 AS lvl
FROM category
WHERE category_id = 5
UNION ALL
SELECT c.category_id, c.category_name, c.parent_id, sc.lvl + 1
FROM category c
JOIN sub_category sc ON c.parent_id = sc.category_id
)
SELECT category_id, category_name, lvl
FROM sub_category
ORDER BY lvl, category_id;
앵커 멤버를 parent_id IS NULL 대신 category_id = 5 로 바꾸면 그 지점부터 아래로만 펼쳐진다. IT전문서(lvl 1), 데이터베이스와 프로그래밍언어(lvl 2)가 조회된다.
2번 정답
SELECT category_id, title, sold_qty
FROM (
SELECT category_id, title, sold_qty,
ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sold_qty DESC, book_id) AS rnk
FROM book
) t
WHERE rnk = 1
ORDER BY category_id;
ROW_NUMBER 는 동점이어도 순번이 겹치지 않으므로 rnk = 1 조건만으로 카테고리당 정확히 한 행이 남는다. RANK() 를 썼다면 동점 시 두 행 이상이 rnk = 1 이 될 수 있어 이 조건에 맞지 않는다.
3번 정답
SELECT category_id, category_name, LEVEL AS lvl
FROM category
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR category_id = parent_id
ORDER BY LEVEL;
PRIOR category_id = parent_id 는 이전 단계의 category_id 가 지금 행의 parent_id 와 같다는 뜻으로, 부모에서 자식 방향으로 내려간다. NOCYCLE 을 붙이면 데이터에 순환이 있어도 이미 방문한 노드를 다시 타지 않고 결과를 반환한다.
4번 정답
-- ROWNUM 방식
SELECT * FROM (
SELECT * FROM book ORDER BY sold_qty DESC
) WHERE ROWNUM <= 3;
-- FETCH FIRST 방식
SELECT * FROM book
ORDER BY sold_qty DESC
FETCH FIRST 3 ROWS ONLY;
두 방식은 정렬을 먼저 끝낸 뒤 자른다는 점에서 결과가 같다. 다만 ROWNUM 을 서브쿼리 없이 ORDER BY 와 나란히 쓰면(1번 실무 함정 참고) 정렬 전 행에 번호가 매겨져 다른 결과가 나올 수 있다는 점이 두 구문의 실제 차이다.