[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #3

<목차>

• 계층형 질의

  • Oracle 계층형 질의

• 셀프 조인

 

<< 계층형 질의와 셀프 조인 >>

 

1. 계층형 질의

* 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다.

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

ex) 사원 테이블에서는 사원들 사이에 상위 사원(관리자)와 하위 사원 관계가 존재.

ex) 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재.

* Entity를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다. 아래의 그림을 참고하자.

계층형 데이터

 

1-1. Oracle 계층형 질의

A. 기본 형태

계층형 질의 구문

* START WITH 절

  : 계층 구조 전개의 시작 위치를 지정하는 구문.

  : ROOT 데이터를 지정한다. (접근)

 

* CONNECT BY 절

  : 다음에 전개될 자식 데이터를 지정하는 구문.

  : 자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 한다. (JOIN)

 

* PRIOR

  : CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정.

  : 'PRIOR 자식 = 부모 형태' 를 사용하면 해당 노드의 자식이 어떤 노드의 부모 노드인지를 찾으므로 부모 --> 자식 방향으로 전개하는 순방향 전개.

  : 'PRIOR 부모 = 자식 형태' 를 사용하면 반대로 해당 노드의 부모가 어떤 노드의 자식 노드인지를 찾으므로 자식 --> 부모 방향으로 전개하는 역방향 전개.

 

* NO CYCLE

  : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타나면 'CYCLE이 형성되었다' 라고 표현.

  : CYCLE이 발생한 데이터는 런타임 오류가 발생하나, NOCYCLE을 추가하면 CYCLE이 발생한 이후의 데이터는 전개하지 않는다.

 

* ORDER SIBLINGS BY

  : 형제 노드(동일 LEVEL에 있는 노드) 사이에서 정렬을 수행한다.

 

* WHERE

  : 모든 전개를 수행한 후에 지장된 조건을 만족하는 데이터만 추출한다. (필터링)

 

B. Oracle은 계층형 질의를 사용할 때 아래와 같은 가상 칼럼을 제공한다.

계층형 질의에서 사용되는 가상 칼럼

 

* 예시 - 샘플 데이터의 계층형 질의 구문을 이용해서 데이터를 조회 예제

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF 
FROM 사원 
START WITH 관리자 IS NULL 
CONNECT BY PRIOR 사원 = 관리자;
 
/*
LEVEL 사원    관리자  ISLEAF 
----- ---------- ------ ------ 
1     A                 0 
2     B       A      1 
2      C       A      0 
3      D  C      1 
3      E  C      1
*/

* SELECT 절

   ---> LEVEL : ROOT 데이터면 1, 하위 데이터면 숫자가 1씩 증가.

   ---> CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면 1, 아니면 0 (자식 데이터가 없으면 리프 노드이다.)

 

* START WITH 절 (ROOT 데이터 지정)

   ---> 관리자가 NULL 값인 데이터가 ROOT 데이터이다.

 

* CONNECT BY (다음 데이터를 지정)

   ---> PRIOR 사원 = 관리자 : 자식 데이터와 부모 데이터를 연결한다.

   ---> 관리자와 같은 값의 사원을 조회하게끔 표현한다. (부모 데이터를 통해 자식 데이터 조회 : 순방향 전개)

 

순방향 계층형 질의 결과의 논리적인 모습

 

 

* 예시 - 사원 'D'로부터의 자신의 상위 관리자를 찾는 역방향 전개 예제

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

* SELECT 절

   ---> LEVEL : ROOT 데이터면 1, 하위 데이터면 숫자가 1씩 증가.

   ---> CONNECT_BY_ISLEAF : 해당 데이터가 리프 데이터면 1, 아니면 0 (자식 데이터가 없으면 리프 노드이다.)

 

* START WITH 절 (ROOT 데이터 지정)

   ---> 사원이 'D'인 노드가 ROOT 데이터.

 

* CONNECT BY (다음 데이터를 지정)

   ---> PRIOR 관리자 = 사원 : 자식 데이터와 부모 데이터를 연결한다.

   ---> 사원과 같은 값의 관리자를 조회하게끔 표현한다. (자식 데이터를 통해 부모 데이터 조회 : 역방향 전개)

 

역방향 계층형 질의 결과의 논리적인 모습

 

 

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

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

 

* 예시

SELECT CONNECT_BY_ROOT 사원 루트사원, SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자 
FROM 사원 
START WITH 관리자 IS NULL 
CONNECT BY PRIOR 사원 = 관리자;
 
/*
루트사원 경로      사원 관리자 
------- --------- ---- ----- 
A       /A        A    
A       /A/B      B    A 
A       /A/C      C    A 
A       /A/C/D    D    C 
A       /A/C/E    E    C
*/

 

2. 셀프 조인

* 동일 테이블 사이의 조인 --> FROM 절에 동일 테이블이 두 번 이상 나타난다.

* 반드시 각 테이블마다 별칭(ALIAS)을 사용해야 한다.

* 칼럼에도 모두 테이블 별칭을 사용해야 한다.

 

A. 기본 형태

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

 

B. 예시

   : 위에서 본 계층형 질의로 해결했던 문제를 셀프 조인으로 해결하면 다음과 같다.

셀프 조인 개념

* 동일한 테이블을 다른 테이블로 구분하기 위해 테이블 별칭(ALIAS)을 사용한다.

* 사원 테이블 : E1, 관리자 테이블 : E2로 지정.

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1, 사원 E2 
WHERE E1.관리자 = E2.사원 
ORDER BY E1.사원;
 
/*
사원 관리자 차상위_관리자 
---- ------ ---------- 
B    A 
C    A 
D    C      A 
E    C      A 
*/

---> 결과에서 사원 A의 데이터가 없다.

   : 사원 테이블(E1)에서 사원 A의 관리자 데이터가 NULL이기 때문에 관리자 테이블(E2) 테이블에서 조인할 대상이 없다. (INNER JOIN을 사용했기 때문)

   : 결과에서 해당 데이터는 누락됨. NULL에 대한 정보도 출력하기 위해서는 OUTER JOIN을 사용한다.

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1 LEFT OUTER JOIN 사원 E2 
ON (E1.관리자 = E2.사원) 
ORDER BY E1.사원;
 
/*
사원 관리자 차상위_관리자 
---- ------ ---------- 
A
B    A 
C    A 
D    C      A 
E    C      A 
*/

 

 

출처

http://www.dbguide.net/db.db?cmd=view&boardUid=148202&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1

데이터 전문가 지식포털 DBGuide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.