1. 계층형 질의
테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query) 사용.
계층형 데이터란?
- 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터.
예를들면, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고 조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재. - 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생.
- 순환관계 데이터 모델의
예) 조직, 사원, 메뉴 등
▼ ［그림 Ⅱ-2-6］: 사원에 대한 순환관계 데이터 모델 표현
(2) 계층형 구조 : A의 하위 사원은 B, C이고 B 밑에는 하위 사원이 없고 C의 하위 사원은 D, E 가 있음
(3) 샘플 데이터 : (2) 계층형 구조를 데이터로 표현한 것
※ 계층형 데이터 조회는 DBMS 벤더와 버전에 따라 다른 방법으로 지원.
가. Oracle 계층형 질의
Oracle은 계층형 질의를 지원하기 위해서 ［그림 Ⅱ-2-7］과 같은 계층형 질의 구문을 제공
- START WITH 절 (액세스)
계층 구조 전개의 시작 위치를 지정하는 구문. 즉, 루트 데이터 지정
- CONNECT BY 절 (조인)
다음에 전개될 자식 데이터를 지정하는 구문
자식 데이터는 CONNECT BY절에 주어진 조건 만족
- PRIOR
CONNECT BY절에 사용되며, 현재 읽은 칼럼 지정
PRIOR 자식 = 부모 형태 시, 자식 데이터에서 부모 데이터(자식 → 부모) 방향으로 전개하는 순방향 전개
PRIOR 부모 = 자식 형태 시, 부모 데이터에서 자식 데이터(부모 → 자식) 방향으로 전개하는 역방향 전개
- NOCYCLE
데이터 전개 시, 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 사이클(Cycle) 형성이라함
사이클이 발생한 데이터는 런타임 오류 발생
☞ NOCYCLE 추가 : 사이클이 발생한 이후의 데이터는 전개하지 않음
- ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬 수행
- WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)
※ 확인해 보자!!
SELECT LEVEL,LPAD(' ', 4 * (LEVEL-1))||EMPNO||':'||ENAME 사원,MGR 관리자,CONNECT_BY_ISLEAF IsLF
FROM SCOTT.EMP -- WHERE ENAME <>'FORD'
START WITH EMPNO = '7369' CONNECT BY PRIOR MGR = EMPNO
Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column) 제공
▼ 예제 1 -［그림 Ⅱ-2-6］의 (3)샘플 데이터를 계층형 질의 구문을 이용해서 조회
| SQL | 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 |
결과 데이터를 들여쓰기 하기 위해서 LPAD 함수 사용
관리자 → 사원 방향을 전개이기 때문에 순방향 전개
▼［그림 Ⅱ-2-8］은 계층형 질의에 대한 논리적인 실행 모습
▼ 예제 2 - 사원 'D'로부터 자신의 상위관리자를 찾는 역방향 전개의 예
| SQL | 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 |
- 역방향 전개이기 때문에 하위 데이터에서 상위 데이터로 전개
- 리프 데이터는 A이다. 루트 및 레벨은 전개되는 방향에 따라 반대가 됨
▼［그림 Ⅱ-2-8］은 계층형 질의에 대한 논리적인 실행 모습
Orcle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 ［표 Ⅱ-2-3］과 같은 함수 제공
▼ 예제 3 - SYS_CONNECT_BY_PATH, CONNECT_BY_ROOT를 사용한 예
| SQL | 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 |
- 루트사원은 모두 A
∵ START WITH를 통해 추출된 루트 데이터가 1건 이기 때문 - 경로는 루트로부터 현재 데이터까지의 경로 표시
예를 들어, D의 경로는 A → C → D
나. SQL Server 계층형 질의
- SQL Server 2000 버전까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았음
- 조직도처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등 (순수한 쿼리가 아닌) 프로그램 방식으로 전개
- SQL Server 2005 버전부터는 하나의 질의로 원하는 결과를 얻을 수 있게 됨
■ 예제. Northwind 데이터베이스에 접속하여 Employees 테이블의 데이터 조회
USE NORTHWIND GO
SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES GO
**********************************************************************************
Emp~ID LastName FirstName ReportsTo
------ ------------ --------- ---------
1 Davolio Nancy 2
2 Fulle Andrew NULL
3 Leverling Janet 2
4 Peacock Margaret2
5 Buchanan Steven 2
6 Suyama Michael 5
7 King Robert 5
8 Callahan Laura 2
9 Dodsworth Anne 5
(9개 행 적용됨)
ReportsTo 칼럼 : 상위 사원에 해당하며 EmployeeID 칼럼과 재귀적 관계를 맺고 있음
EmployeeID가 2인 Fuller 사원을 살펴보면, ReportsTo 칼럼 값이 NULL이므로 계층 구조의 최상위에 있음을 알 수 있음
CTE(Common Table Expression)를 재귀 호출함으로써
Employees 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 결과는 다음과 같음
WITH EMPLOYEES_ANCHOR AS
( SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL
FROM EMPLOYEES
WHERE REPORTSTO IS NULL /* 재귀 호출의 시작점 */
UNION ALL
SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1
FROM EMPLOYEES_ANCHOR A, EMPLOYEES R
WHERE A.EMPLOYEEID = R.REPORTSTO )
SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO FROM EMPLOYEES_ANCHOR GO
**********************************************************************************
Level Emp~ID LastName FirstName ReportsTo
----- ------ ------------ --------- ---------
0 2 Fuller Andrew NULL
1 1 Davolio Nancy 2
1 3 Leverling Janet 2
1 4 Peacock Margaret 2
1 5 BuchananSteven 2
1 8 Callahan Laura 2
2 6 Suyama Michael 5
2 7 King Robert 5
2 9 Dodsworth Anne 5
(9개 행 적용됨)
WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 쿼리 두 개를 결합
둘 중 위에 있는 쿼리를 ‘앵커 멤버’(Anchor Member)라고 하고,
아래에 있는 쿼리를 ‘재귀 멤버’(Recursive Member)라고 함
▼ 재귀적 쿼리의 처리 과정
1. CTE 식을 앵커 멤버와 재귀 멤버로 분할
2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0) 생성
3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버 실행
4. 빈 집합이 반환될 때까지 3단계 반복
5. 결과 집합을 반환한다. 이것은 T0에서 Tn까지의 UNION ALL
※ 정리
1. 앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인 시작
2. 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면
(즉, 더 조인할 수 없으면)
지금까지 만들어진 결과 집합을 모두 합하여 리턴
▼［그림 Ⅱ-2-10］에 있는 조직도를 쿼리로 출력했을 때, 대부분 사용자는 아래와 같은 결과를 예상
EmployeeID ManagerID (보기 편하도록 각 로우 앞쪽에 자신의 레벨만큼 빈칸 삽입)
----------------- ---------
1000 NULL
1100 1000
1110 1100
1120 1100
1121 1120
1122 1120
1200 1000
1210 1200
1211 1210
1212 1210
1220 1200
1221 1220
1222 1220
1300 1000
▼ 아래에 t_emp 데이터의 최상위부터 시작해 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리와 그 결과이다.
WITH T_EMP_ANCHOR AS
( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL
FROM T_EMP
WHERE MANAGERID IS NULL /* 재귀 호출의 시작점 */
UNION ALL
SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1
FROM T_EMP_ANCHOR A, T_EMP R
WHERE A.EMPLOYEEID = R.MANAGERID )
SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID FROM T_EMP_ANCHOR GO
***********************************************************************************************
Level EmployeeID ManagerID
----- ----------- -----------
0 1000 NULL
1 1100 1000
1 1200 1000
11300 1000
2 1210 1200
2 1220 1200
3 1221 1220
3 1222 1220
3 1211 1210
3 12121210
2 1110 1100
2 1120 1100
3 1121 1120
3 1122 1120
(14개 행 적용됨)
보다시피, 계층 구조를 단순히 하위 방향으로 전개했을 뿐 ［그림 Ⅱ-2-10］에 있는 조직도와는 많이 다른 모습
앞서 보았듯이, CTE 재귀 호출로 만들어낸 계층 구조는 실제와 다른 모습으로 출력
☞ 따라서 조직도와 같은 모습으로 출력하려면 order by 절을 추가해 원하는 순서대로 결과 정렬
▼ 실제 조직도와 같은 모습의 결과를 출력하도록, CTE에 Sort라는 정렬용 칼럼을 추가하고 쿼리 마지막에 order by 조건 추가(단, 앵커 멤버와 재귀 멤버 양쪽에서 convert 함수 등으로 데이터 형식을 일치시켜야 한다.)
WITH T_EMP_ANCHOR AS
( SELECT EMPLOYEEID, MANAGERID, 0 AS LEVEL, CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT
FROM T_EMP
WHERE MANAGERID IS NULL /* 재귀 호출의 시작점 */
UNION ALL
SELECT R.EMPLOYEEID, R.MANAGERID, A.LEVEL + 1, CONVERT(VARCHAR(1000), A.SORT + '/' + R.EMPLOYEEID) AS SORT
FROM T_EMP_ANCHOR A, T_EMP R
WHERE A.EMPLOYEEID = R.MANAGERID )
SELECT LEVEL, REPLICATE(' ', LEVEL) + EMPLOYEEID AS EMPLOYEEID, MANAGERID, SORT
FROM T_EMP_ANCHOR ORDER BY SORT GO
CTE 안에서 Sort 칼럼에 사번(=EmployeeID)을 재귀적으로 더해 나가면 정렬 기준으로 삼을 수 있는 값 생성
▼ 아래는 Sort 칼럼으로 정렬하여 출력한 결과로, ［그림 Ⅱ-2-10］에 있는 조직도의 모습과 일치
Level Emp~ID ManagerID Sort
----- -------- ---------- ----------------------
0 1000 NULL 1000
1 1100 1000 1000/1100
2 1110 1100 1000/1100/1110
2 1120 1100 1000/1100/1120
3 1121 1120 1000/1100/1120/1121
3 1122 1120 1000/1100/1120/1122
1 1200 1000 1000/1200
2 1210 1200 1000/1200/1210
3 1211 1210 1000/1200/1210/1211
3 1212 1210 1000/1200/1210/1212
2 1220 1200 1000/1200/1220
3 1221 1220 1000/1200/1220/1221
3 1222 1220 1000/1200/1220/1222
1 1300 1000 1000/1300
(14개 행 적용됨)
※ 정리
가상의 Sort 칼럼을 추가해 정렬하는 게 아쉽기는 하지만,
SQL Server에서 계층 구조를 실제 모습대로 출력하려면 현재(2005, 2008 버전 기준)로서는 감수해야 함
2. 셀프 조인
- 셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다.
∴ FROM 절에 동일 테이블이 두 번 이상 나타난다.
- 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias) 사용
- 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별
- 이외 사항은 조인과 동일
■ 셀프 조인에 대한 기본적인 사용법
SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...
FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2 => FROM 테이블1 ALIAS명1, 테이블1 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
계층형 질의에서 살펴보았던 사원이라는 테이블 속에는 사원과 관리자가 모두 하나의 사원이라는 개념으로 동일시하여 같이 입력
문제 : “ 자신과 상위, 차상위 관리자를 같은 줄에 표시하라. ”
☞ 이 문제를 해결하기 위해서는 FROM 절에 사원 테이블을 두 번 사용 (셀프조인 사용)
▼ 문제 해결
- 셀프 조인은 동일한 테이블(사원)이지만 ［그림 Ⅱ-2-11］과 같이 개념적으로는 두 개의 서로 다른 테이블(사원, 관리자)을 사용하는 것과 동일
- 동일 테이블을 다른 테이블인 것처럼 처리하기 위해 테이블 별칭 사용
☞ 여기서는 E1(사원), E2(관리자) 테이블 별칭사용 - 차상위 관리자를 구하기 위해서 E1.관리자 = E2.사원 조인 조건 사용
▼ 셀프 조인을 이용한 SQL문
SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자
FROM 사원 E1, 사원 E2
WHERE E1.관리자 = E2.사원
ORDER BY E1.사원
사원 관리자 차상위_관리자
---- ------ -------------
B A
C A
D C A
E C A
- 자신과 자신의 직속 관리자는 동일한 행에서 데이터를 구할 수 있으나 차상위 관리자는 바로 구할 수 없음
- 차상위 관리자를 구하기 위해서는 자신의 직속 관리자를 기준으로 사원 테이블과 한번 더 조인(셀프 조인) 수행 필요
- 결과 표시를 위해 SELECT절에 2개의 ‘관리자’ 칼럼 사용
- 한 명은 자신의 직속 관리자(E1.관리자)이고 다른 한 명은 자신의 차상위 관리자(E2.관리자)
- 결과를 보면, B와 C의 관리자는 A이고 차상위 관리자는 없음
- D와 E의 관리자는 C이고 차상위 관리자는 A
- 결과에서 A에 대한 정보는 누락
- 내부 조인(Inner Join)을 사용할 경우 자신의 관리자가 존재하지 않는 경우,
- 관리자(E2) 테이블에서 조인할 대상이 존재하지 않기 때문에 해당 데이터는 결과에서 누락
☞이를 방지하기 위해서는 아우터 조인 사용
▼ 아우터 조인을 추가한 SQL문
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
☞ 아우터 조인을 사용해서 관리자가 존재하지 않는 데이터까지 모두 결과 표시
✅ REFERENCE
제 3절 계층형 질의와 셀프 조인
1. 계층형 질의 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarch...
blog.naver.com
'# 4. 자격증 > SQLD' 카테고리의 다른 글
| SQL 자격검정 실전문제 # 문제집 요점정리_과목 1 (0) | 2021.11.13 |
|---|---|
| SQLD 자격증 # SQL 활용(과목2)_서브쿼리 (0) | 2021.11.12 |
| SQLD 자격증 # SQL 활용(과목2)_집합 연산자 (0) | 2021.11.12 |
| SQLD 자격증 # SQL 활용(과목2)_표준 조인 (0) | 2021.11.12 |
| SQLD 자격증 # SQL 기본(과목2)_조인 (0) | 2021.11.11 |