Chapter 2 : 고급 SQL

---

🍀 Section 1 | 인덱스 활용

1. INDEX

1) 인덱스 개념

• 데이터를 빠르게 찾을 수 있는 수단
• 테이블에 대한 조회 속도를 높여주는 자료구조
• 테이블의 특정 레코드 위치를 알려주는 용도로 사용
• 테이블에서 기본키로 지정할 경우 자동으로 인덱스가 생성된다.

2) 인덱스 활용

2-1) 인덱스 생성

CREATE [UNIQUE] INDEX <index_name> ON <table_name> (<column(s)>);

• 각각의 파라미터가 의미하는 내용은 다음과 같다.

파라미터	내용
[UNIQUE]	인덱스가 걸린 컬럼에 중복값을 허용하지 않음 (생략 가능)
<index_name>	생성하고자 하는 인덱스 테이블 이름
<table_name>	인덱스 대상 테이블 이름
<column(s)>	인덱스 대상 테이블의 특정 컬럼 이름(들)

2-2) 인덱스 변경

ALTER [UNIQUE] INDEX <index_name> ON <table_name> (<column(s)>);

• 한 번 생성된 인덱스에 대해 변경이 필요한 경우는 드물다.
• 일부 제품은 인덱스에 대한 변경 SQL문이 없다.
• 이 경우 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용이 권고되고 있다.

2-3) 인덱스 삭제

ALTER TABLE <table_name> DROP INDEX <index_name>;

• <index_name>은 생성된 인덱스 이름을 의미한다.
• 인덱스 관련 명령어에 대한 SQL 표준이 없기에 제품별 DROP 명령문의 사용법은 약간씩 다르다.
• 보통 인덱스를 테이블의 종속 구조로 생각하여 인덱스를 삭제하기 위해 테이블의 변경을 가하는 형식의 명령을 사용한다.
• 즉, ALTER TABLE 명령 뒤에 DROP INDEX 명령이 추가되는 형태로 사용된다.

🚀 예상문제

1) 데이터베이스에서 테이블에 있는 데이터를 빠르게 색인하기 위한 기능을 무엇이라고 하는지 쓰시오.

• 답 : 인덱스(INDEX)

---

🍀 Section 2 | 뷰 활용

1. 뷰 (View)

1) 뷰 개념

• 논리 테이블로서 사용자에게는 테이블과 동일하다. (생성 관점이 아닌 사용 관점에서)
• 하나의 물리 테이블로부터 생성가능하다.
• 다수의 테이블 도는 다른 뷰를 이용하여 만들 수 있다.
• 뷰와 같은 결과를 만들기 위해 조인(Join) 기능을 활용할 수 있다.
• 뷰가 만들어져 있다면 사용자는 조인 없이하나의 테이블을 대상으로 하는 단순한 질의어를 사용할 수 있다.

2) 뷰 특징

• 뷰를 사용하는 주된 이유는 다음과 같은 단순한 질의어를 사용할 수 있기 때문이다.

SELECT * FROM <View_Name>;

• 즉, FROM 절에 있는 하나의 <뷰>를 통해 뷰를 구성하는 복수의 <테이블>을 대체하는 단순성에 그 의의가 있다.
• 또 이러한 기능을 통해 테이블의 중요 데이터 일부만을 제공할 수 있다는 장점이 있다.

👍🏻 뷰의 장점

• 논리적 독립성 제공
  • 뷰는 논리 테이블이므로 테이블의 구조가 변경되어도 뷰를사용하는 응용 프로그램은 변경하지 않아도 된다.
• 사용자 데이터 관리 용이
  • 복수 테이블에 존재하는 여러 종류의 데이터에 대해 단순한 질의어 사용이 가능하다.
• 데이터 보안 용이
  • 중요 보안 데이터를 저장 중인 테이블에 접근을 불허한다.
  • 해당 테이블의 일부 정보만을 볼 수 있는 뷰에는 접근을 허용하는 방식
  • 보안 데이터에 대한 접근 제어가 가능하다.

👎🏻 뷰의 단점

• 뷰 자체 인덱스 불가
  • 인덱스는 물리적으로 저장된 데이터를 대상으로 한다.
  • 따라서 논리적 구성인 뷰 자체는 인덱스를 가지지 못한다.
• 뷰 정의 변경 불가
  • 뷰의 정의를 변경하려면 뷰를 삭제하고 재생성해야 한다.
• 데이터 변경 제약 존재
  • 뷰의 내용에 대한삽입, 삭제, 변경 제약이 있다.

3) 뷰 생성

• 뷰 생성 명령의 일반 형태는 다음과 같다.

CREATE VIEW <뷰 이름>(컬럼목록) AS <뷰를 통해 보여줄 데이터 조회용 쿼리문>

[뷰 생성 방법]

상황	뷰 생성 쿼리문
테이블A 그대로 뷰A	CREATE VIEW 뷰A AS SELECT * FROM 테이블A;
테이블A 일부 컬럼 뷰X	CREATE VIEW 뷰X AS SELECT 컬럼1, 컬럼2 FROM 테이블A;
테이블A와 테이블B 조인 결과를 뷰Y	CREATE VIEW 뷰Y AS SELECT * FROM 테이블A, 테이블B
	WHERE 테이블A.컬럼1 = 테이블B.컬럼2;

4) 뷰 삭제 및 변경

• 뷰 정의 자체를 변경하는 것은 불가능하다.
• 뷰를 정의하면 뷰의 물리적 내용은 뷰의 이름과 데이터를 조회하기 위한 쿼리문일 뿐이다.
• 이때 뷰의 이름이나 쿼리문을 변경하는수단은 제공되지 않는다.
• 이 경우 뷰의 삭제와 재생성을 통해 뷰에 대한 정의 변경이가능하다.
• 뷰를 삭제하는 쿼리문은 다음과 같다.

DROP VIEW <View_Name>;

• 뷰를 통해 접근 가능한 데이터에 대한 변경이 가능하다.
• 하지만 모든 경우에 데이터의 변경이 가능한 것이 아니라 일부 제약이 존재한다.
• 이러한 제약은 뷰 자체가 논리적 개념이기에 물리적 상황에 의존적임을 의미한다.
• 예를 들어 기본키에 해당하는 컬림이 뷰에 정의되어 있지 않은 경우 INSERT는 불가능하다.

🚀 예상문제

1) 학생 테이블에서 3학년 학생들의 성명, 학년으로 구성된 뷰를 동아리라는 이름으로 생성하려 한다. 동아리 뷰를 생성하는 SQL문을 완성하시오

CREATE VIEW 동아리
(      ) 성명, 학년
FROM 학생
WHERE 학년 = 3;

• 답 : AS SELECT

---

🍀 Section 2 | 다중 테이블

1. 다중 테이블

• 다중 테이블 검색 방법
  • RDB는 데이터의 중복을 최소화하기 위해 데이터를 분해하여 저장하고 통합하여 사용한다.
  • 데이터를 분해하는 방법으로 정규화 기법이 사용되며, 통합하는 기법으로 다중 테이블에 대한 검색이 사용된다.
• 다중 테이블을 이요하는 보다 세부적인 기법은 다음과 같다.

기법	설명
조인	두 개의 테이블을 결합하여 데이터를 추출하는 기법
서브쿼리	SQL문 안에 포함된 SQl문 형태의 사용 기법
집합 연산	테이블을 집합 개념으로 조작하는 기법

2. 조인(JOIN)

1) 조인 개념

• 조인은 결합을 의미한다.
  • RDB에서의 조인은 교집합 결과를 가지는 결합 방법을 의미한다.
• 조인은 두 테이블의 공통값을 이용하여 컬럼을 조합하는 수단이다.
  • 보통 기본키와 후보키값을 결합하여 사용하는 것이 일반적이다.
  • 보다 엄밀하게 말핮면 기본키, 후보키와 관계없이 논리적인 값들의 연관을 사용한다.

2) 조인 유형

• 조인은 RDB에의 가장 큰 장점이면서 대표적인 핵심 기능이다.
• 크게 논리적 조인과 물리적 조인으로 구분할 수 있다.

💡 논리적 조인

• 사용자의 SQL문에 표현되는 테이블 결합 방식
• 두 테이블에 공통으로 존재하는 컬럼을 이용하는 방식 (공통 컬럼 기반)
• 종류
  • 내부 조인 Inner Join
  • 외부 조인 Outer Join

💡 물리적 조인

• DB의 옵티마이저에 의해 내부적으로 발생하는 테이블 결합 방식
• 틀정 테이블의 모든 데이터를 기준으로 다른 테이블의 정보를 추출
  • 다른 테이블에 정보가 없어도 출력됨
• 종류
  • 중첩 반복 조인 Nested Loop Join
  • 정렬 합병 조인 Sort-Merge Join
  • 해시 조인 Hash Join

2-1) 논리적 조인

💡 내부 조인 Inner Join

• 동등 조인 Equi Join
  • 공통 존재 컬럼의 값이 같은 경우를 추출
• 자연 조인 Natural Join
  • 두 테이블의 모든 컬럼을 비교하여 같은 컬럼명을 가진 모든 컬럼값이 같은 경우를 추출
• 교차 조인 Cross Join
  • 조인 조건이 없는 모든 데이터의 조합을 추출

💡 외부 조인 Outer Join

• 왼쪽 외부 조인 Left Outer Join
  • 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출
• 오른쪽 외부 조인 Right Outer Join
  • 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출
• 완전 외부 조인 Full Outer Join
  • 양쪽의 모든 데이터를 추출

2-2) 물리적 조인

💡 외부 조인 Outer Join

• 중첩 반복 조인 Nested Loop Join
  • 2개 이상의 테이블에서 하나의 집합을 기준으로 바깥 테이블의 처리 범위를 하나씩 액세스하면서 그 추출된 값으로 안쪽 테이블을 조인하는 방식
• 정렬 합병 조인 Sort-Merge Join
  • 적당한 인덱스가 없을 때 사용되며 양쪽 테이블의 처리 범위를 각자 실행하여 정렬한 결과를 차례로 스캔하고, 연결고리 조건으로 합병하는 방식
• 해시 조인 Hash Join
  • 조인할 테이블에 대해서 해시 함수를 생성
  • 해시 함수의 순서대로 결과를 출력하는 방식

3. 서브 쿼리(Sub Query)

1) 서브 쿼리의 개념

• 서브 쿼리는 SQL문 안에 포함된 또 다른 SQL문을 의미한다.
  • 알려지지 않은 기준을 검색하기 위해 사용한다.
• 메인 쿼리와 서브 쿼리 관계는 주종 관계이다.
  • 때문에, 서브 쿼리에 사용되는 컬럼 정보는 메인 쿼리의 컬럼 정보를 사용할 수 있으나 역으로는 성립하지 않는다.

2) 서브 쿼리의 유형

2-1) 동작 방식에 따른 서브 쿼리

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

• 서브 쿼리가 메인 쿼리의 컬럼을 가지고 있지 않은 형태
• 메인 쿼리에 서브 쿼리에서 실행된 결과값의 제공 용도

💡 연관(Correlated) 서브 쿼리

• 서브 쿼리가 메인 쿼리의 컬럼을 가지고 있는 형태
• 메인 쿼리가 먼저 수행되어 얻은 데이터를 서브 쿼리의 조건에 맞는지 확인하고자 할 경우에 사용

2-1) 데이터 형태에 따른 서브 쿼리

💡 Single Row (단일 행)

• 서브 쿼리의 결과가 항상 1건 이하인 서브 쿼리
• 단일 행 비교 연산자(=, <, <=, >, >=, <>)가 사용됨

💡 Multiple Row (다중 행)

• 서브 쿼리 실행 결과가 여러 건인 서브 쿼리
• 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS)가 사용됨

💡 Multiple Column (다중 컬럼행)

• 서브 쿼리 결과가 여러 컬럼으로 반환되는 서브 쿼리
• 메인 쿼리의 조건절에 여러 컬럼을 동시에 비교할 때, 서브 쿼리와 메인 쿼리에서 비교하는 컬럼 개수와 위치가 동일해야함.

4. 집합 연산

1) 집합 연산의 개념

• 테이블을 집합 개념으로 보고, 투 테이블 연산에 집합 연산자를 사용하는 방식
• 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어 준다.
• 일반적으로 집합 연산자를 사용하고자 하는 상황
  • 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할 때
  • 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때

2) 집합 연산의 유형

집합 연산	설명
UNION	여러 SQL문의 결과에 대한 합집합(중복 행 제거함)
UNION ALL	여러 SQL문의 결과에 대한 합집합(중복 행 제거하지 않음)
INTERSECTION	여러 SQL문의 결과에 대한 교집합(중복 행 제거함)
MINUS	앞의 SQL문의 결과와 뒤의 SQL문의 결과 사이의 차집합 (중복 행 제거함, 일부 제품의 경우 EXCEPT 사용

🚀 예상문제

1) 집합 연산자 중 앞의 SQL문의 결과와 뒤의 SQL문이 결과 사이의 차집합을 무엇이라 하는지 쓰시오.

• 답 : MINUS

2) 아래는 book, magazine 두 테이블을 결합시킨 테이블이다. 표를 참고하여 SQL문의 빈칸을 완성하시오.

<SELECT * FROM book>

publisher	price	remark
youngjin	20.2	1
miracle	21.2	1
dotcom	15.1	2
path	11.9	2
siwon	11.2	3

<SELECT * FROM magazine>

id	name	subject
1	rei	it
2	quan	cooking
3	jung	military

SELECT * FROM book
(      ) magazine on book.remark = magazine.id;

<결과>

publisher	price	remark	name	subject
youngjin	20.2	1	rei	it
miracle	21.2	1	rei	it
dotcom	15.1	2	quan	cooking
path	11.9	2	quan	cooking
siwon	11.2	3	jung	military

• 답 : LEFT JOIN 또는 LEFT OUTER JOIN