계층형 질의와 셀프 조인

SQLD 2과목의 「계층형 질의와 셀프 조인」 항목을 정리합니다. 계층형 데이터의 뜻, 같은 테이블을 두 번 잇는 셀프 조인과 그 결과 행 수, 오라클 START WITH·CONNECT BY 문법, PRIOR 위치로 정해지는 순방향과 역방향, 시작 행에 따라 달라지는 결과, LEVEL·CONNECT_BY_ROOT·CONNECT_BY_ISLEAF·SYS_CONNECT_BY_PATH, WHERE 조건과 CONNECT BY 조건의 차이, ORDER SIBLINGS BY와 NOCYCLE, SQL Server의 재귀 CTE를 상품분류 예시로 살펴본 뒤 기출 유형 연습 문제 5개로 확인합니다.

앞 글 「윈도우 함수, 순위·집계·LAG/LEAD」에서 행마다 순위와 앞뒤 행의 값을 붙이는 방법을 정리했습니다. 이번에는 한 테이블 안에서 행끼리 위아래 관계를 가진 계층형 데이터를 조회합니다. 정해진 단계만큼 위를 찾을 때는 셀프 조인을, 깊이를 모르는 트리를 끝까지 펼칠 때는 계층형 질의를 씁니다. 예시 데이터는 앞 글들과 같은 쇼핑몰 테이블에 상품분류 테이블 두 개를 더했습니다. 연습 문제는 실제 시험과 같은 유형으로 새로 만든 문제입니다.

1. 계층형 데이터와 예시 테이블

한 테이블 안에서 어떤 행이 다른 행의 「위」가 되는 데이터를 계층형 데이터라고 합니다. 각 행이 자기 위 행의 키를 컬럼으로 가지고 있어서, 그 컬럼은 같은 테이블의 기본키를 참조하는 외래키(자기 참조 외래키)가 됩니다. 수험서는 사원과 관리자(EMP 테이블의 MGR 컬럼) 예를 자주 쓰고, 이 글에서는 쇼핑몰의 상품분류로 봅니다.

방법	잘하는 일	한계
셀프 조인	바로 위(또는 정해진 단계 수만큼) 부모 찾기	단계 수만큼 조인을 늘려야 함
계층형 질의	깊이를 몰라도 끝까지 펼치고, 단계(LEVEL)와 경로 표시	오라클은 START WITH·CONNECT BY, SQL Server는 재귀 CTE

상품분류

분류코드	분류명	상위분류코드
10	식품	NULL
11	과일	10
111	사과	11
112	배	11
113	감	11
114	귤	11
12	선물세트	10
20	생활용품	NULL
21	주방용품	20

-- 상품분류의 모양
-- 식품(10)                 생활용품(20)
-- ├─ 과일(11)              └─ 주방용품(21)
-- │   ├─ 사과(111)
-- │   ├─ 배(112)
-- │   ├─ 감(113)
-- │   └─ 귤(114)
-- └─ 선물세트(12)

• 상위분류코드는 같은 테이블의 분류코드를 참조합니다. 맨 위 분류(식품, 생활용품)는 위가 없으므로 NULL입니다.
• 맨 위가 두 개라 트리도 두 그루입니다. 과일 아래는 3단계까지, 선물세트는 2단계에서 끝나 깊이가 다른 가지가 있습니다.
• 생활용품과 주방용품에는 상품이 없습니다. 상품이 없는 분류도 분류 트리에는 나옵니다.

상품분류연결

상품번호	분류코드	뜻
P10	111	사과 → 사과
P20	112	배 → 배
P30	113	감 → 감
P40	114	귤 → 귤
P50	111	청사과 → 사과
P60	12	배 선물세트 → 선물세트

상품 테이블에는 분류 컬럼이 없어서, 기존 테이블을 바꾸지 않고 상품과 분류를 잇는 테이블을 따로 두었습니다. 8장에서 「과일 아래 모든 상품」을 찾을 때 씁니다.

2. 셀프 조인

셀프 조인은 같은 테이블에 별칭을 둘 붙여 서로 다른 테이블처럼 잇는 조인입니다. 분류와 그 바로 위 분류를 나란히 봅니다.

SELECT s.분류명 AS 분류, p.분류명 AS 상위분류
  FROM 상품분류 s, 상품분류 p
 WHERE s.상위분류코드 = p.분류코드;

분류	상위분류
과일	식품
사과	과일
배	과일
감	과일
귤	과일
선물세트	식품
주방용품	생활용품

• s는 아래 분류, p는 위 분류 역할입니다. 별칭이 필수입니다. 같은 테이블이 두 번 나오므로 별칭이 없으면 어느 쪽 컬럼인지 구별할 수 없습니다.
• 결과는 9행 중 7행입니다. 상위분류코드가 NULL인 식품과 생활용품은 등가 조인에서 짝이 없어 빠집니다.
• 상품분류 s LEFT OUTER JOIN 상품분류 p ON s.상위분류코드 = p.분류코드로 바꾸면 9행이고, 식품과 생활용품의 상위분류는 NULL입니다. 「표준 조인, INNER·OUTER·CROSS JOIN」의 LEFT OUTER JOIN과 같은 원리입니다.

셀프 조인	결과
한 번 더 이어 「분류-상위-상위의 상위」 보기	4행(사과, 배, 감, 귤 모두 과일-식품)
부모 기준 LEFT JOIN으로 자식 수 세기	식품 2, 과일 4, 생활용품 1, 나머지 0

세 단계를 보려면 별칭을 하나 더 붙여 조인을 한 번 더 해야 합니다. 단계가 늘수록 조인도 늘어나는 것이 셀프 조인의 한계이고, 이를 해결하는 것이 계층형 질의입니다.

3. 계층형 질의 문법과 순방향 전개

SELECT LEVEL, LPAD(' ', (LEVEL - 1) * 2) || 분류명 AS 분류트리,
       분류코드, 상위분류코드
  FROM 상품분류
 START WITH 상위분류코드 IS NULL            -- 시작(루트) 행
 CONNECT BY PRIOR 분류코드 = 상위분류코드    -- 부모-자식 연결
 ORDER SIBLINGS BY 분류코드;                -- 형제끼리만 정렬

LEVEL	분류명	분류코드	상위분류코드
1	식품	10	NULL
2	과일	11	10
3	사과	111	11
3	배	112	11
3	감	113	11
3	귤	114	11
2	선물세트	12	10
1	생활용품	20	NULL
2	주방용품	21	20

-- 분류트리 컬럼(LPAD로 LEVEL만큼 들여씀)
-- 식품
--   과일
--     사과
--     배
--     감
--     귤
--   선물세트
-- 생활용품
--   주방용품

구성	뜻
START WITH	펼치기를 시작할 루트 행의 조건. 생략하면 모든 행이 각각 루트가 됨
CONNECT BY	부모 행과 자식 행의 관계. 조건 안의 한쪽 식에 PRIOR를 붙임
PRIOR	「앞에서 읽은 행의 값」. PRIOR가 붙은 쪽이 이미 읽은 행
LEVEL	루트는 1, 그 자식은 2, 손자는 3
NOCYCLE	데이터에 순환이 있어도 오류 없이 결과를 돌려줌
ORDER SIBLINGS BY	계층 모양을 유지한 채 같은 부모 아래 형제끼리 정렬

• 처리 순서는 ① 조인이 있으면 조인 → ② START WITH로 루트를 고르고 CONNECT BY로 자식, 손자 순서로 펼침 → ③ 남은 WHERE 조건으로 행마다 거름입니다.
• 결과는 깊이 우선 순서입니다. 부모 바로 아래에 그 자식들이 나오고, 한 가지를 끝까지 내려간 뒤 다음 형제로 넘어갑니다.

PRIOR 읽는 법

PRIOR 분류코드 = 상위분류코드는 「앞에서 읽은 행의 분류코드와 상위분류코드가 같은 행을 다음에 읽는다」입니다. 앞 행(부모)의 코드를 상위분류코드로 가진 행은 자식이므로 위에서 아래로(순방향) 펼쳐집니다. 식품(10)을 읽은 다음에는 상위분류코드가 10인 과일과 선물세트를 읽습니다.

4. PRIOR 방향과 시작 행에 따른 결과

CONNECT BY 조건	앞 행(PRIOR)과 다음 행	방향
PRIOR 분류코드 = 상위분류코드	앞 행의 분류코드 = 다음 행의 상위분류코드 → 자식	순방향(부모 → 자식)
분류코드 = PRIOR 상위분류코드	앞 행의 상위분류코드 = 다음 행의 분류코드 → 부모	역방향(자식 → 부모)
PRIOR 상위분류코드 = 분류코드	위와 같음(등호 좌우만 바꿈)	역방향

• 등호의 왼쪽, 오른쪽이 아니라 PRIOR가 어느 컬럼에 붙었는지가 방향을 정합니다. 「PRIOR 자식 쪽 컬럼(분류코드) = 부모 쪽 컬럼」이면 순방향, 「PRIOR 부모 쪽 컬럼(상위분류코드) = 자식 쪽 컬럼」이면 역방향으로 외워도 됩니다.

역방향 전개

SELECT LEVEL, 분류명
  FROM 상품분류
 START WITH 분류명 = '사과'
 CONNECT BY PRIOR 상위분류코드 = 분류코드;

LEVEL	분류명
1	사과
2	과일
3	식품

역방향에서는 시작 행(사과)이 LEVEL 1이고 위로 갈수록 LEVEL이 커집니다. 「LEVEL 1 = 맨 위 분류」가 아니라 「LEVEL 1 = 시작 행」입니다.

시작 행과 방향에 따른 행 수

순방향은 PRIOR 분류코드 = 상위분류코드, 역방향은 분류코드 = PRIOR 상위분류코드입니다.

시작 행	방향	행 수	이유
상위분류코드 NULL	순방향	9	두 루트에서 아래로 전부
과일	순방향	5	과일과 그 아래 4개
상위분류코드 '10'	순방향	6	과일, 선물세트가 루트(LEVEL 1)
사과	역방향	3	사과에서 위로
사과	순방향	1	사과는 자식이 없음
상위분류코드 NULL	역방향	2	루트는 부모가 없음
START WITH 생략	순방향	20	9개 행이 모두 루트가 됨

• START WITH를 생략해도 오류가 아닙니다. 9개 행이 각각 루트가 되어 자기 아래를 펼치므로 식품 7 + 과일 5 + 사과·배·감·귤 각 1 + 선물세트 1 + 생활용품 2 + 주방용품 1 = 20행입니다.
• START WITH 상위분류코드 = '10'처럼 중간에서 시작하면 과일과 선물세트가 LEVEL 1입니다.

5. 계층형 질의 전용 의사컬럼과 연산자

SELECT LEVEL, 분류명,
       SYS_CONNECT_BY_PATH(분류명, '/') AS 경로,
       CONNECT_BY_ROOT 분류명           AS 최상위,
       CONNECT_BY_ISLEAF                AS 리프,
       PRIOR 분류명                     AS 부모
  FROM 상품분류
 START WITH 상위분류코드 IS NULL
 CONNECT BY PRIOR 분류코드 = 상위분류코드
 ORDER SIBLINGS BY 분류코드;

분류명	경로	최상위	리프
식품	/식품	식품	0
과일	/식품/과일	식품	0
사과	/식품/과일/사과	식품	1
배	/식품/과일/배	식품	1
감	/식품/과일/감	식품	1
귤	/식품/과일/귤	식품	1
선물세트	/식품/선물세트	식품	1
생활용품	/생활용품	생활용품	0
주방용품	/생활용품/주방용품	생활용품	1

LEVEL은 3장의 표와 같고, 부모 컬럼은 식품·생활용품이 NULL, 과일·선물세트가 식품, 사과·배·감·귤이 과일, 주방용품이 생활용품입니다.

이름	종류	값
LEVEL	의사컬럼	루트 1, 그 자식 2, 손자 3
CONNECT_BY_ISLEAF	의사컬럼	자식이 없는 행(리프)이면 1, 아니면 0
CONNECT_BY_ISCYCLE	의사컬럼	자식 중에 자기 조상이 있으면 1. NOCYCLE과 함께만 사용
SYS_CONNECT_BY_PATH(컬럼, 구분자)	함수	루트부터 현재 행까지 값을 구분자로 이은 경로
CONNECT_BY_ROOT 컬럼	연산자	현재 행이 속한 트리의 루트 행 값
PRIOR 컬럼	연산자	바로 위 부모 행의 값. SELECT 절에도 쓸 수 있고 루트는 NULL

• CONNECT_BY_ROOT는 맨 위, PRIOR는 바로 위입니다. 사과 행에서 CONNECT_BY_ROOT 분류명은 식품, PRIOR 분류명은 과일입니다. 둘을 바꿔 쓴 보기가 단골입니다.
• 리프만 보기: WHERE CONNECT_BY_ISLEAF = 1이면 사과, 배, 감, 귤, 선물세트, 주방용품 6행입니다.
• 최상위별 개수: CONNECT_BY_ROOT 분류명으로 묶어 세면 식품 7, 생활용품 2입니다.
• LEVEL은 CONNECT BY가 있어야 씁니다. SELECT LEVEL FROM 상품분류는 오류(ORA-01788)입니다.

6. WHERE 조건과 CONNECT BY 조건

같은 조건이라도 WHERE에 쓰는지 CONNECT BY에 쓰는지에 따라 결과가 다릅니다. 시험에 가장 자주 나오는 부분입니다.

조건 위치	결과	행 수
WHERE 분류명 <> '과일'	과일 한 행만 빠지고 그 아래 사과, 배, 감, 귤은 남음	8
CONNECT BY PRIOR 분류코드 = 상위분류코드 AND 분류명 <> '과일'	과일에서 펼치기가 끊겨 과일과 그 아래 전체가 빠짐	4
CONNECT BY ... AND LEVEL <= 2	2단계까지만 펼침	5
WHERE LEVEL = 3	다 펼친 뒤 3단계만 남김	4

• WHERE는 펼치기가 끝난 뒤 행마다 거릅니다. 조건에 맞지 않는 행 하나만 빠지고 그 자식은 남습니다. 8행 결과에서 사과, 배, 감, 귤은 LEVEL 3 그대로, 부모 줄(과일) 없이 식품 아래에 보입니다.
• CONNECT BY 안의 조건은 「자식이 될 수 있는지」를 정합니다. 과일이 자식 조건을 통과하지 못하면 과일을 거쳐 내려가는 가지 전체가 잘립니다. 4행은 식품, 선물세트, 생활용품, 주방용품입니다.
• WHERE를 가위, CONNECT BY 조건을 톱으로 생각하면 쉽습니다. 가위는 이름표 하나를 오리고, 톱은 가지째 자릅니다.

7. ORDER SIBLINGS BY와 순환 데이터

정렬

정렬	결과 순서
ORDER SIBLINGS BY 분류명	생활용품, 주방용품, 식품, 과일, 감, 귤, 배, 사과, 선물세트(계층 유지, 형제끼리 가나다순)
ORDER SIBLINGS BY 분류명 DESC	식품, 선물세트, 과일, 사과, 배, 귤, 감, 생활용품, 주방용품
ORDER BY 분류명	감, 과일, 귤, 배, 사과, 생활용품, 선물세트, 식품, 주방용품(계층 순서가 깨짐)

• 일반 ORDER BY를 쓰면 부모 바로 아래에 자식이 오는 트리 모양이 깨집니다. 모양을 유지한 채 정렬하려면 ORDER SIBLINGS BY를 씁니다. LEVEL 값은 정렬과 상관없이 그대로입니다.
• ORDER SIBLINGS BY가 없으면 형제끼리의 순서는 보장되지 않습니다.
• 계층형 질의가 아닌 SELECT에 ORDER SIBLINGS BY를 쓰면 오류(ORA-30929)입니다.

순환 데이터와 NOCYCLE

식품(10)의 상위분류코드를 사과(111)로 바꾸면 식품 → 과일 → 사과 → 식품으로 다시 돌아오는 순환이 생깁니다.

SQL	결과
START WITH 분류코드 = '10' CONNECT BY PRIOR 분류코드 = 상위분류코드	오류(ORA-01436, 순환 발생)
CONNECT BY NOCYCLE PRIOR ...와 CONNECT_BY_ISCYCLE	식품 트리 7행이 나오고, 사과 행만 CONNECT_BY_ISCYCLE = 1

사과의 자식이 될 식품이 이미 사과의 조상이므로, 사과 행에서 순환이 표시됩니다. CONNECT_BY_ISCYCLE은 NOCYCLE을 쓸 때만 쓸 수 있습니다.

8. 계층형 질의를 서브쿼리로 쓰기

「과일 아래에 있는 모든 상품」은 분류가 몇 단계든 계층형 질의로 분류코드를 모은 뒤 IN 서브쿼리로 찾습니다.

SELECT p.상품번호, p.상품명, s.분류명
  FROM 상품 p
  JOIN 상품분류연결 l ON p.상품번호 = l.상품번호
  JOIN 상품분류 s     ON l.분류코드 = s.분류코드
 WHERE l.분류코드 IN (SELECT 분류코드 FROM 상품분류
                      START WITH 분류명 = '과일'
                      CONNECT BY PRIOR 분류코드 = 상위분류코드);

상품번호	상품명	분류명
P10	사과	사과
P20	배	배
P30	감	감
P40	귤	귤
P50	청사과	사과

• 같은 방식으로 판매금액을 더하면 과일 아래 51000, 식품 아래 76000입니다.
• 식품 아래에는 모든 상품이 있으므로 76000은 「GROUP BY와 HAVING, 집계 함수와 NULL」에서 구한 주문상품 전체 총금액과 같습니다. 과일 아래 51000은 76000에서 선물세트(배 선물세트 25000)를 뺀 값입니다.

9. SQL Server와의 차이

SQL Server에는 START WITH, CONNECT BY가 없습니다(문법 오류). 대신 WITH 절이 자기 자신을 참조하는 재귀 CTE(공통 테이블 식)를 씁니다. 오라클도 같은 문법을 지원합니다.

WITH 트리 (분류코드, 분류명, 단계) AS (
  SELECT 분류코드, 분류명, 1                 -- 앵커 멤버: 루트(= START WITH)
    FROM 상품분류 WHERE 상위분류코드 IS NULL
  UNION ALL
  SELECT c.분류코드, c.분류명, t.단계 + 1    -- 재귀 멤버: 자식(= CONNECT BY)
    FROM 상품분류 c JOIN 트리 t ON c.상위분류코드 = t.분류코드
)
SELECT 단계, 분류명 FROM 트리;

항목	오라클	SQL Server
계층형 질의	START WITH ... CONNECT BY, 재귀 CTE	재귀 CTE만
단계 표시	LEVEL 의사컬럼	앵커에서 1, 재귀 멤버에서 +1로 직접 계산
순환 데이터	NOCYCLE 없으면 ORA-01436	재귀 횟수 기본 한도 100번에서 멈추고 오류

• 결과는 9행이고 단계별 개수는 1단계 2, 2단계 3, 3단계 4로 CONNECT BY와 같습니다.
• 재귀 한도는 OPTION (MAXRECURSION n)으로 바꿀 수 있습니다.

10. 헷갈리기 쉬운 점

• PRIOR는 「부모」가 아니라 「앞에서 읽은 행」입니다. 순방향에서는 앞 행이 부모, 역방향에서는 앞 행이 자식입니다. PRIOR가 붙은 컬럼 값이 다음 행의 어느 컬럼과 같아야 하는지 화살표로 그려 봅니다.
• LEVEL 1은 시작 행입니다. 역방향에서는 가장 아래(사과)가 LEVEL 1이고, 중간에서 시작하면 그 행이 LEVEL 1입니다.
• WHERE는 펼친 뒤에 행마다 거르고, CONNECT BY 조건은 가지째 자릅니다. 과일을 WHERE로 빼면 8행, CONNECT BY로 빼면 4행입니다.
• START WITH를 생략해도 오류가 아닙니다. 모든 행이 루트가 되어 행이 중복해 나옵니다(9행 테이블에서 20행).
• CONNECT_BY_ROOT는 맨 위, PRIOR는 바로 위입니다. SYS_CONNECT_BY_PATH는 루트부터 현재 행까지의 경로입니다.
• ORDER BY를 쓰면 트리 모양이 깨집니다. 형제끼리만 정렬하려면 ORDER SIBLINGS BY를 씁니다.
• 셀프 조인의 등가 조인은 최상위 행을 빠뜨립니다. 상위분류코드가 NULL인 행은 짝이 없으므로 모두 보려면 OUTER JOIN을 씁니다.
• CONNECT_BY_ISCYCLE은 NOCYCLE과 함께만 씁니다. 순환 데이터에서 NOCYCLE 없이 펼치면 오류(ORA-01436)입니다.

11. 연습 문제

PRIOR의 방향, 시작 행, WHERE와 CONNECT BY 조건의 차이, 의사컬럼, 셀프 조인 행 수를 확인하는 문제입니다. 실제 시험과 같은 유형으로 새로 만들었습니다. 정답과 풀이는 다음 장에 있으니, 먼저 직접 풀어 보세요.

모든 문제는 다음 [상품분류] 테이블을 사용한다.

[상품분류]

분류코드	분류명	상위분류코드
10	식품	NULL
11	과일	10
111	사과	11
112	배	11
113	감	11
114	귤	11
12	선물세트	10
20	생활용품	NULL
21	주방용품	20

문제 1. 다음 두 SQL의 결과 행 수를 차례로 고르면?

-- (가)
SELECT LEVEL, 분류명 FROM 상품분류
 WHERE 분류명 <> '과일'
 START WITH 상위분류코드 IS NULL
 CONNECT BY PRIOR 분류코드 = 상위분류코드;

-- (나)
SELECT LEVEL, 분류명 FROM 상품분류
 START WITH 상위분류코드 IS NULL
 CONNECT BY PRIOR 분류코드 = 상위분류코드 AND 분류명 <> '과일';

① 8, 8
② 8, 4
③ 4, 8
④ 4, 4

문제 2. 다음 SQL 결과의 마지막 행의 분류명과 LEVEL은?

SELECT LEVEL, 분류명 FROM 상품분류
 START WITH 분류명 = '사과'
 CONNECT BY PRIOR 상위분류코드 = 분류코드;

① 식품, 1
② 과일, 2
③ 사과, 3
④ 식품, 3

문제 3. 다음 중 결과가 1행인 것은? (보기는 모두 SELECT 분류명 FROM 상품분류 뒤에 붙는다.)

① START WITH 상위분류코드 IS NULL CONNECT BY PRIOR 분류코드 = 상위분류코드
② START WITH 분류명 = '과일' CONNECT BY PRIOR 분류코드 = 상위분류코드
③ START WITH 분류명 = '사과' CONNECT BY PRIOR 분류코드 = 상위분류코드
④ START WITH 분류명 = '사과' CONNECT BY 분류코드 = PRIOR 상위분류코드

문제 4. 다음 SQL에서 분류명이 '사과'인 행의 컬럼 값으로 옳지 않은 것은?

SELECT 분류명, LEVEL AS 단계,
       CONNECT_BY_ROOT 분류명 AS A,
       CONNECT_BY_ISLEAF AS B,
       SYS_CONNECT_BY_PATH(분류명, '/') AS C
  FROM 상품분류
 START WITH 상위분류코드 IS NULL
 CONNECT BY PRIOR 분류코드 = 상위분류코드;

① A = 과일
② 단계 = 3
③ B = 1
④ C = /식품/과일/사과

문제 5. 다음 두 SQL의 결과 행 수를 차례로 고르면?

-- (가)
SELECT a.분류명, b.분류명
  FROM 상품분류 a, 상품분류 b
 WHERE a.상위분류코드 = b.분류코드;

-- (나)
SELECT a.분류명, b.분류명
  FROM 상품분류 a LEFT OUTER JOIN 상품분류 b
    ON a.상위분류코드 = b.분류코드;

① 7, 7
② 7, 9
③ 9, 9
④ 2, 9

12. 풀이

문제 1. 정답 ②
(가)는 펼치기가 끝난 뒤 WHERE로 과일 한 행만 빼므로 9 - 1 = 8행이고, 사과·배·감·귤은 남습니다. (나)는 과일이 자식 조건을 통과하지 못해 과일과 그 아래 4개가 함께 빠지므로 식품, 선물세트, 생활용품, 주방용품 4행입니다.

문제 2. 정답 ④
PRIOR가 상위분류코드에 붙어 「앞 행의 상위분류코드 = 다음 행의 분류코드」이므로 역방향입니다. 사과(1) → 과일(2) → 식품(3) 순서로 나오고, 마지막 행은 식품, LEVEL 3입니다. 역방향에서도 LEVEL 1은 시작 행인 사과입니다.

문제 3. 정답 ③
사과에서 순방향(아래로)으로 펼치는데 사과는 자식이 없어 사과 1행입니다. ①은 9행, ②는 과일과 그 아래 5행, ④는 사과에서 위로 가는 3행입니다.

문제 4. 정답 ①
CONNECT_BY_ROOT는 바로 위 부모가 아니라 트리의 루트 값이므로 A는 식품입니다. 과일은 PRIOR 분류명의 값입니다. 사과는 LEVEL 3이고 자식이 없어 CONNECT_BY_ISLEAF는 1, 경로는 /식품/과일/사과입니다.

문제 5. 정답 ②
(가) 등가 셀프 조인은 상위분류코드가 NULL인 식품과 생활용품이 짝이 없어 빠지므로 7행입니다. (나) LEFT OUTER JOIN은 모든 분류가 남아 9행이고, 식품과 생활용품의 b.분류명은 NULL입니다.

마치며

이번 글에서는 셀프 조인과 계층형 질의를 정리했습니다.

• 셀프 조인은 같은 테이블에 별칭을 둘 붙여 잇는 조인이고, 등가 조인이면 맨 위 행이 빠집니다. 상품분류 9행에서 7행, LEFT OUTER JOIN이면 9행입니다.
• 계층형 질의는 START WITH로 루트를 고르고 CONNECT BY로 펼치며, PRIOR가 붙은 컬럼이 방향을 정합니다. PRIOR 분류코드 = 상위분류코드는 순방향, PRIOR 상위분류코드 = 분류코드는 역방향입니다.
• LEVEL 1은 시작 행이고, START WITH를 생략하면 모든 행이 루트가 됩니다.
• CONNECT_BY_ROOT는 맨 위, PRIOR는 바로 위, CONNECT_BY_ISLEAF는 리프 여부, SYS_CONNECT_BY_PATH는 루트부터의 경로입니다.
• WHERE 조건은 행 하나만, CONNECT BY 조건은 가지 전체를 거릅니다. 정렬은 ORDER SIBLINGS BY, 순환 데이터는 NOCYCLE로 다룹니다.
• SQL Server에는 CONNECT BY가 없고 재귀 CTE를 씁니다.

다음 글 「PIVOT·UNPIVOT과 정규 표현식」에서는 행을 열로 펼치는 PIVOT, 열을 행으로 세우는 UNPIVOT, 그리고 LIKE보다 세밀한 패턴을 찾는 정규 표현식과 REGEXP 함수를 쇼핑몰 예시로 살펴보고 기출 유형 문제로 확인합니다.

참고 자료(공식)

• 데이터자격검정, SQL 개발자(SQLD) 안내: 출제 범위
• Oracle Database SQL Language Reference, Hierarchical Queries
• Oracle Database SQL Language Reference, Hierarchical Query Pseudocolumns
• Oracle Database SQL Language Reference, Hierarchical Query Operators
• Oracle Database SQL Language Reference, SYS_CONNECT_BY_PATH
• Microsoft Learn, WITH common_table_expression (Transact-SQL)