SQLD - 과목2. (12) 계층형 질의와 셀프 조인

1. 계층형 질의 ( Hierarchical Query )

 

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

 

 

계층형 데이터란,

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

 

 

계층형 데이터

 

 

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

조직테이블에는 조직들 사이에 상위와 하위 조직 관계가 존재한다.

 

엔터티를 순한관계 데이터 모델로 설계할 경우, 계층형 데이터가 발생한다.

 

순환관계 데이터 모델의 예로는 조직, 사원, 메뉴 등이 있다.

 

 

 

계층형 구조에서 A의 하위 사원은 B, C 이고 / B 밑에는 하위 사원이 없고 / C의 하위 사원 D, E 가 있다.

 

계층형 구조를 데이터로 표현한 것이 샘플 데이터 이다.

계층형 데이터 조회는 DBMS 벤더와 버전에 따라 다른 방법으로 지원한다.

 

 

 

 

 

---

① ORACLE 계층형 질의

 

 

계층형 질의 구문

 

▷ START WITH 절

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

   루트 데이터를 지정한다.

 

 

 

▷ CONNECT BY 절

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

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

 

 

 

▷ PRIOR

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

 

   ' PRIOR 자식 = 부모 ' 형태를 사용하면 계층 구조에서 자식 데이터에서 부모 데이터 방향으로 전개하는 순방향을 전개한다.

    자식 → 부모

 

   ' PRIOR 부모 = 자식 ' 형태는 역방향 전개를 한다.

    부모 → 자식

 

 

 

▷ NOCYCLE

  : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클이 형성되었다 한다.

   사이클이 발생한 데이터는 런타임 오류가 발생한다.

   그렇지만 NOCYCLE 를 추가하면 사이클 발생 이후의 데이터는 전개하지 않는다.

 

 

 

▷ ORDER SIBLINGS BY

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

 

 

 

▷ WHERE

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

 

 

 

 

---

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

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

 

---

 

< 계층형 질의에 대한 논리적인 실행 모습 >

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

 

 

 

< 역방향 계층형 질의에 대한 논리적인 실행 모습 >

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

 

---

② SQL SERVER 계층형 질의

 

SQL Server 2015 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 되었다.

 

Northwind 데이터베이스에 접속한고, 테이블 데이터를 조회한다.

USE NORTHWIND GO
SELECT ..... FROM .......

 

SQL server 에서는 계층 구조를 실제 모습대로 출력하려면 가상의 Sort 칼럼을 추가해 정렬하는 감수를 해야 한다.

 

 

 

 

 

 

 

 

---

2. 셀프 조인 ( Self Join )

 

셀프 조인이란, 동일 테이블 사이의 조인을 말한다.

FROM 절에 동일 테이블이 두 번 이상 나타난다.

 

 

▷ 테이블 별칭 ALIAS

동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일해서, 식별을 위해 반드시 테이블 별칭을 사용해야 한다.

 

칼럼에도 모두 테이블 별칭을 사용해서, 어느 테이블의 칼럼인지 식별해줘야 한다.

 

 

이외는 JOIN 과 동일하다.

 

 

---

[ 셀프 조인 기본적인 사용법 ]

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

 

" 자신과 상위, 차상위 관리자를 같은 줄에 표시하라 " 를 해결하기 위해서는 FROM 절에 사원 테이블을 두 번 사용한다.

 

 

 

셀프 조인 개념

 

 

 

( 누락된 데이터 SQL문 )

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

B와 C의 관리자는 A 이고 차상위 관리자는 없다. 

D와 E의 관리자는 C 이고 차상위 관리자는 A 이다.

 

결과에서 A 에 대한 정보가 누락되었다.

 

 

INNER JOIN 을 사용할 경우 관리자가 존재하지 않을 경우 해당 데이터는 결과에서 누락된다.

이를 방지하기 위해 OUTER JOIN 을 사용해야 한다.

 

 

 

( OUTER JOIN 을 사용한 SQL문 )

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

관리자가 존재하지 않는 데이터까지 모두 결과에 표시 되었다.

 

 

 

 

 

 

 

---