[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 2장. SQL 활용 - 3. 계층형 질의와 셀프 조인

계층형 질의

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의 (Hierarchical Query)를 사용한다.
• 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.
• 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다.
  

Oracle 계층형 질의

• Oracle은 계층형 질의를 지원하기 위해서 아래와 같은 계층형 질의 구문을 제공한다.

SELECT ...
FROM 테이블
WHERE condition AND condition...
START WITH condition
CONNECT BY [NOCYCLE] condition AND condition ...
[ORDER SIBLINGS BY column, column, ...]

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

가상 칼럼

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

순방향 전개

• 다음은 [그림 II-2-6]의 (3)샘플 데이터를 계층형 질의 구문을 이용해서 조회한 것이다.
• 결과 데이터를 들여쓰기 하기 위해서 LPAD 함수를 사용하였다.

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

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

역방향 전개

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

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

• 아래 그림은 역방향 전개에 대한 계층형 질의에 대한 논리적인 실행 모습이다.
  • D는 루트 데이터이기 때문에 레벨이 1이다.
  • D의 상위 데이터인 C는 레벨이 2이다.
  • C의 상위 데이터인 A는 레벨이 3이다.
  • 리프 데이터는 A이다.
  • 루트 및 레벨은 전개되는 방향에 따라 반대가 됨을 알 수 있다.
    

계층형 질의에서 사용되는 함수

Orcle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 아래와 같은 함수를 제공한다.

• SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT를 사용한 예는 다음과 같다.

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

SQL Server 계층형 질의

• CTE(Common Table Expression) 를 재귀 호출함으로써 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개한다.

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

• 재귀적 쿼리의 처리 과정은 다음과 같다.

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.
   • 위에 있는 쿼리를 ‘앵커 멤버’(Anchor Member)라고 하고, 아래에 있는 쿼리를 ‘재귀 멤 버’(Recursive Member)라고 한다.
2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.
3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행한다.
4. 빈 집합이 반환될 때까지 3단계를 반복한다.
5. 결과 집합을 반환 한다. 이것은 T0에서 Tn까지의 UNION ALL이다.

• 정리하자면 다음과 같다.
  • 먼저 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인을 시작한다.
  • 이어서 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면 즉, 더 조인할 수 없으면 지금까지 만 들어진 결과 집합을 모두 합하여 리턴한다.

셀프 조인

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

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

예제

• 자신과 상위, 차상위 관리자를 같은 줄에 표시하라.
  
• 셀프 조인은 동일한 테이블(사원)이지만 [그림 II-2-11]과 같이 개념적으로는 두 개의 서로 다른 테이블(사원, 관리자)을 사용하는 것과 동일하다.
• 동일 테이블을 다른 테이블인 것처럼 처리하기 위해 테이블 별칭을 사용한다.
  • 여기서는 E1(사원), E2(관리자) 테이블 별칭을 사용하였다.
  • 차상위 관리자를 구하기 위해서 E1.관리자 = E2.사원 조인 조건을 사용 한다.
• 셀프 조인을 이용한 SQL문은 다음과 같다.

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1, 사원 E2
WHERE E1.관리자 = E2.사원
ORDER BY E1.사원;

• 내부 조인(Inner Join)을 사용할 경우 자신의 관리자가 존재하지 않는 경우에는 관리자(E2) 테이블에서 조인할 대상이 존재하지 않기 때문에 해당 데이터는 결과에서 누락된다.
  • 이를 방지하기 위해서는 아우터 조인을 사용해야 한다.

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1 LEFT OUTER JOIN 사원 E2
ON (E1.관리자 = E2.사원)
ORDER BY E1.사원;