고급 SQL 작성

(1) 뷰

1. 뷰(View)의 개념

- 뷰는 논리 테이블로서 사용자에게 테이블과 동일하다.

- 뷰와 같은 결과를 만들기 위해 조인 기능을 활용할 수 있으나, 뷰가 만들어져 있다면 사용자는 조인 없이 하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있다. 

 

2. 뷰의 특징

• 논리적 데이터 독립성 제공 : 데이터베이스에 영향을 주지 않고 애플리케이션이 원하는 형태로 데이터에 접근 가능
• 데이터 조작 연산 간소화 : 애플리케이션이 원하는 형태의 논리적 구조를 형성하여 데이터 조작 연산을 간소화 
• 보안 기능 제공 : 특정 필드만을 선택해 뷰를 생성할 경우 애플리케이션은 선택되지 않은 필드의 조회 및 접근 불가
• 뷰 변경 불가 : 뷰 정의는 ALTER 문을 이용하여 변경할 수 없음

 

3. 뷰 명령어

CREATE VIEW 뷰 이름 컬럼 목록 AS 데이터 조회 쿼리;

 

▼ 테이블 A 그대로

CREATE VIEW VW_A AS
SELECT *
	FROM A;

▼ 테이블 A 일부 컬럼

CREATE VIEW VW_B AS
SELECT 컬럼1, 컬럼2
	FROM A;

▼ 테이블A와 테이블B 조인 결과

CREATE VIEW VW_C AS
SELECT *
	FROM A, B
   WHERE A.컬럼1 = B.컬럼1;

 

(2) 인덱스

1. 인덱스 개념

- 인덱스는 데이터를 빠르게 찾을 수 있는 수단으로서, 테이블에 대한 조회 속도를 높여 주는 자료 구조

- 테이블의 특정 레코드 위치를 알려 주는 용도로 사용

- 인덱스는 자동으로 생성되지 않음

 

▼ 인덱스 종류

종류	설명
순서 인덱스	데이터가 정령된 순서로 생성되는 인덱스(B-tree 알고리즘)
해시 인덱스	해시 함수에 의해 직접 데이터에 키 값으로 접근하는 인덱스
비트맵 인덱스	각 컬럼에 적은 개수 값이 저장된 경우 선택하는 인덱스
함수기반 인덱스	수식이나 함수를 적용하여 만든 인덱스
단일 인덱스	하나의 컬럼으로만 구성한 인덱스
결합 인덱스	두 개 이상의 컬럼으로 구성한 인덱스
클러스터트 인덱스	기본 키 기준으로 레코드를 묶어서 저장하는 인덱스 저장 데이터의 물리적 순서에 따라 인덱스가 생성 특정 범위 검색 시 유리

 

3. 인덱스 조작

[1] 인덱스 생성

CREATE [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명);

UNIQUE -> 인덱스 걸린 컬럼에 중복 값을 허용하지 않음(생략 가능)

인덱스명 -> 생성하고자 하는 인덱스 테이블명

테이블명 -> 인덱스 대상 테이블명

컬럼명 -> 테이블의 특정 컬럼명

 

[2] 인덱스 삭제

DROP INDEX 인덱스명;

 

[3] 인덱스 변경

ALTER [UNIQUE] INDEX 인덱스명 ON 테이블명(컬럼명);

 

[4] 인덱스 스캔

1. 인덱스 범위 스캔 : 인덱스 루트 블록에서 리프블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔
2. 인덱스 전체 스캔 : 수직적 탐색 업싱 인덱스 리프 블록을 처음부터 끝가지 수평적으로 탐색
3. 인덱스 단일 스캔 : 수직적 탐색만으로 데이터를 찾는 스캔 방식
4. 인덱스 생략 스캔 : 선두 컬럼이 조건 절에 빠졌어도 인덱스를 활용하는 스캔방식 

 

(3) 집합 연산자

1. 집합 연산자란

- 집합 연산자는 두 개 이상의 테이블에서 여러 개의 질의의 결과를 연결하여 하나로 결합하는 연산자

 

2. 집합 연산자 유형

• UNION : 중복 행이 제거된 쿼리 결과 집합
• UNION ALL : 중복 행이 제거되지 않은 쿼리 결과 집합
• INTERSECT : 두 쿼리 결과에 공통적으로 존재하는 집합
• MINUS : 첫 쿼리에 있고 두 번째 쿼리에는 없는 집합

[1] UNION

- 합집합을 의미

- 중복을 제거하고 모두 포함한 결과 반환

 

[2] UNION ALL

- 중복된 항목을 포함하여 결과를 반환

 

[3] INTERSECT

- 교집합을 반환

 

[4] MINUS

- 차집합을 의미

- 데이터 집합을 기준으로 다른 데이터 집합과 공통 항목을 제외한 결과만 추출

 

 

(4) 조인

1. 조인 개념

- 조인은 두 개 이상의 테이블을 연결하여 데이터를 검색

- 관련된 튜플(행)들을 결합하여 하나의 튜플로 만드는 데이터 연결 방법

 

2. 조인 유형

• 논리적 조인 : 사용자 SQL 문에 표현되는 테이블 결합 방식(내부 조인, 외부 조인)
• 물리적 조인 : 데이터베이스 옵티마이저에 의해 내부적으로 발생하는 방식(중첩 반복 조인, 정렬 합병 조인, 해시 조인)

 

3. 논리적 조인

1. 내부 조인 : 공통 존재 컬럼의 값이 같은 경우 추출
2. 외부 조인 : 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인
3. 교차 조인 : 조인 조건이 없는 모든 데이터 조합을 추출
4. 셀프 조인 : 자기 자신에게 별칭을 자정한 후 다시 조인

 

▼ 내부 조인 예시

SELECT A.컬럼1, A.컬럼2, ....., B.컬럼1, B.컬럼2, ....
	FROM 테이블1 A [INNER] JOIN 테이블2 B
    ON 조인조건
[WHERE 검색조건1];

 

▼ 왼쪽 외부 조인(왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 테이블을 추출)

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ....
	FROM 테이블1 A LEFT [OUTER[ JOIN 테이블2 B
    ON 조인조건
[WHERE 검색조건];

 

▼ 오른쪽 외부 조인(오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 테이블을 추출)

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ....
	FROM 테이블1 A RIGHT [OUTER] JOIN 테이블2 B
    ON 조인조건
[WHERE 검색조건];

 

▼ 완전 외부 조인

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
	FROM 테이블1 A FULL [OUTER[ JOIN 테이블2 B
    ON 조인조건
[WHERE 검색조건];

 

▼ 교차 조인

SELECT 컬럼1, 컬럼2, ...
	FROM 테이블1 CROSS JOIN 테이블2

 

▼ 셀프 조인

SELECT A.컬럼1, A.컬럼2, ..., B.컬럼1, B.컬럼2, ...
	FROM 테이블1 A [INNER] JOIN 테이블1 B
    ON 조인조건
[WHERE 검색조건];

 

4. 물리적 조인

[1] 중첩 반복 조인(Nested- Loop Join)

- 2개 이상의 테이블에서 하나의 집합을 기준으로 순차적으로 상대방 Row를 결합하여 원하는 결과를 조합하는 조인 방식

- (실행속도) = (선행 테이블 사이트) * (후행 테이블 접근 횟수)

 

 

[2] 정렬 합병 조인(Sort-Merge Join)

- 조인의 대상 범위가 넓을 경우 발생하는 임의 접근을 줄이기 위한 경우나 연결고리에 마땅한 인덱스가 존재하지 않을 경우 해결하기 위한 조인 방식

- 양쪽 테이블의 정렬한 결과를 차례로 검색하면서 연결고리 형태로 합병하는 방식

 

 

[3] 해시 조인

- 해싱 함수를 활용하여 테이블 간 조인을 수행하는 방식

- 해싱 함수는 직접적인 연결을 담당하는 것이 아니라 연결될 대상을 특정 지역에 모아두는 역할만을 담당한다.

- 비용기반 옵티마이저에서만 가능

 

!! 여기서 잠깐!!

비용기반 옵티마이저란?

- 처리 방법들에 대한 비용을 산정해보고, 그 중에서 가장 적은 비용이 들어가는 처리 방법들을 선택하는 옵티마이저

 

(5) 서브쿼리

1. 서브쿼리의 개념

- 서브쿼리는 SQL 문 안에 포함된 또 다른 SQL문을 의미 

- 서브쿼리에 사용되는 컬럼 정보는 메인쿼리의 컬럼 정보를 사용할 수 있으나 역으로는 성립X

 

2. 서브쿼리 유형

• 비연관 서브쿼리 : 서브쿼리가 메인쿼리의 컬럼을 가지고 있지 않은 형태(동작 방식 기준)
• 연관 서브쿼리 : 서브쿼리가 메인쿼리의 컬럼을 가지고 있는 형태(동작 방식 기준)
• 단일 행 서브쿼리 : 결과가 항상 1건 이하인 서브쿼리(데이터 형태 기준)
• 다중 행 서브쿼리 : 실행 결과가 여러 건인 서브쿼리(데이터 형태 기준)
  • IN : 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건
  • ALL : 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건
  • ANY : 어느 하나의 값이라도 만족하는 조건
  • EXIST : 결과 만족 값이 존재 여부를 확인하는 조건
• 다중 컬럼 쿼리 : 결과가 여러 컬럼으로 반환되는 서브쿼리(데이터 형태 기준)
• FROM 절 서브쿼리 : 서브쿼리가 FROM 절 안에 들어있는 형태(위치 기준)
• WHERE 절 서브쿼리 : 서브쿼리가 WHERE 절 안에 들어있는 형태