SQLD / 과목 2 / 제 2장 SQL 활용 / 계층형 질의와 셀프 조인, 서브쿼리

1. 계층형 질의와 셀프 조인

1) 계층형 질의(Hierarchical Query)

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용, 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말함

 

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 절에 주어진 조건을 만족(JOIN)
• PRIOR
  CONNECT BY 절에 사용되며 현재 읽은 칼럼을 지정
  PRIOR 자식 = 부모 (자식 데이터 -> 부모 데이터 방향으로 순방향 전개)
  PRIOR 부모 = 자식 (부모 데이터 -> 자식 데이터 방향으로 역방향 전개)
• NOCYCLE
  데이터를 전개하면서 이마 나타났던 동일한 데이터가 전개 중에 다시 나타나는 것을 CYCLE이 형성되었다고 하는데
  NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다
• ORDER SIBLINGS BY
  형제 노드(동일 LEVEL) 사이에서 정렬 수행
• WHERE
  모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출(필터링)

 

Oracle 계층형 질의 가상 칼럼

• LEVEL
  루트 데이터면 1, 그 하위 데이터면 2. 리프(leaf) 데이터까지 1씩 증가
• CONNECT_BY_ISLEAF
  전개 과정에서 해당 데이터가 리프 데이터면 1, 아니면 0
• CONNECT_BY_ISCYCLE
  CYCLE 옵션을 사용했을 때만 사용 가능하며 전개 과정에서 자식을 갖는데, 해당 데이터가 존재하면 1, 아니면 0
  여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터

 

Oracle 계층형 질의 함수

• SYS_CONNECT_BY_PATH
  루트 데이터부터 현재 전개할 데이터까지 경로
  SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
• CONNECT_BY_ROOT
  현재 전개할 데이터의 루트 데이터를 표시. 단항 연산자
• CONNECT_BY_ROOT 칼럼

 

재귀 멤버(Recursive Member) 재귀적 처리 과정

1. CTE 식을 앵커 멤버와 재귀 멤버로 분리
2. 앵커 맴버를 실행하여 첫 번째 호출 또는 기본 결과 집합(TO)을 만든다.
3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버 실행
4. 빈 집합이 반환될 때까지 3단계 반복
5. 결과 집합을 반환. T0에서 Tn까지의 UNION ALL이다

2) 셀프 조인

동일 테이블 사이의 조인. FROM절에 동일 테이블이 두 번 이상 나타나기 때문에 별칭(ALIAS) 사용

---

2. 서브쿼리(Subquery)

하나의 SQL문 안에 포함되어 있는 또 다른 SQL문을 만함

서브쿼리는 메인쿼리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.

 

서브쿼리 주의사항

• 서브쿼리를 괄호로 감싸서 사용
• 단일 행(Single Row) 또는 복수 행(Multiple Row) 비교 연산자와 함께 사용 가능
  단일 행 비교 연산자는 서브쿼리의 결과가 반드시 한 건 이하
  복수 행 비교 연산자는 서브쿼리 결과 건수와 상관없다.
• ORDER BY 사용 불가

 

서브쿼리를 SQL문에서 사용 가능한 곳

• SELECT 절
• FROM 절
• WHERE 절
• HAVING 절
• ORDER BY 절
• INSERT문의 VALUES 절
• UPDATE 문의 SET 절

1) 단일 행 서브쿼리

서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리( =, <, <=, >, >=, <> )

2) 다중 행 서브쿼리

서브쿼리의 실행 결과가 여러 건인 서브쿼리( IN, ALL, ANY, SOME, EXISTS )

3) 다중 칼럼 서브쿼리

서브쿼리의 실행 결과로 여러 칼럼을 반환

메인쿼리 조건절에 여러 칼럼을 동시에 비교 가능

비교하고자 하는 칼럼 개수와 위치가 동일해야 한다.

4) 연관(Correlated) 서브쿼리

서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태

메인쿼리가 먼저 수행되어 읽힌 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용

5) 비연관(Un- Correlated) 서브쿼리

메인쿼리 칼럼을 가지고 있지 않는 형태

메인쿼리 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적

6) 그 밖에 위치에서 사용하는 서브쿼리

SELECT절

스칼라 서브쿼리(Scalar Subquery)

한 행, 한 칼럼(1 Row 1 Column)만을 반환하는 서브쿼리

 

FROM절

인라인 뷰(Inline View)

SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 DB에 해당 정보가 저장되지 않는다.

 

HAVING절

그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해 사용

7) 뷰(View)

실제 데이터를 가지고 있지 않다.

테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)이라고도 함

 

뷰 사용의 장점

• 독립성
  테이블 구조가 변경되어도 응용 프로그램은 변경하지 않아도 됨
• 편리성
  복잡한 질의를 단순하게 작성할 수 있다.
• 보안성
  직원의 급여정보와 같이 숨기고 싶은 정보는 뷰를 생성할 때 해당 칼럼을 빼고 생성하여 사용자에게 정보를 감춤