[SQL 이해하기] - 16. SQL: 계층 쿼리

 

테이블 안에 계층 정보가 포함되어 있는 경우가 있다. 예를 들어 어떤 회사의 직원들을 나타내는 아래 테이블을 보자.

EMPNO	NAME	MGRNO	DEPTNO
-----	----	-----	------
100	Steve	NULL	90
101	Nina	100	90
102	Mark	100	90
103	Nancy	101	100
104	Jimmy	102	100
105	Dan	102	100
106	Aron	103	100
107	Emma	103	100

 

Steve는 Nina와 Mark의 상사이고 Nina는 Nancy의 상사, Mark는 Jimmy와 Dan의 상사, Nancy는 Aron과 Emma의 상사임을 알 수 있다. 이와 같이 테이블 안에서 같은 테이블로의 참조가 발생하는 경우 아래와 같이 같은 테이블 사이를 조인하는 셀프 조인(Self join)을 사용하여 계층 구조를 파악할 수 있다.

SELECT A.EMPNO, A.NAME, A.MGRNO, A.DEPTNO, B.NAME AS MANAGER
FROM EMPLOYEES A, EMPLOYEES B
WHERE A.MGRNO = B.EMPNO;

 

하지만 계층 구조가 깊어지면 같은 테이블을 여러번 셀프조인 해야 할 수도 있기 때문에 셀프 조인 보다 계층 쿼리를 사용한다. 계층 쿼리의 일반적인 문법은 아래와 같다.

SELECT LEVEL, SYS_CONNECT_BY_PATH(대상 컬럼, 구분자)
FROM 대상 테이블
START WITH PARENT IS NULL
CONNECT BY PRIOR CHILDREN = PARENT;

 

이전까지 살펴봤던 쿼리와 꽤 다른데 계층 쿼리에 쓰인 각 키워드의 의미를 살펴보면 아래와 같다.

LEVEL	현재 행의 DEPTH를 반환한다.
SYS_CONNECT_BY_PATH(컬럼, 구분자)	루트(root) 노드부터 현재 노드까지 경로를 출력해준다.
START WITH	경로가 시작되는 루트 노드를 지정한다.
CONNECT BY	루트로부터 자식 노드를 생성한다. 조건을 만족시키는 데이터가 없을 때 까지 노드를 생성한다.
PRIOR	바로 앞에 있는 부모 노드를 반환한다.
CONNECT_BY_ROOT 컬럼	루트 노드의 주어진 컬럼 값을 반환한다.
CONNECT_BY_ISLEAF	가장 하위 노드(leaf node)인 경우 1을 반환하고 그 외에는 0을 반환한다.

 

계층형 쿼리를 이용해서 포스팅의 맨 처음 테이블의 구조를 출력하는 쿼리를 아래와 같이 작성할 수 있다. 상사가 없는(MGRNO IS NULL) 행 부터 시작하고, 부모 노드(PRIOR)의 EMPNO가 자식 노드의 MGRNO로 연결됨을 CONNECT BY 구문을 이용해서 명시하고 있다.

SELECT EMPNO, NAME, MGRNO, LEVEL
FROM EMPLOYEES
START WITH MGRNO IS NULL
CONNECT BY PRIOR EMPNO = MGRNO;

EMPNO	NAME	MGRNO	LEVEL
-----	----	-----	------
100	Steve	NULL	1
101	Nina	100	2
102	Mark	100	2
103	Nancy	101	3
104	Jimmy	102	3
105	Dan	102	3
106	Aron	103	4
107	Emma	103	4

 

참고로 계층형 쿼리의 결과를 정렬할 때는 같은 레벨끼리 정렬되도록 만들어주는 ORDER SIBLING BY 절을 사용한다.

 

■