New 병아리 개발자

# UNION, UNION ALL

## UNION (DISTINCT)

• 쿼리문1, 2에 Union 진행 시 쿼리 결과를 합침 (이때 중복된 row 제거)

## UNION ALL

• 쿼리문1, 2에 UnionAll 진행 시 쿼리 결과를 합침 (중복 row 그대로 표시 -> UNION 보다 빠름)

1. Union (Distinct)
쿼리문1
Union
쿼리문2


2. UnionAll
쿼리문1
UnionAll
쿼리문2

# SQLD 2과목_4 SQL 최적화 기본 원리

## CBO (비용 기반 옵티마이저)

• 테이블 및 인덱스 등 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O자원량 등을 계산하여 가장 효율적일 것으로 예상되는 실행계획을 선택하는 옵티마이저

## 실행계획

• 실행계획은 예상정보이다.
• 실행계획은 조인방법, 조인순서, 액세스 기법, 질의 처리 예상 비용 등이 표현된다.
• 실행계획은 SQL 처리를 위한 실행 절차와 방법을 표현한 것이다.
• CBO 실행계획에는 단계별 예상 비용 및 건수 등이 표시된다.
• 실행방법이 달라진다고 결과가 달라지지는 않는다.

## SQL 처리 흐름도

• SQL의 내부적인 처리 절차를 시각적으로 표현해 준다.
• 인덱스 스캔, 데이블 전체 스캔 등과 같은 액세스 기법이 표현된다.
• SQL 처리 흐름도를 보고 실행 시간을 알 수는 없다.

## 인덱스

• 기본 인덱스는 UNIQUE & NOT NULL 제약조건을 갖는다.
• 보조 인덱스는 UNIQUE 인덱스가 아니라면 중복 데이터 입력이 가능하다.
• 자주 변경되는 속성을 인덱스로 선정할 경우 UPDATE, DELETE 성능에 악양향을 주므로 인덱스 후보에 적합하지 않다.
• 규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용하려고 한다.
• 비용기반 옵티마이저는 SQL을 수행하는데 있어 소요되는 비용을 계산하여 실행계획을 생성하므로 인덱스가 존재하더라도 전체 테이블 스캔이 유리하다고 판단할 수도 있다.
• 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있다.
• 인덱스를 생성할 때 정렬 순서를 내림차순으로 하면 내림차순으로 정렬된다.
• 인덱스 액세스는 테이블 전체 스캔보다 항상 유리하지 않다.
• 인덱스의 목적은 조회 성능 최적화이다.

## 여러 인덱스

• B-TREE 인덱스 : 브랜치 블록과 리프 블록으로 구성, 일치 및 범위 검색에 적합한 구조이다.
• CLUSTERED 인덱스 : 리프 페이지가 곧 데이터 페이지
• BITMAP 인덱스 : 포인터를 저장

# SQLD 2과목_3 SQL 활용

## 순수 관계 연산자

• SELECT, PROJECT, JOIN, DIVIDE

## CROSS JOIN

• 일반집합 연산자의 PRODUCT 개념, 테이블 간 조인 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말하며, 결과는 양쪽 집합의 M * N건의 데이터 조합이 발생한다.

## FULL OUTER JOIN

• 조인 수행시 좌축, 우측 테이블의 모든 데이터를 읽어 조인하여 결과 생성
• LEFT OUTER JOIN 과 RIGHT OUTER JOIN의 결과를 합집합으로 처리한 결과와 동일하다.

## LEFT (OUTER) JOIN

• 좌측 테이블 기준으로 결과 생성

## SELF JOIN

• 한 테이블 내에서 두개의 칼럼이 연관 관계가 있을 때 수행한다.
• 동일 테이블 사이의 조인
• FROM절에 동일한 테이블이 두번 이상 나타난다.
• 테이블과 칼럼 이름이 모두 동일하기 때문에 반드시 별칭을 지정해야 한다.

## 집합 연산자의 종류

1. UNION : 여러 개의 SQL문 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만든다. (합집합)
2. UNION ALL : 여러 개의 SQL문 결과에 대한 합집합으로 중복된 행 그대로 결과로 표시 (단순히 결과만 합쳐놓은 것), 개별 SQL문의 결과가 서로 중복되지 않은 경우 UNION과 결과가 동일함
3. INTERSECT : 여러 개의 SQL문의 결과에 대한 교집합, 중복된 행은 하나의 행으로 만든다. (교집합)
4. EXCEPT : 앞의 SQL문의 결과에서 뒤의 SQL문의 결과에 대한 차집합, 중복된 행은 하나의 행으로 만든다. (=MINUS) (차집합)

## ORDER BY 1, 2;

• 테이블의 1, 2번째 컬럼 기준으로 정렬

## 일반 집합 연산자와 SQL 비교

• UNION 연산은 UNION 기능으로 구현되었다.
• INTERSECTION 연산은 INTERSECTION 기능으로 구현되었다.
• DIFFERENCE 연산은 EXCEPT(MINUS) 기능으로 구현되었다.
• PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다.

## 오라클 계층형 질의

• START WITH절은 계층 구조 전개의 시작위치를 지정하는 구문으로 루트 데이터를 지정함
• ORDER SIBLINGS BY절은 형제 노드 사이에서 정렬을 수행
• 루트 노드의 LEVEL 값은 1이다.
• 순방향전개란 부모 노드로부터 자식 노드 방향으로 전개하는 것을 의미

## PRIOR 키워드는 SELECT, WHERE 절에서도 사용 가능하다.

## 서브쿼리

• 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용할 수 있다.
• 서브쿼리는 SELECT, FROM, HAVING, ORDER BY절 등에서 사용 가능하다.
• 서브쿼리의 결과가 복수 행 결과를 반환하는 경우에는 IN, ALL, ANY 등 복수 행 비교 연산자와 사용해야 한다.
• 다중 칼럼 서브쿼리는 서브쿼리의 결과로 여러 개의 칼럼이 반환되어 메인 쿼리의 조건과 비교되는데 SQL SERVER에서는 지원하지 않는다.
• 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브쿼리이다.
• 다중 행 서브쿼리 비교 연산자는 단일 행 서브쿼리의 비교 연산자로도 사용 가능하다. (반대는 불가)

## 뷰

• 뷰는 단지 정의만을 갖고 있으며 실행 시점에 질의를 재작성하여 수행한다.
• 뷰는 보안을 강화하기 위한 목적으로 활용할 수 있다.
• 실제 데이터를 저장하고 있는 뷰를 생성하는 기능을 지원하는 DBMS도 있다.
• 뷰의 장점중 독립성은 테이블 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 변경하지 않아도 된다.

## 뷰 사용의 장점

1. 독립성 : 테이블 구조 변경되어도 뷰 사용하는 응용 프로그램은 변경하지 않아도 됨
2. 편리성 : 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있고, 해당 형태의 SQL 문을 자주 사용할 때 뷰를 이용하면 편리하게 사용 가능함
3. 보안성 : 숨기고 싶은 정보가 존재하면 뷰를 생성할 때 해당 컬럼을 빼고 생성하여 정보를 감출 수 있다.

## ROLLUP

• 집계, 계층구조, 나열된 컬럼에 대해 계층구조로 집계를 출력한다.

## CUBE

• 나열된 모든 인수의 결합 가능한 집계가 출력

## GROUPING SETS

• 다양한 소계 집합을 만들수 있는데, GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있고, 인수의 순서가 바뀌어도 결과는 같다. 결과에 대한 정렬이 필요한 경우 ORDER BY 절에 명시적으로 정렬 칼럼이 표시되어야 함

## 윈도우 함수

• Partition과 Group By 구문은 의미적으로 유사함
• Partition 구문이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다.
• 윈도우 함수 적용 범위는 Partition을 넘을 수 없다.
• 윈도우 함수는 결과에 대한 함수처리이기 때문에 결과 건수는 줄지 않는다.

## ROLE 관련

• ROLE을 DBMS USER에게 부여하기 위해서는 GRANT 명령을 사용하며, ROLE을 회수하기 위해서는 REVOKE 명령을 사용한다.
• GRANT 권한명 ON 테이블명 TO 유저명;

## ROLE

• 다양한 권한을 그룹으로 묶어 관리할 수 있도록 사용자와 권한 사이에서 중개 역할을 수행하는 ROLE을 제공한다.

## TRIGGER

• 트리거는 데이터베이스에 의해 자동으로 호출되고 수행된다.
• 트리거는 특정 테이블에 대해 INSERT, UPDATE, DELETE 문이 수행되었을 때 호출되도록 정의할 수 있다.
• 트리거는 데이터베이스에 로그인하는 작업에도 정의할 수 있다.
• 트리거는 프로시저와 달리 커밋, 롤백 등 TCL을 사용할 수 없다.

# SQLD 2과목_2 기본

## SQL 문장 종류

1. DML (데이터 조작어) 

• SELECT : 데이터를 조회, 검색하기 위한 명령어로 RETRIEVE라고도 함
• INSERT / UPDATE / DELETE : 데이터에 변형을 가하는 종류의 명령어

2. DDL (데이터 정의어)

• CREATE / ALTER / DROP / RENAME : 테이블과 같은 데이터 구조를 정의하는데 사용

3. DCL (데이터 제어어)

• GRANT / REVOKE : 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고, 화수하는 명령어

4. TCL (트랜잭션 제어어)

• COMMIT / ROLLBACK / Savepoint : 논리적인 작업의 단위를 묶어 DML에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명렁어

## 테이블 칼럼에 대한 정의 변경

1. 오라클

• ALTER TABLE 테이블명 MODIFY (컬럼명1 데이터 유형, 컬럼명2 데이터 유형 ...);

2. SQL Server

• ALTER TABLE 테이블명 ALTER (컬럼명1 데이터 유형, 컬럼명2 데이터 유형 ...);

## NULL

• 공백, 숫자 0과는 전혀 다른 값
• 조건에 맞는 데이터가 없을 때의 공집합과도 다름
• 아직 정의되지 않은 미지의 값 또는 현재 데이터를 입력하지 못하는 경우를 의미.
• (모르는 값, 값의 부재를 의미, NULL과의 모든 비교는 알 수 없음(Unknown을 반환)

## 남아있는 데이터 관련 문제 풀이

• DELETE CASCADE 시 모두 삭제
• DELETE SET NULL 시 필드 값 NULL로 변경

## 제약 조건의 종류

• PRIMARY KEY (기본키) : 주키로 테이블당 1개만 생성 가능
• UNIQUE KEY (고유키) : 테이블 내에서 중복값이 없으며, NULL 입력 가능
• NOT NULL : 명시적으로 NULL 입력 방지
• CHECK : 데이터의 무결성을 유지하기 위해 특정 칼럼에 설정하는 제약
• FOREIGN KEY (외래키) : 테이블 생성 시 설정할 수 있고, NULL값을 가질 수 있으며, 테이블당 여러 개 생성 가능하고, 외래키 값은 참조 무결성 제약을 받을 수 있다.

## 테이블 생성의 주의사항

• 테이블명은 객체를 의미할 수 있는 적절한 이름 사용, 가능한 단수명 사용
• 테이블명은 다른 테이블의 이름과 중복되지 않아야 함
• 한 테이블 내 칼럼명은 중복되게 지정할 수 없음
• 테이블명을 지정 후 각 칼럼들은 괄호로 묶어 지정
• 각 칼럼은 콤마로 구분, 테이블 생성문 끝은 항상 세미콜론으로 끝남
• 칼럼에 대해 다른 테이블까지 고려, 일관성 있게 사용하는 것이 좋다
• 칼럼 뒤에 데이터 유형은 꼭 지정해야 함
• 테이블명, 칼럼명은 반드시 문자로 시작, 벤더별로 길이에 대한 한계 존재
• 벤더에서 사전에 정의한 예약어는 사용 불가
• A-Z, a-z, 0-9, _, $, # 문자만 허용된다

## COUNT(*), COUNT(PK칼럼)

• PK는 NULL값을 가질 수 없고, COUNT(*), COUNT(PK칼럼)은 항상 같은 값이 나온다.

## 테이블 칼럼 삭제 SQL 구문

• ALTER TABLE 테이블명 DROP COLUMN 삭제할 칼럼명;

## 테이블 이름 변경 SQL 구문

• RENAME 기존테이블명 TO 신규테이블명;

## 데이터 입력 SQL 구문

• INSERT INTO 테이블명 (칼럼 리스트) VALUES (칼럼 리스트에 넣을 VALUE값 리스트);
• INSERT INTO 테이블명 VALUES (전체 칼럼에 넣을 VALUE값 리스트);

## 데이터 수정 SQL 구문

• UPDATE 테이블명 SET 수정할 칼럼명 = 수정될 새로운 값;

## 데이터 조회 SQL 구문

• SELECT [ALL / DISTINCT] FROM 테이블명;
• ALL : Default 옵션, 중복된 데이터 모두 출력
• DISTINCT : 중복된 데이터 1건으로 처리해서 출력

## 참조동작

• CASCADE : Master 테이블 삭제 시 같이 삭제
• RESTRICT : Master 테이블에 PK없을 경우 외부키를 NULL값 처리
• AUTOMATIC : Master 테이블에 PK없을 경우 PK생성 후 입력
• DEPENDENT : Master 테이블에 PK 존재할때만 입력 허용

## 로그 관련

• DELETE : 로그를 남김
• DROP / TRUNCATE : 로그를 남기지 않음

## TRUNCATE TABLE

• 해당 테이블에 들어있던 모든 행들이 제거, 저장 공간을 재사용 가능하도록 해제 한다. (데이터 삭제, 디스크 사용량 초기화, 스키마 정의 유지)
• 테이블 구조를 완전히 삭제하기 위해서는 DROP TABLE 실행.

## TRUNCATE vs DELETE 비교

1. TRUNCATE : DDL, ROLLBACK 불가, AUTO COMMIT, 테이블을 최초 생성된 초기상태로 만든다.
2. DELETE : DML, COMMIT와 ROLLBACK 사용 가능, 사용자 COMMIT, 데이터만 삭제한다.

## 트랜잭션의 특성

1. 원자성 : 트랜잭션에서 정의된 연산은 모두 성공적으로 실행되던지, 전혀 실행되지 않은 상태로 남아야 함
2. 일관성 : 트랜잭션 실행 전 데이터베이스 내용이 잘못 되어있지 않다면 트랜잭션 실행 후에도 데이터베이스의 내용에 잘못이 있으면 안된다.
3. 고립성 : 트랜잭션이 실행되는 도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
4. 지속성 : 트랜잭션이 성공적으로 수행되면 트랜잭션이 갱신한 데이터베이스 내용은 영구적으로 저장된다.

## ROLLBACK

• 데이터 변경 사항이 취소되어 데이터의 이전 상태로 복구되며, 관련된 행에 대한 잠금(LOCKING)이 풀리고 다른 사용자들이 데이터 변경을 할 수 있게 된다.
• 롤백 구문을 만나면 최초의 BEGIN TRANSACTION시점까지 모두 롤백이 수행된다.

## 저장점 (SAVEPOINT)

• 롤백할 때 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아닌, 현 시점에서 저장점까지 트랜잭션의 일부만 롤백할 수 있다.

1. 오라클

• SAVEPOINT 저장점;
• ROLLBACK TO 지정한 저장점;

2. SQL SERVER

• SAVE TRANSACTION 저장점;
• ROLLBACK TRANSACTION 지정한 저장점;

## 용어 설명

• 트랜잭션은 데이터베이스의 논리적 연산단위로서 밀접히 관련되어 분리될 수 없는 한개 이상의 데이터베이스 조작을 의미
• 트랜잭션의 종료를 위한 대표적인 명령어로서 데이터에 대한 변경사항을 데이터베이스에 영구적으로 반영하는 커밋과 데이터에 대한 변경사항을 모두 폐기하고 변경전의 상태로 되돌리는 롤백이 있다.

## WHERE 절

• SQL을 사용하면서 테이블의 데이터를 조회할 때 원하는 데이터만 검색하기 위해 SELECT, FROM 절과 함께 WHERE 절을 이용, 조회되는 데이터의 조건을 지정하여 데이터를 제한할 수 있다.

## NULL의 연산

• NULL 값과의 연산은 NULL 값을 리턴
• NULL 값과의 비교연산은 거짓(FALSE)를 리턴
• NULL 값은 특정 값보다 크다, 작다라고 표현할 수 없다.
• NULL 값을 조건절에서 찾는 경우 IS NULL , IS NOT NULL만 사용한다.

## BETWEEN a AND b

• a와 b의 값 사이에 있으면 됨

## IN (리스트)

• 리스트에 있는 값 중 어느 하나라도 일치하면 됨

## 내장함수

• 함수의 입력 행수에 따라 단일행 함수, 다중행 함수로 구분한다.
• 단일행 함수는 SELECT, WHERE, ORDER BY, UPDATE의 SET절에 사용 가능하다.
• 1:M 조인이라도 M쪽에 출력된 행이 하나의 단일행 함수의 입력값으로 사용되므로 단일행 함수는 사용할 수 있다.
• 다중행 함수도 단일행 함수와 동일하게 단일값만을 반환한다.

## 단일행 문자형 함수 종류

• LOWER : 소문자로 바꿔줌
• UPPER : 대문자로 바꿔줌
• CONCAT (문자열1, 문자열2) : 문자열1, 2를 연결
• SUBSTR / SUBSTRING (문자열, m, n) : 문자열 중 m위치에서 n개의 문자 길에 해당하는 문자를 반환
• LENGTH / LEN : 문자열의 개수 반환
• LTRIM (문자열, 지정문자) : 첫 문자부터 확인, 지정 문자가 나타나면 해당 문자 제거
• RTRIM (문자열, 지정문자) : 마지막 문자부터 확인, 지정 문자가 나타나면 해당 문자 제거
• TRIM : 문자열에서 머리말, 꼬리말 또는 양쪽에 있는 지정 문자 제거

## NULL 관련 함수 종류

1. NVL / ISNULL (표현식1, 표현식2) : 표현식1의 결과값이 NULL이면 표현식2 값을 출력
2. NULLIF (표현식1, 표현식2) : 표현식1이 표현식2와 같으면 NULL을, 다르면 표현식1 출력
3. COALESCE (표현식1, 표현식2, ..) : 임의의 개수 표현식에서 NULL이 아닌 최초의 표현식을 나타낸다. 모든 값이 NULL이면 NULL 리턴

## 집계함수의 종류

1. COUNT (*) : NULL 포함, 행 수 출력
2. COUNT(표현식) : 표현식 값이 NULL인 값 제외, 행 수 출력
3. SUM (표현식) : NULL 값 제외 합계 출력
4. AVG (표현식) : NULL 값 제외 평균 출력
5. MAX (표현식) : 최대값 출력
6. MIN (표현식) : 최소값 출력
7. STDDEV (표현식) : 표준 편차 출력
8. VARIAN (표현식) : 분산 출력

## DUAL 테이블 특징

• SYS가 소유, 모든 사용자가 액세스 가능한 테이블
• 일종의 DUMMY 테이블
• DUMMY라는 문자열 유형의 칼럼에 'X'라는 값이 들어 있는 행을 1건 포함하고 있다.

## ORDERY BY 절

• 기본적인 정렬 순서는 오름차순(ASC : 낮->높)
• 숫자형 데이터는 가장 작은 값부터 출력
• 날짜형 데이터는 가장 빠른 값부터 출력
• 오라클에서는 NULL 값을 가장 큰 값으로 간주
• SQL SERVER는 NULL 값을 가장 작은 값으로 간주

## SELECT 문장 실행 순서

• FWGHSO : FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

## TOP 관련

• EMP 테이블에서 급여가 높은 2명 내림차순으로 출력 : SELECT TOP(2) WITH TIES NAME, SAL FROM EMP ORDER BY SAL DESC;

## JOIN 조건 개수

• 여러 테이블로부터 원하느 데이터 조회 시 전체 테이블 개수에서 최소 (N-1)개 만큼의 JOIN 조건 필요

## JOIN

• 일반적으로 조인은 PK와 FK 값의 연관성에 의해 성립된다.
• DBMS 옵티마이저는 FROM 절에 나열된 테이블이 아무리 많아도 항상 2개의 테이블씩 짝지어 조인을 수행한다.
• EQUI JOIN은 조인에 관여하는 테이블 간의 컬럼 값들이 정확하게 일치하는 경우 사용되는 방법이다.
• EQUI JOIN은 '=' 연산자에 의해서만 수행, 그 외의 비교 연사자를 사용하는 경우 모두 NON EQUI JOIN이다.
• 대부분 NON EQUI JOIN을 수행할 수 있지만, 때로는 설계상의 이유로 수행이 불가능한 경우도 있다.