이번 퀴즈로 배워보는 SQL 시간에는 오라클에서 사용할 수 있는 계층구조 쿼리를 어떻게 작성하는지에 대해 알아본다.

지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한후 정답과 해설을 참조하길 바란다.

공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.

문제

사원번호(empno), 성명(ename), 상사의 사원번호(mgr) 를 가진 테이블 emp가 있습니다.

이 원본테이블([표 1] 참조)을 이용하여 [표 2]의 계층구조 결과테이블을 완성하는 쿼리를 작성하세요.

scott 사용자의 emp 테이블을 사용하여 문제를 해결하면 됩니다.

• [표 1] 원본 테이블

 EMPNO ENAME        MGR
------ -------- -------
  7369 SMITH       7902
  7499 ALLEN       7698
  7521 WARD        7698
  7566 JONES       7839
  7654 MARTIN      7698
  7698 BLAKE       7839
  7782 CLARK       7839
  7788 SCOTT       7566
  7839 KING
  7844 TURNER      7698
  7876 ADAMS       7788
  7900 JAMES       7698
  7902 FORD        7566
  7934 MILLER      7782

• [표 2] 결과 테이블

 EMPNO ENAME        LV        MGR MGR_ENAME  ENAMES
------ ---------- ---- ---------- ---------- -------------------------
  7839 KING          1                       KING
  7566 JONES         2       7839 KING       KING-JONES
  7788 SCOTT         3       7566 JONES      KING-JONES-SCOTT
  7876 ADAMS         4       7788 SCOTT      KING-JONES-SCOTT-ADAMS
  7902 FORD          3       7566 JONES      KING-JONES-FORD
  7369 SMITH         4       7902 FORD       KING-JONES-FORD-SMITH
  7698 BLAKE         2       7839 KING       KING-BLAKE
  7499 ALLEN         3       7698 BLAKE      KING-BLAKE-ALLEN
  7521 WARD          3       7698 BLAKE      KING-BLAKE-WARD
  7654 MARTIN        3       7698 BLAKE      KING-BLAKE-MARTIN
  7844 TURNER        3       7698 BLAKE      KING-BLAKE-TURNER
  7900 JAMES         3       7698 BLAKE      KING-BLAKE-JAMES
  7782 CLARK         2       7839 KING       KING-CLARK
  7934 MILLER        3       7782 CLARK      KING-CLARK-MILLER

문제설명

이 문제는 사원번호와 상사의 사원번호를 가진 테이블로부터 상사와 부하직원간의 관계를 이용하여 계층적인 구조를 표현하는 문제입니다.

[표 2]의 결과를 보면 상사가 없는 사원 "KING"으로부터 시작하여 레벨이 1이 되고, 그다음 상사가 "KING"인 부하직원 "JONES", "BLAKE", "CLARK" 이 레벨 2가 됩니다.

이때 부하직원 3명의 정렬순서는 사원번호 순서대로 정렬을 합니다. 그리고 각각의 2레벨 직원들의 부하직원이 3레벨로 연결되고, 다시 3레벨 직원의 부하직원이 4레벨로 연결되는 구조로 이루어집니다.

[표 2]의 결과 테이블에 추가된 항목은 다음과 같습니다.

• - LV 는 계층구조의 레벨을 의미합니다.
• - MGR_ENAME 은 상사의 이름을 의미합니다.
• - ENAMES 는 최상위 계층에서부터 해당 직원까지 연결된 이름을 나열한 것입니다.

정답

문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.

• [리스트1] 정답 리스트

SELECT empno
     , ename
     , LEVEL lv
     , mgr
     , PRIOR ename mgr_ename
     , SUBSTR(SYS_CONNECT_BY_PATH(ename, '-'), 2) enames
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
 ORDER SIBLINGS BY empno
;

어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.

해설

동일 테이블끼리 EMPNO 와 MGR 을 조인하면 2단계까지 부하직원을 찾을 수 있습니다.

하지만 이 문제는 부하직원의 부하직원을 계속해서 찾아 내려가야만 하는 문제로, 부하직원 이 어느 레벨까지 내려갈지 알 수 없기 때문에 셀프 조인을 사용할 수는 없습니다.

따라서 이 문제를 풀기 위해서는 순환구조로 테이블을 반복적으로 읽어 내려갈 수 있는 쿼리가 필요합니다.

이렇게 반복적으로 자기 자신을 참조하는 쿼리를 재귀 쿼리(Recursive Query)라고 합니다. 오라클에서는 CONNECT BY 라고 하는 오라클만의 방식을 제공합니다.

다른 DBMS 에서는 WITH 문으로 정의 된 집합을 WITH 문 안에서 다시 조회하는 방식으로 재귀 쿼리를 구현합니다.

오라클 버전 10G 까지는 이와 같은 방식을 사용할 수 없었지만 11G 부터는 이와 같은 방식도 사용이 가능해 졌습니다.

CONNECT BY 절에 대한 설명이 끝나면 WITH 문을 이용한 재귀 쿼리에 대해서도 살펴보도록 하겠습니다.

우선 계층구조를 전개하기 위해 필요한 것은 계층구조의 최상위 시작점입니다. 상사와 부하로 연결되는 구조에서 가장 최상위는 상사가 없는 직원이 됩니다.

즉 MGR 이 NULL 인 직원을 시작으로 계층구조를 전개하면 됩니다. START WITH 절은 계층구조의 시작조건을주는 조건절이 됩니다.

START WITH MGR IS NULL

두 번째로 계층구조를 전개하기 위해 필요한 것은 상위와 하위 간의 관계입니다. 이 문제에서는 사원번호(EMPNO)와 상사의 사원번호(MGR) 간의 관계를 이용합니다.

정확하게 표현하면 상위 행의 사원번호와 하위 행의 상사의 사원번호가 서로 연결되는 구조입니다.

상하위 간의 관계는 [표 3] 을 참조하세요.

• [표 3] 상위행과 하위행간의 관계
• 

계층구조의 상하위간의 관계에 대한 조건은 CONNECT BY 절에 주게 됩니다. 이때 PRIOR 는 상위에 해당하는 값임을 의미합니다.

CONNECT BY PRIOR empno = mgr

이렇게 계층구조의 시작조건 구문과 계층관계 조건 구문이 완성되었습니다. 이 두 개 조건만으로 쿼리([리스트 2])를 실행해 보겠습니다.

• [리스트 2] 계층구조 쿼리

SELECT empno
     , ename
     , mgr
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
;

• [표 4] 계층구조 쿼리 결과

  EMPNO ENAME             MGR
------- ---------- ----------
   7839 KING
   7566 JONES            7839
   7788 SCOTT            7566
   7876 ADAMS            7788
   7902 FORD             7566
   7369 SMITH            7902
   7698 BLAKE            7839
   7499 ALLEN            7698
   7521 WARD             7698
   7654 MARTIN           7698
   7844 TURNER           7698
   7900 JAMES            7698
   7782 CLARK            7839
   7934 MILLER           7782

[리스트 2]계층구조 쿼리의 실행결과 [표 4]를 보면 계층관계를 이용해 결과가 나온 것을 알 수 있습니다. 시작조건과 관계조건 두 개 만 주면 손쉽계 계층구조 쿼리가 완성되는것입니다.

하지만 결과가 원본과 크게 달라 보이지 않아 계층구조인지 아닌지 파악하기 어렵습니다. 나머지 추가 항목들을 만들어 보겠습니다.

계층구조에서 단계, 레벨을 나타내 주는 함수는 LEVEL입니다.

   , LEVEL lv

계층구조의 경로를 보여주는 함수는 SYS_CONNECT_BY_PATH입니다.

계층 경로에 표현될 항목과 경로 사이 사이 표현될 구분자를 입력해주면 간단하게 원하는 결과가 나옵니다. 단, 구분자가 맨 앞에 한번 더 나오므로 맨 앞의 구분자는 SUBSTR으로 제거해 보도록 하겠습니다.

   , SUBSTR(SYS_CONNECT_BY_PATH(ename, '-'), 2) enames

상사의 이름은 어떻게 구할 수 있을까요? MGR 이 있으니 이를 이용해 EMP 테이블과 조인해서 구해야 할까요?

   , (SELECT ename FROM emp b WHERE b.empno = a.mgr) mgr_ename
FROM emp a

여기까지 생각하셨다면 일단은 문제를 푸는 능력은 갖추고 계신 것입니다. 하지만 계층 쿼리에 대한 이해가 부족하다고 할 수 있겠네요.

앞에서 CONNECT BY 절에 조건을 줄때 사용한 PRIOR 는 상위의 값을 표현할 때 사용한다고 설명을 드렸습니다. 따라서 상위의 이름값을 가져오고 싶다면 간단하게 PRIOR ename 이라고 적어 주기만 하면 됩니다.

   , PRIOR ename mgr_ename

정말 간단하죠. 계층쿼리를 사용하고 계신 분들 중에서, 이 간단한 것을 모르는 분이 의외로 많더군요. 대부분의 사용자는 CONNECT BY PRIOR 를 마치 하나의 구문처럼 암기해서사용하시고 계시더군요.

CONNECT BY 와 PRIOR 의 역할이 각기 다름에도 불구하고 이를하나의 구문처럼 생각하시는 분들은 이번 기회에 각각의 역할을 이해하고 사용했으면 하는 바램으로 이번 퀴즈를 출제하게 되었습니다. 이번 퀴즈의 키포인트라고 할 수 있겠네요.

자 이제 정답이 거의 완성되어 가고 있네요. 마지막으로 정렬입니다. 부하직원은 사원번호 순으로 정렬해야 합니다. 일반적인 정렬구문([리스트 3])을 사용해 보겠습니다.

• [리스트 3] 일반 정렬 구문

SELECT empno
     , ename
     , LEVEL lv
     , mgr
     , PRIOR ename mgr_ename
     , SUBSTR(SYS_CONNECT_BY_PATH(ename, '-'), 2) enames
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
 ORDER BY empno
;

• [표 5] 일반 정렬 구문 결과

  EMPNO ENAME              LV        MGR MGR_ENAME  ENAMES
------- ---------- ---------- ---------- ---------- -----------------------
   7369 SMITH               4       7902 FORD       KING-JONES-FORD-SMITH
   7499 ALLEN               3       7698 BLAKE      KING-BLAKE-ALLEN
   7521 WARD                3       7698 BLAKE      KING-BLAKE-WARD
   7566 JONES               2       7839 KING       KING-JONES
   7654 MARTIN              3       7698 BLAKE      KING-BLAKE-MARTIN
   7698 BLAKE               2       7839 KING       KING-BLAKE
   7782 CLARK               2       7839 KING       KING-CLARK
   7788 SCOTT               3       7566 JONES      KING-JONES-SCOTT
   7839 KING                1                       KING
   7844 TURNER              3       7698 BLAKE      KING-BLAKE-TURNER
   7876 ADAMS               4       7788 SCOTT      KING-JONES-SCOTT-ADAMS
   7900 JAMES               3       7698 BLAKE      KING-BLAKE-JAMES
   7902 FORD                3       7566 JONES      KING-JONES-FORD
   7934 MILLER              3       7782 CLARK      KING-CLARK-MILLER

[표 5] 일반 정렬 구문 결과를 보면 전체 자료가 사원번호 순서대로 정렬이 되어 계층구조가 흐트러진 것을 확인 할 수 있습니다.

우리가 원하는 결과는 계층구조는 그대로 유지하면서 부하사원들끼리의 순서만 정렬해주는 것입니다. 이때 사용되는 구문은 ORDER SIBLINGS BY 구문입니다.

 ORDER SIBLINGS BY empno

지금까지 설명 드린 내용을 하나로 연결해보면 정답쿼리([리스트 1])가 완성이 됩니다. 여기까지는 오라클만의 특화된 영역이었구요.

이번에는 11G에서 새로 등장한 구문으로 타 DBMS 에서도 사용 가능한 구문을 함께 배워 보도록 하겠습니다.

• [리스트 4] WITH 문을 이용한 재귀 쿼리

WITH t1(empno, ename, lv, mgr, mgr_ename, enames, empnos) AS
(
SELECT empno
     , ename
     , 1 lv
     , mgr
     , '' mgr_ename
     , ename enames
     , TO_CHAR(empno) empnos
  FROM emp
 WHERE mgr IS NULL
 UNION ALL
SELECT t2.empno
     , t2.ename
     , t1.lv + 1 lv
     , t2.mgr
     , t1.ename mgr_ename
     , t1.enames || '-' || t2.ename enames
     , t1.empnos || '-' || t2.empno empnos
  FROM t1, emp t2
 WHERE t1.empno = t2.mgr
)
SELECT *
  FROM t1
 ORDER BY empnos
;

• [표 5] WITH 문을 이용한 재귀 쿼리 결과

 EMPNO ENAME        LV      MGR MGR_ENAME  ENAMES                   EMPNOS
------ ---------- ---- -------- ---------- ------------------------ --------------------
  7839 KING          1                     KING                     7839
  7566 JONES         2     7839 KING       KING-JONES               7839-7566
  7788 SCOTT         3     7566 JONES      KING-JONES-SCOTT         7839-7566-7788
  7876 ADAMS         4     7788 SCOTT      KING-JONES-SCOTT-ADAMS   7839-7566-7788-7876
  7902 FORD          3     7566 JONES      KING-JONES-FORD          7839-7566-7902
  7369 SMITH         4     7902 FORD       KING-JONES-FORD-SMITH    7839-7566-7902-7369
  7698 BLAKE         2     7839 KING       KING-BLAKE               7839-7698
  7499 ALLEN         3     7698 BLAKE      KING-BLAKE-ALLEN         7839-7698-7499
  7521 WARD          3     7698 BLAKE      KING-BLAKE-WARD          7839-7698-7521
  7654 MARTIN        3     7698 BLAKE      KING-BLAKE-MARTIN        7839-7698-7654
  7844 TURNER        3     7698 BLAKE      KING-BLAKE-TURNER        7839-7698-7844
  7900 JAMES         3     7698 BLAKE      KING-BLAKE-JAMES         7839-7698-7900
  7782 CLARK         2     7839 KING       KING-CLARK               7839-7782
  7934 MILLER        3     7782 CLARK      KING-CLARK-MILLER        7839-7782-7934

WITH 문의 구성을 살펴보면 SELECT 문 두 개가 UNION ALL 로 연결되어 있습니다.

첫 번째 SELECT 문은 mgr IS NULL 조건으로 최상위 자료를 가져오는 쿼리입니다. 최상위 레벨이므로 lv 값은 1, mgr_ename 은 ''이 됩니다. enames 는 자신의 ename 이됩니다.

두 번째 SELECT 문은 FROM 절에 WITH 로 정의된 t1 자기 자신을 참조합니다. t1 과 emp t2 를 조인하는 구조이지요.

t1 에는 일단 첫 번째 SELECT문에 의해 최상위 자료가 조회되고, t1.empno = t2.mgr 조건에 의해 2레벨의 부하직원이 다시 조회되게 됩니다. 이 때 lv 는 상위의 lv, 즉, t1.lv 에 1을 더한 2가 되구요, mgr_ename 은 상위의t1.ename 이 됩니다. 상위까지의 t1.enames 에 형재 ename 을 연결하면 enames 가 됩니다. 이런 절차로 3레벨 4레벨이 차례로 연결되면서 계층구조가 완성이 되는 구조입니다.

오라클만의 독특한 구문인 STSRT WITH, CONNECT BY, PRIOR, ORDER SIBLINGS BY, LEVEL, SYS_CONNECT_BY_PATH 등이 사용되지 않고, 원하는 항목들을 1씩 증가하는 숫자나 값을 차례로 연결해 나가는 방식 상위의 값을 구하는 방법 등 일반적인 방식으로 만들어 내고 있습니다.

다만 이 방식은 위에서 설명드린대로 1레벨 가져온 후 2레벨 전체 가져오고 다시 3레벨 전체 가져오는 방식으로 진행되기 때문에 결과 값을 보면 레벨 순서대로 정렬이 되게 됩니다.

계층구조순 정렬을 위해 별도 empnos 라고 하는 항목을 추가로 조회해서 정렬에 이용해 봤습니다.

오라클을 사용하고 있는 사용자라면 좀더 간단하게 구현할 수 있는 CONNECT BY 구문을사용하는 것이 편리하겠지요. 하지만 한 가지만 편식하면 좋지 않겠지요?

11G의 새로운 방법도 꼭 알아두시기 바랍니다. 다른 DBMS 에서도 사용할 수 있는 방법입니다.