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

1.   계층형 질의

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

 

-       계층형 데이터란?

n  동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터.
ex) 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재.

n  엔티티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생

n  순환관계 데이터 모델
ex) 조직, 사원, 메뉴 등

가.  Oracle 계층형 질의

가상 칼럼	설명
LEVEL	루드 데이터이면 1, 그 하위 데이터이면 2이다. 리프(Leaf) 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF	전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0이다.
CONNECT_BY_ISCYCLE	전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용할 수 있다.

-       START WITH 절 (엑세스)
계층 구조 전개의 시작 위치를 지정하는 구문.
즉, 루트 데이터 지정

-       CONNECT BY 절 (조인)
다음에 전개될 자식 데이터를 지정하는 구문.
자식 데이터는 CONNECT BY절에 주어진 조건 만족

-       PRIOR
CONNECT BY절에 사용되며, 현재 읽은 칼럼 지정
* PRIOR 자식 = 부모형태시, 자식 데이터에서 부모 데이터(자식->부모)방향으로 전재하는 순방향 전개
* PRIOR 부모 = 자식형태시, 부모 데이터에서 자식 데이터(부모->자식)방향으로 전개하는 역방향 전개

-       NOCYCLE
데이터 전개시, 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 사이클(Cycle)형성이라 한다.
사이클이 발생한 데이터는 런타임 오류 발생

n  NOCYCLE 추가 : 사이클이 발생한 이후의 데이터는 전개하지 않음
- ORDER SIBLINGS BY : 형제 노드(동일 LEVLEL) 사이에서 정렬 수행
- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출(필터링)

함수	설명
SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다. 사용법 : SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
CONNECT_BY_ROOW	현재 전개할 데이터의 루트 데이터를 표시한다. 단항 연산자이다. 사용법 : CONNECT_BY_ROOT 칼럼

 

나.  SQL Server 계층형 질의

-       SQL Server 2000 버전까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았다.

-       조직도 처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 while 루프 문에서 임시 테이블을 사용하는 등 (순수한 쿼리가 아닌) 프로그램 방식으로 전개하였다.

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

 

2.   셀프 조인

-       셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다.
FROM 절에 동일 테이블이 두 번 이상 나타난다.

-       동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 

 식별을 위해 반드시 테이블 별칭(Alias) 사용한다.

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

-       이외 사항은 조인과 동일하다.