목차

1. 계층형 질의
2. 셀프조인

h1.1. 계층형 질의

• 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다

사원에 대한 순환관계 데이터 모델을 표현한 것이다. (2)계층형 구조에서 A의 하위 사원은 B, C이고 B 밑에는 하위 사원이 없고 C의 하위 사원은 D, E가 있다. 계층형 구조를 데이터로 표현한 것이 (3)샘플 데이터이다.
계층형 데이터 조회는 DBMS 벤더와 버전에 따라 다른 방법으로 지원한다.

가. Oracle 계층형 질의

<오라클>

• START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트 데이터를 지정한다.(액세스)
• CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다.(조인)
• PRIOR : CONNECT BY절에 사용되며, 현재 읽은 칼럼을 지정한다.
  PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 순방향 전개를 한다.
  그리고 PRIOR 부모 = 자식 형태를 사용하면 반대로 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 역방향 전개를 한다.
• NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
• ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
• WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

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

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || ENAME , EMPNO , MGR, CONNECT_BY_ISLEAF ISLEAF 
   FROM EMP  
  START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR;

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || ENAME , EMPNO, MGR, CONNECT_BY_ISLEAF ISLEAF 
   FROM EMP  
  START WITH MGR = '7788'
CONNECT BY PRIOR MGR= EMPNO ;

ORDER SIBLINGS BY

• ORDER SIBLINGS BY절을 사용하면 계층구조 쿼리에서 편하게 정렬작업을 할 수 있다.

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || ENAME , EMPNO , MGR, CONNECT_BY_ISLEAF ISLEAF 
   FROM EMP  
  START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR
-----------------------------------------------------
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || ENAME , EMPNO , MGR, CONNECT_BY_ISLEAF ISLEAF 
   FROM EMP  
  START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO DESC

CONNECT BY의 실행순서는 다음과 같다.

• 첫째 START WITH 절
• 둘째 CONNECT BY 절
• 세째 WHERE 절 순서로 풀리게 되어있다.

-- 결과값은 EMPNO가 '7698' 인 로우만 남는다.
SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || ENAME , EMPNO , MGR, CONNECT_BY_ISLEAF ISLEAF 
   FROM EMP  
  WHERE EMPNO = '7698'
  START WITH MGR IS NULL 
CONNECT BY PRIOR EMPNO = MGR

나. SQL Server 계층형 질의

SQL Server 2000 버전까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았다. 조직도처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등 (순수한 쿼리가 아닌) 프로그램 방식으로 전개해야만 했다. 그러나 SQL Server 2005 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 되었다. 먼저, Northwind 데이터베이스에 접속하여 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 )

h1.1. 셀프조인

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

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

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

문서에 대하여

• 최초작성자 : ~우리집아찌
• 최초작성일 : 2013년 09월 23일
• 이 문서는 오라클클럽 오라클 성능 트러블슈팅 스터디 모임에서 작성하였습니다.
• {*}이 문서의 내용은 한국 데이터베이스 진흥원에서 출간한 'SQL 전문가 가이드'를 참고하였습니다.*