💡 계층형 질의 (Hierarchical Query)
- 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 계층형 질의(Hierarchical Query)를 사용한다.
- 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다. (예: 조직, 사원, 메뉴 등)
계층형 데이터 :동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터
◽ Oracle 계층형 질의
-
START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트데이터를 지정한다.
-
CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문이다.
자식 데이턴는 CONNECT BY 절에 주어진 조건을 만족해야한다. -
PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
-
PRIOR 자식 = 부모
- 계층구조에서 부모 ➡ 자식 방향으로 순방향 전개를 한다.
-
PRIOR 부모 = 자식
- 계층구조에서 자식 ➡ 부모 방향으로 역방향 전개를 한다.
-
-
NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전재 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다.
사이클이 발생한 데이터는 런타임 오류가 발생한다. 하지만 NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다. -
ORDER SIBLINGS BY : 형제 노드(동일 레벨) 사이에서 정렬을 수행한다.
-
WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출오라클은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column)을 제공한다.
오라클은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column)을 제공한다.
- 순방향 전개
SELECT LEVEL,
LPAD(' ' , 4 * (LEVEL -1)) || EMPNO 사원,
MGR 관리자, CONNECT_BY_ISLEAF ISLEAF
FROM EMPSTART WITH MGR IS NULLCONNECT BY PRIOR EMPNO = MGR;CONNECT BY PRIOR EMPNO = MGR 이 구문은, PRIOR 사원번호 = 관리자 라는 형태이므로 순방향 전개를 펼치게 된다.
CONNECT_BY_ISLEAF 라는 가상 칼럼을 사용하여, 해당 데이터가 리프데이터(잎노드)인지 구별하고 있다.
❓ LPAD 함수: 계층형 조회 결과를 명확히 하기 위해 사용 (LEVEL 값을 이용하여 결과 데이터 정렬)
- 역방향 전개
SELECT LEVEL,
LPAD(' ', 4 * (LEVEL-1)) || 사원 사원,
관리자,
CONNECT_BY_ISLEAF ISLEAF
FROM 사원
START WITH 사원 = 'D'
CONNECT BY PRIOR 관리자 = 사원;
- 사원 ‘D’ 로부터 자신의 상위 관리자를 찾는 역방향 전개이다.
- 역방향 전개이기 때문에 하위 데이터에서 상위 데이터로 전개된다.
- D 는 루트 데이터이기 때문에 레벨이 1이며, D 의 상위 데이터인 C 는 레벨이 2, C 의 상위 데이터인 A 는 레벨 3 이다.
- 리프 데이터는 A
Oracle 은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해 [표 II-2-3] 과 같은 계층형 질의 함수를 제공한다.
◽ SQL Server 계층형 질의
SQL Server 2000 버전까지는 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등 순수한 쿼리가 아닌 프로그램 방식으로 전개해야 했다.
그러나 SQL Server 2005 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 되었다.
CTE(Common Table Expression)를 재귀 호출
먼저, 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인을 시작한다.
이어서, 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면
즉, 더 조인할 수 없으면 지금까지 만들어진 결과 집합을 모두 합하여 리턴한다.
CTE란?
-
SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문
하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있다. -
CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷하나,
CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있다.
◽ 셀프 조인(Self Join)
동일 테이블 사이의 조인을 말한다.
-
FROM 절에 동일 테이블이 두 번 이상 나타난다.
-
동일 테이블 사이의 조인을 수행하면 테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭 (ALIAS) 을 사용해야 한다.
SELECT A.EMPNO 사원번호, A.ENAME 사원명, B.ENAME 관리자명
FROM EMP A, EMP B
WHERE A.MGR = B.EMPNO사원이라는 테이블에는 사원과 관리자가 모두 하나의 사원 개념으로 동일시하여 같이 입력되어 있다.
이것을 이용해서 "자신과 상위, 차상위 관리자를 같은 줄에 표시하라"는 문제를 셀프 조인을 이용해서 풀 수 있다.
SELECT E1.ENAME 사원, E1.MGR 관리자, E2.MGR 차상위_관리자
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO
ORDER BY E2.MGR DESC, E1.MGR, E1.EMPNO
관리자가 존재하지 않는 데이터까지 모두 표시하려면 아래와 같이 해야한다. (left join 이용)
SELECT E1.ENAME 사원, E1.MGR 관리자, E2.MGR 차상위_관리자
FROM EMP E1 LEFT OUTER JOIN EMP E2
ON (E1.MGR = E2.EMPNO)
ORDER BY E2.MGR DESC, E1.MGR, E1.EMPNO✔ kdata 한국데이터진흥원에서 출간한 SQL 전문가 가이드 2013 Edition을 요약했습니다. 