🌠2-2-6. 계층형 질의와 셀프 조인

과목2. SQL 기본과 활용

제2장 SQL 활용

제6절 계층형 질의와 셀프 조인

1. 개요

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다. 예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다. 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다. 순환관계 데이터 모델의 예로는 조직, 사원, 메뉴 등이 있다.

[그림 Ⅱ-2-6]은 사원에 대한 순환관계 데이터 모델을 표현한 것이다. (2)계층형 구조에서 A의 하위 사원은 B, C이고 B 밑에는 하위 사원이 없고 C의 하위 사원은 D, E가 있다. 계층형 구조를 데이터로 표현한 것이 (3)샘플 데이터이다. 순환 관계 데이터 모델은 셀프 조인이나 계층형 질의로 조회할 수 있다.

2. 셀프 조인

셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타난다. 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다. 그리고 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별해줘야 한다. 이외 사항은 조인과 동일하다.

셀프 조인에 대한 기본적인 사용법은 다음과 같다

SELECT ALIAS명1.칼럼명
    , ALIAS명2.칼럼명
    , ...
 FROM 테이블1 ALIAS명1
    , 테이블2 ALIAS명2
WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

SELECT WORKER.ID    사원번호
    , WORKER.NAME  사원명
    , MANAGER.NAME 관리자명
 FROM EMP WORKER
    , EMP MANAGER
WHERE WORKER.MGR = MANAGER.ID; 

계층형 질의에서 살펴보았던 사원이라는 테이블 속에는 사원과 관리자가 모두 하나의 사원이라는 개념으로 동일시하여 같이 입력되어 있다. 이것을 이용해서 다음 문제를 셀프 조인으로 해결해 보면 다음과 같다. "자신과 상위, 차상위 관리자를 같은 줄에 표시하라." 이 문제를 해결하기 위해서는 FROM 절에 사원 테이블을 두 번 사용해야 한다.

셀프 조인은 동일한 테이블(사원)이지만 [그림 Ⅱ-2-11]과 같이 개념적으로는 두 개의 서로 다른 테이블(사원, 관리자)을 사용하는 것과 동일하다. 동일 테이블을 다른 테이블인 것처럼 처리하기 위해 테이블 별칭을 사용한다. 여기서는 E1(사원), E2(관리자) 테이블 별칭을 사용하였다. 차상위 관리자를 구하기 위해서 E2.사원 = E1.관리자 조인 조건을 사용한다. 아래는 JONES의 자식 노드를 조회하는 쿼리다. mgr가 JONES의 empno인 행을 조회하면 된다.

[예제]

SELECT B.EMPNO, B.ENAME, B.MGR
 FROM EMP A, EMP B
WHERE A.ENAME = 'JONES'
  AND B.MGR = A.EMPNO; 

[실행 결과]

EMPNO	ENAME	MGR
7788	SCOTT	7566
7902	FORD	7566

2 개의 행이 선택되었습니다.

아래는 JONES의 자식 노드의 자식 노드를 조회하는 쿼리다. mgr이 JONES의 자식 노드인 SCOTT과 FORD의 empno인 행을 조회하면 된다. 이런 계층 전개 방식을 순방향 전개라고 한다.

[예제]

SELECT C.EMPNO, C.ENAME, C.MGR
 FROM EMP A, EMP B, EMP C
WHERE A.ENAME = 'JONES'
  AND B.MGR = A.EMPNO
  AND C.MGR = B.EMPNO; 

[실행 결과]

EMPNO	ENAME	MGR
7369	SMITH	7902
7876	ADAMS	7788

2 개의 행이 선택되었습니다.

아래는 SMITH의 부모 노드를 조회하는 쿼리다. empno가 SMITH인 mgr인 행을 조회하면 된다.

[예제]

SELECT B.EMPNO, B.ENAME, B.MGR
 FROM EMP A, EMP B
WHERE A.ENAME = 'SMITH'
  AND B.EMPNO = A.MGR;

[실행 결과]

EMPNO	ENAME	MGR
7902	FORD	7566

1 개의 행이 선택되었습니다.

아래는 SMITH의 부모 노드의 부모 노드를 조회하는 쿼리다. empno가 SMITH인 부모 노드인 FORD의 mgr인 행을 조회하면 된다. 이런 계층 전개 방식을 역방향 전개라고 한다.

[예제]

SELECT C.EMPNO, C.ENAME, C.MGR
 FROM EMP A, EMP B, EMP C
WHERE A.ENAME = 'SMITH'
  AND B.EMPNO = A.MGR 
  AND C.EMPNO = B.MGR 

[실행 결과]

EMPNO	ENAME	MGR
7566	JONES	7839

1 개의 행이 선택되었습니다.

지금까지 살펴본 것처럼 깊은 레벨의 노드를 조회하기 위해서는 셀프 조인을 반복해야 한다. Oracle과 SQL Server는 순환 관계를 가진 데이터를 조회할 수 있는 계층 질의 기능을 제공한다.

3. 계층형 질의

가. Oracle 계층형 질의

Oracle은 계층형 질의를 지원하기 위해서 [그림 Ⅱ-2-8]과 같은 계층형 질의 구문을 제공한다.

• START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉 루트 데이터를 지정한다.(액세스)

• CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.(조인)

• PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 순방향 전개를 한다. 그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 역방향 전개를 한다.

• NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.

• ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

• WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column)을 제공한다.

다음은 emp 테이블을 계층형 질의 구문을 이용해서 조회한 것이다. PK인 empno 앞쪽에 PRIOR 연산자를 기술했다. 여기서는 결과 데이터를 들여쓰기 하기 위해서 LPAD 함수를 사용하였다.

[예제]

SELECT LEVEL
    , LPAD(' ', 4 * ( LEVEL - 1 )) || 사원 사원
    , 관리자
    , CONNECT_BY_ISLEAF ISLEAF
 FROM 사원
START WITH 관리자 IS NULL
CONNECT BY PRIOR 사원 = 관리자; 

[실행 결과]

LEVEL	사원	관리자	ISLEAF
1	A		0
2	B	A	1
2	C	A	0
3	D	C	1
3	E	C	1

5 개의 행이 선택되었습니다.

A는 루트 데이터이기 때문에 레벨이 1이다. A의 하위 데이터인 B, C는 레벨이 2이다. 그리고 C의 하위 데이터인 D, E는 레벨이 3이다. 리프 데이터는 B, D, E이다. 관리자 → 사원 방향을 전개이기 때문에 순방향 전개이다. [그림 Ⅱ-2-8]은 계층형 질의에 대한 논리적인 실행 모습이다.

다음 예제는 사원 'D'로부터 자신의 상위관리자를 찾는 역방향 전개의 예이다.

[예제]

SELECT LEVEL
    , LPAD(' ', 4 * ( LEVEL - 1 )) || 사원 사원
    , 관리자
    , CONNECT_BY_ISLEAF ISLEAF
 FROM 사원
START WITH 사원 = 'D'
CONNECT BY PRIOR 관리자 = 사원; 

[실행 결과]

LEVEL	사원	관리자	ISLEAF
1	D	C	0
2	C	A	0
3	A		1

3 개의 행이 선택되었습니다.

본 예제는 역방향 전개이기 때문에 하위 데이터에서 상위 데이터로 전개된다. 결과를 보면 내용을 제외하고 표시 형태는 순방향 전개와 동일하다. D는 루트 데이터이기 때문에 레벨이 1이다. D의 상위 데이터인 C는 레벨이 2이다. 그리고 C의 상위 데이터인 A는 레벨이 3이다. 리프 데이터는 A이다. 루트 및 레벨은 전개되는 방향에 따라 반대가 됨을 알 수 있다. [그림 Ⅱ-2-9]는 역방향 전개에 대한 계층형 질의에 대한 논리적인 실행 모습이다.

Orcle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 [표 Ⅱ-2-3]과 같은 함수를 제공한다

SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT를 사용한 예는 다음과 같다. START WITH를 통해 추출된 루트 데이터가 1건 이기 때문에 루트사원은 모두 A이다. 경로는 루트로부터 현재 데이터까지의 경로를 표시한다. 예를 들어, D의 경로는 A → C → D 이다.

[예제]

SELECT CONNECT_BY_ROOT (사원) 루트사원
    , SYS_CONNECT_BY_PATH(사원, ',') 경로
    , 사원
    , 관리자
 FROM 사원
START WITH 관리자 IS NULL
CONNECT BY PRIOR 사원 = 관리자 

[실행 결과]

루트사원	경로	사원	관리자
A	,A	A
A	,A,B	B	A
A	,A,C	C	A
A	,A,C,D	D	C
A	,A,C,E	E	C

5 개의 행이 선택되었습니다.

나. SQL Server 계층형 질의

SQL Server 2000 버전까지는 계층형 질의를 작성 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등 (순수한 쿼리가 아닌) 프로그램 방식으로 전개해야만 했다. 그러나 SQL Server 2005 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 되었다. 먼저, Northwind 데이터베이스에 접속하여 Employees 테이블의 데이터를 조회해 보자. 총 9개 로우가 있는데, ReportsTo 칼럼이 상위 사원에 해당하며 EmployeeID 칼럼과 재귀적 관계를 맺고 있다. EmployeeID가 2인 Fuller 사원을 살펴보면, ReportsTo 칼럼 값이 NULL이므로 계층 구조의 최상위에 있음을 알 수 있다.

USE NORTHWIND
GO

SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO
 FROM EMPLOYEES
GO 

EmployeeID	LastName	FirstName	ReportsTo
1	Davolio	Nancy	2
2	Fulle	ANDrew	NULL
3	Leverling	Janet	2
4	Peacock	Margaret	2
5	Buchanan	Steven	2
6	Suyama	Michael	5
7	King	Robert	5
8	Callahan	Laura	2
9	Dodsworth	Anne	5

9 개의 행이 선택되었습니다.

(9개 행 적용됨)

CTE(Common Table Expression)를 재귀 호출함으로써 Employees 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 결과는 다음과 같다.

WITH EMPLOYEES_ANCHOR
    AS (SELECT EMPLOYEEID
             , LASTNAME
             , FIRSTNAME
             , REPORTSTO
             , 0 AS LEVEL
          FROM EMPLOYEES
         WHERE REPORTSTO IS NULL /* 재귀호출의시작점*/
        UNION ALL
        SELECT R.EMPLOYEEID
             , R.LASTNAME
             , R.FIRSTNAME
             , R.REPORTSTO
             , A.LEVEL + 1
          FROM EMPLOYEES_ANCHOR A
             , EMPLOYEES R
         WHERE A.EMPLOYEEID = R.REPORTSTO )
SELECT LEVEL
    , EMPLOYEEID
    , LASTNAME
    , FIRSTNAME
    , REPORTSTO
 FROM EMPLOYEES_ANCHOR GO 

Level	EmployeeID	LastName	FirstName	ReportsTo
0	2	Fuller	Andrew	NULL
1	1	Davolio	Nancy	2
1	3	Leverling	Janet	2
1	4	Peacock	Margaret	2
1	5	Buchanan	Steven	2
1	8	Callahan	Laura	2
2	6	Suyama	Michael	5
2	7	King	Robert	5
2	9	Dodsworth	Anne	5

9 개의 행이 선택되었습니다.

(9개 행 적용됨)

WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 쿼리 두 개를 결합했다. 둘 중 위에 있는 쿼리를 '앵커 멤버'(Anchor Member)라고 하고, 아래에 있는 쿼리를 '재귀 멤버'(Recursive Member)라고 한다. 아래는 재귀적 쿼리의 처리 과정이다.

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.

2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.

3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행한다.

4. 빈 집합이 반환될 때까지 3단계를 반복한다.

5. 결과 집합을 반환한다. 이것은 T0에서 Tn까지의 UNION ALL이다.

정리하자면 다음과 같다. 먼저, 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인을 시작한다. 이어서, 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면 즉 더 조인할 수 없으면 지금까지 만들어진 결과 집합을 모두 합하여 리턴한다. [그림 Ⅱ-2-10]에 있는 조직도를 쿼리로 출력했을 때, 대부분 사용자는 아래와 같은 결과를 기대할 것이다.(보기 편하도록 각 로우 앞쪽에 자신의 레벨만큼 빈칸을 삽입했다.)

EmployeeID	ManagerID
1000	NULL
1100	1000
1110	1100
1120	1100
1121	1120
1122	1120
1200	1000
1210	1200
1211	1210
1212	1210
1220	1200
1221	1220
1222	1220
1300	1000

14 개의 행이 선택되었습니다.

아래에 t_emp 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 그 결과다. 보다시피, 계층 구조를 단순히 하위 방향으로 전개했을 뿐 [그림 Ⅱ-2-10]에 있는 조직도와는 많이 다른 모습이다. 앞서 보았듯이, CTE 재귀 호출로 만들어낸 계층 구조는 실제와 다른 모습으로 출력된다. 따라서 조직도와 같은 모습으로 출력하려면 ORDER BY 절을 추가해 원하는 순서대로 결과를 정렬해야 한다. 실제 조직도와 같은 모습의 결과를 출력하도록, CTE에 Sort라는 정렬용 칼럼을 추가하고 쿼리 마지막에 ORDER BY 조건을 추가해보자.(단, 앵커 멤버와 재귀 멤버 양쪽에서 convert 함수 등으로 데이터 형식을 일치시켜야 한다.)

WITH T_EMP_ANCHOR
    AS (SELECT EMPLOYEEID
             , MANAGERID
             , 0 AS LEVEL
          FROM T_EMP
         WHERE MANAGERID IS NULL /* 재귀호출의시작점*/
        UNION ALL
        SELECT R.EMPLOYEEID
             , R.MANAGERID
             , A.LEVEL + 1
          FROM T_EMP_ANCHOR A
             , T_EMP R
         WHERE A.EMPLOYEEID = R.MANAGERID)
SELECT LEVEL
    , REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID
    , MANAGERID
 FROM T_EMP_ANCHOR

GO 

Level	EmployeeID	ManagerID
0	1000	NULL
1	1100	1000
1	1200	1000
1	1300	1000
2	1210	1200
2	1220	1200
3	1221	1220
3	1222	1220
3	1211	1210
3	1212	1210
2	1110	1100
2	1120	1100
3	1121	1120
3	1122	1120

14 개의 행이 선택되었습니다.

14개 행 적용됨

WITH T_EMP_ANCHOR
    AS (SELECT EMPLOYEEID
             , MANAGERID
             , 0 AS LEVEL
             , CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT
          FROM T_EMP
         WHERE MANAGERID IS NULL
        /* 재귀호출의시작점*/
        UNION ALL
        SELECT R.EMPLOYEEID
             , R.MANAGERID
             , A.LEVEL + 1
             , CONVERT(VARCHAR(1000), A.SORT + '/' + R.EMPLOYEEID) AS SORT
          FROM T_EMP_ANCHOR A
             , T_EMP R
         WHERE A.EMPLOYEEID = R.MANAGERID)
SELECT LEVEL
    , Replicate(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID
    , MANAGERID
    , SORT
 FROM T_EMP_ANCHOR
ORDER BY SORT

GO 

CTE 안에서 Sort 칼럼에 사번(=EmployeeID)을 재귀적으로 더해 나가면 정렬 기준으로 삼을 수 있는 값이 만들어진다. 아래는 Sort 칼럼으로 정렬하여 출력한 결과인데, [그림 Ⅱ-2-10]에 있는 조직도의 모습과 일치한다. 가상의 Sort 칼럼을 추가해 정렬하는 게 아쉽기는 하지만, SQL Server에서 계층 구조를 실제 모습대로 출력하려면 현재(2005, 2008 버전 기준)로서는 감수해야 할 수밖에 없다.

Level	EmployeeID	ManagerID	Sort
0	1000	NULL	1000
1	1100	1000	1000/1100
2	1110	1100	1000/1100/1110
2	1120	1100	1000/1100/1120
3	1121	1120	1000/1100/1120/1121
3	1122	1120	1000/1100/1120/1122
1	1200	1000	1000/1200
2	1210	1200	1000/1200/1210
3	1211	1210	1000/1200/1210/1211
3	1212	1210	1000/1200/1210/1212
2	1220	1200	1000/1200/1220
3	1221	1220	1000/1200/1220/1221
3	1222	1220	1000/1200/1220/1222
1	1300	1000	1000/1300

14 개의 행이 선택되었습니다.

14개 행 적용됨

출처 : 데이터온에어 – 한국데이터산업진흥원(https://dataonair.or.kr)