[SQLD] SQL 활용 2-3. 계층형 질의와 셀프 조인

1. 계층형 질의

: 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 사용한다.

 

계층형 데이터

: 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

 

ex

: 조직, 사원, 메뉴

사원에 대한 순환관계 데이터 모델

가. 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	사이클이 발생한 이후의 데이터는 전개하지 않는다.
ORDER SIBLINGS BY	형제 노드(동일 LEVEL) 사이에서 정렬을 수행
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)

 

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

 

(1) 샘플데이터를 계층형 질의 구문을 이용해서 조회한 것이다.

[결과 데이터를 들여쓰기 하기 위해서 LPAD 함수 사용]

 

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

리프 데이터는 B, D, E이다. 관리자 → 사원 방향을 전개하기 때문에 순방향 전개

 

 

(2) 사원 'D'로부터 자신의 상위관리자를 찾는 역방향 전개

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

리프 데이터는 A이고, 루트 및 레벨은 전개되는 방향에 따라 반대기 때문에 역방향 전개

 

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

(2)

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

루트사원은 모두 A이다. 경 로는 루트로부터 현재 데이터까지의 경로를 표시

D의 경로는 : A -> C -> D

---

2. 셀프 조인

: 동일 테이블 사이의 조인을 말한다. 따라서 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;

사원과 관리자가 모두 하나의 사원이라는 개념으로 동일시하여 같이 입력되어 있다.

 

(2) 자신과 상위, 차상위 관리자를 같은 줄에 표시하라.

FROM 절에 사원 테이블을 두 번 사용
-> E1(사원), E2(관리자) 테이블 별칭을 사용
차상위 관리자를 구하기 위해서 E1.관리자 = E2.사원 조인 조건을 사용

 

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

 

차상위 관리자를 구하기 위해서는 자신의 직속 관리자를 기준으로 사원 테이블과 한번 더 조인(셀프 조인) 수행
내부 조인(Inner Join)을 사용할 경우 자신의 관리자가 존재하지 않는 경우에는 관리자(E2) 테이블에서 조인할 대상이 존재하지 않기 때문에 해당 데이터는 결과에서 누락 된다.
이를 방지하기 위해 아우터 조인을 사용

 

(3) 아우터 조인 사용

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

아우터 조인을 사용하면 관리자가 존재하지 않는 데이터까지 모두 결과에 표시