[자격증 / SQLD] SQL 활용(2)_계층형 질의, 서브쿼리

 

DATA ON-AIR에서 23번 계층형 질의와 셀프 조인, 24번 서브쿼리를 요약 정리한 내용이다.

 

 

 

---

 

 

 

계층형 질의(Hierarchical Query)

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

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

 

 

Oracle 계층형 질의

Oracle은 계층형 질의(Hierarchical Query)를 지원하기 위해서 [그림 Ⅱ-2-7]과 같은 계층형 질의 구문을 제공한다.

• START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문 (루트 데이터를 지정 -> 액세스)
• CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문
• PRIOR: CONNECT BY절에서 사용되며, 현재 읽은 칼럼을 지정
  • PRIOR 자식 = 부모 : 부모 데이터에서 자식 데이터(부모 -> 자식) 방향으로 전개하는 순방향 전개
  • PRIOR 부모 = 자식 : 자식 데이터에서 부모 데이터(자시 -> 부모) 방향으로 전개하는 역방향 전개
• NOCYCLE : 동일한 데이터가 전개되지 않음 (이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 오류 발생)
• ORDER SIBLINGS BY : 형제 노드 사이에서 정렬을 수행
• WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출 (필터링)

 

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

Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼(Pseudo Column)을 제공한다.

가상 칼럼	설명
LEVEL	루트 데이터(가장 상위 레벨)이면 1, 그 하위 데이터면 2 리프(Leaf) 데이터까지 1씩 증가한다.
CONNECT_BY_ISLEAF	전개 과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 아니면 0 CYCLE 옵션을 사용했을 때만 사용할 수 있다.
SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시 단항 연산자

 

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

Orcle은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해서 다음과 같은 함수(Function)를 제공한다.

함수	설명
SYS_CONNECT_BY_PATH	루트 데이터부터 현재 전개할 데이터까지의 경로를 표시
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터를 표시 단항 연산자

 

 

 

셀프 조인

• 한 테이블 내 두 칼럼이 연관 관계가 있을 때 동일 테이블 사이의 조인을 말한다.
• FROM 절에 동일 테이블이 두 번 이상 나타난다.
• 반드시 테이블 별칭(Alias)를 사용해야 한다.

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

 

 

 

서브쿼리(Subquery)

서브쿼리(Subquery)란 하나의 SQL문안에 포함되어 있는 또 다른 SQL문을 말한다. 알려지지 않은 기준을 이용한 검색을 위해 사용한다.

 

• 서브쿼리를 괄호로 감싸서 사용한다.
• 서브쿼리는 메인커리의 칼럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 칼럼을 사용할 수 없다.
  • 질의 결과에 서브쿼리의 칼럼을 표시해야 한다면 조인방식으로 변환하거나 함수, 칼라 서브 쿼리 등을 사용해야 한다.
• 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다.
  • 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하이어야 한다.
  • 복수 행 비교 연산자는 서브쿼리의 결과 건수와 상관 없다.
• 서브쿼리에서는 ORDER BY를 사용하지 못한다.
  • ORDER BY절은 SELECT 절에서 오직 한 개만 올 수 있기 때문에 ORDER BY절은 메인쿼리의 맨 마지막에 위치해야 한다.
• SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT문의 VALUES 절, UPDATE문의 SET 절에 사용 가능

 

 

서브쿼리의 종류

서브쿼리의 종류는 동작하는 방식이나 반환되는 데이터의 형태에 따라 분류할 수 있다.

 

+ 동작하는 방식에 따른 서브쿼리 분류

서브쿼리 종류	설명
Un-Correlated(비연관) 서브쿼리	- 서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태의 서브쿼리 - 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용
Correlated(연관) 서브쿼리	- 서브쿼리가 메인쿼리 칼럼을 가지고 있는 형태의 서브쿼리 - 일반적으로 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 주로 사용 - EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용됨

 

+ 반환되는 데이터의 형태에 따른 서브쿼리 분류

서브쿼리 종류	설명
Single Row 서브쿼리 (단일 행 서브쿼리)	- 서브쿼리의 실행 결과가 항상 1건 이하인 서브쿼리 - 단일 행 서브쿼리는 단일 행 비교 연산자와 함께 사용됨 - 단일 행 비교 연산자 : = , < , <= , > , >= , <>
Multi Row 서브쿼리 (다중 행 서브쿼리)	- 서브쿼리의 실행 결과가 여러 건인 서브쿼리 - 다중 행 서브쿼리는 다중 행 비교 연산자와 함께 사용 - 다중 행 비교 연산자 : IN, ALL, ANY, SOME, EXISTS
Multi Column 서브쿼리 (다중 칼럼 서브쿼리)	- 서브쿼리의 실행 결과로 여러 칼럼을 반환 - 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있음 - 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 함 - ex) WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID)

 

+ 다중 행 서브쿼리의 비교 연산자

다중 행 연산자	설명
IN (서브쿼리)	서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다. (Multiple OR 조건)
비교연산자 ALL (서브쿼리)	서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다. ex) x > ALL (1, 2, 3, 4, 5) 라면 x > 5가 되어야 함
비교연산자 ANY (서브쿼리)	서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다. (ANY = SOME) ex) x > ANY (1, 2, 3, 4, 5) 라면 x > 1이면 됨
EXISTS (서브쿼리)	서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건을 하나라도 찾으면 검색을 중지한다. (속도가 빠름) 주로 참/거짓의 조건 판단용으로 사용한다.

-- EXISTS 사용 예시
SELECT PLAYER_NAME, HEIGHT, BACK_NO
FROM   PLAYER
WHERE  EXISTS (SELECT 1              
               FROM PLAYER
               WHERE BACK_NO = '15);

• EXISTS 연산자는 서브쿼리의 WHERE 조건절에 값이 존재하는지 여부만 중요하기 때문에, 서브쿼리의 SELECT절에 어떤 컬럼명이 와도 신경쓰지 않는다. 그래서 가독성을 위해 1이라는 상수값으로 작성하기도 한다.

 

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

+ 스칼라 서브쿼리(Scalar Subquery)

• 하나의 값을 반환하는 서브쿼리
  • 단일 행, 단일 칼럼(1 Row 1 Column)
  • 하나의 값을 반환한다는 점에서 함수(Function)의 특성을 가짐
  • 공집합을 반환하는 경우 NULL이 대응됨
• 칼럼을 쓸 수 있는 대부분의 곳에서 사용 가능
  • SELECT절, WHERE절, 함수의 인자, ORDER BY절, CASE절, HAVING절 등
• 스칼라 서브쿼리 대신 JOIN으로 동일한 결과 추출 가능

 

+ 인라인 뷰(Inline View)

• FROM 절에서 사용하는 서브쿼리
• 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용 가능 ( = 동적 뷰(Dynamic View))
• 인라인 뷰는 SQL문이 실행될 때만 암시적으로 생성되는 동적인 뷰 -> 데이터베이스에 해당 정보 저장 X

 

+ HAVING 절, UPDATE문의 SET절, INSERT문의 VALUES절에 서브쿼리 사용 가능

 

 

 

뷰(View)

• 테이블은 실제로 데이터를 가지고 있는 반면, 뷰(View)는 실제 데이터를 가지고 있지 않다. 
• 질의에서 뷰가 사용되면 뷰 정의를 참조해서 DBMS 내부적으로 질의를 재작성(Rewrite)하여 질의를 수행한다.
• 테이블이 수행하는 역할을 수행하기 때문에 가상 테이블(Virtual Table)이라고도 한다.  ( = 정적 뷰(Static View))

 

뷰(View)의 장점

독립성	테이블 구조가 변경 시, 뷰만 변경되고 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.
편리성	복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
보안성	직원의 급여 정보와 같이 숨기고 싶은 정보가 존재할 때, 민감한 정보를 제외하고 뷰를 생성하여 사용할 수 있다.

 

• 생성 : CREATE VIEW V_PLAYER_TEAM AS SELECT ... ;
• 삭제 : DROP VIEW V_PLAYER_TEAM;
• 뷰 확인 : SELECT * FROM USER_VIEWS;

 

인라인 뷰(Inline View)

• FROM절에서 사용되는 서브쿼리
• 실행 순간에만 임시적으로 생성되며 DB에는 저장되지 않음 ( = 동적 뷰, Dinamic View)
• 인라인 뷰의 SELECT문에서 정의된 칼럼은 메인 쿼리에서 사용 가능 (일반적으로는 메인 쿼리에서 사용 불가)

 

+ 인라인 뷰의 특징

메인 쿼리에서 EMP 테이블의 모든 컬럼을 쓸 수 없고, EMP 테이블에서 뽑아낸 EMPNO와 ENAME만 사용할 수 있다.

-- OK
SELECT EMPNO
FROM   (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);

-- ERROR 
SELECT MGR
FROM   (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);

 

 

 

 

---

[관련 글 보러가기]

 

• [자격증 / SQLD] SQL 기본(1)_TABLE과 명령어(DML, DDL, DCL, TCL)
• [자격증 / SQLD] SQL 기본(2)_WHERE 조건절(연산자)과 함수
• [자격증 / SQLD] SQL 기본(3)_GROUP BY, ORDER BY, 조인(JOIN)
• [자격증 / SQLD] SQL 활용(1)_표준 조인과 집합 연산자
• [자격증 / SQLD] SQL 활용(3)_그룹 함수와 윈도우 함수
• [자격증 / SQLD] SQL 활용(4)_절차형 SQL

 

---

 

[참고자료]

 

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod=list

SQL – DATA ON-AIR