집합연산자 & 계층형질의
STANDARD SQL
관계형 대수
- 관계형 데이터베이스에서 원하는 정보를 유도하기 위한 기본 연산 집합
- 일반 집합 연산, 순수 관계 연산이 있음
일반 집합 연산
순수 관계 연산
셀렉션은 행 프로젝션은 열 디비전은 두 번째 오는 테이블과 연관된 데이터만 출력(디비전은 DB를 다룰 때 사용하진 않음)
집합 연산자 개념 - UNION/UNION ALL
집합 연산자란?
- 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나
- 테이블에서 SELECT한 컬럼의 수와 각 컬럼의 데이터타입이 테이블 간 상호 호환 가능해야 한다.
UNION(합집합)
- 두 개의 테이블을 하나로 만드는 연산
- UNION에 사용할 컬럼의 수와 데이터 형식이 일치해야 하며 합친 후에 테이블에서 중복된 데이터는 제거. 이를 위해 UNION은 테이블을 합칠 때 정렬 과정을 발생시킴
- 관계형 대수의 일반 집합 연산에서 합집합의 역할
데이터 연결 및 (정렬 밑에 A4잘려있음)
UNION ALL
- UNION과 거의 동일하지만 복 제거와 정렬을 하지 않음
집합 연산자 개념 - INTERSECT
INTERSECT
- 두 개의 테이블에 대해 겹치는 부분을 추출하는 연산
- 추출 후에는 중복된 결과를 제거
- 관계형 대수의 일반 집합 연산에서 교집합의 역할
- Oracle/Maria DB에서는 intersect 키워드가 지원되지만 MySQL에서는 지원x(mysql에선s join 등을 활용해야함)
집합 연산자 개념 - EXCEPT
EXCEPT
- 두 개의 테이블에서 겹치는 부분을 앞의 테이블에서 제외하여 추출하는 연산
- 추출 후에는 중복된 결과를 제거
- 관계형 대수의 일반 집합 연산에서 차집합의 역할
- Oracle/Maria DB에서는 intersect 키워드가 지원되지만 MySQL에서는 지원x(mysql에선s join 등을 활용해야함)
계층형 질의 - Oracle
계층형 질의란?
- 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 사용하는 것
계층형 데이터
- 동일 테이블에 계층적으로 상위와 하위 데이터가 포함되어 있는 데이터
계층형 질의 예시(ORACLE)
CONNECT BY 키워드(Oracle)
ISLEAF에서 최하위면 1 아니면 0으로 표시
계층형 질의 - SQL Server/MariaDB
무슨 소린지 모르겠고,, 나중에 다시 보기
JOIN 심화
JOIN
- 두 개 이상의 테이블들을 연결 또는 결합하여 데이터를 출력하는 것
EQUI JOIN
- 두 개의 테이블 간에 서로 정확하게 일치하는 경우를 활용한 조인
- 등가 연산자('=')를 활용한 조인을 의미
Non EQUI JOIN
- 두 개의 테이블 간에 서로 정확하게 일치하지 않는 경우를 활용한 조인
- 등가 연산자 이외의 연산자를 활용한 조인을 의미
FROM절 JOIN형태 - INNER JOIN/ON
INNER JOIN
FROM절 JOIN형태 - INNER JOIN/USING/NATURAL JOIN
USING 조건절
NATURAL JOIN
FROM절 JOIN형태 - CROSS JOIN
CROSS JOIN
- CROSS 생략 가능(inner join은 뒤에 on이 들어가기 때문에 구분 가능)
FROM절 JOIN형태 - OUTER JOIN
OUTER JOIN
- 두 개의 테이블 간에 교집합을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회
- 빈 곳은 NULL 값으로 출력
- WHERE 조건절에서 한쪽에만 있는 데이터를 포함시킬 테이블 쪽으로 (+)를 위치
(+)가 왼쪽에 있으므로 LEFT OUTER JOIN
표준 OUTER JOIN(LEFT JOIN)
왼쪽 테이블의 데이터는 다 출력이 되어야 한다.
표준 OUTER JOIN(RIGHT JOIN)
오른쪽 테이블의 데이터는 다 출력이 되어야 한다.
표준 OUTER JOIN(FULL OUTER JOIN)
FULL OUTER JOIN in MySQL
- LEFT OUTER JOIN과 RIGHT OUTER JOIN의 결과를 UNION으로 합치면 FULL OUTER JOIN과 같은 결과를 출력할 수 있다.
FROM절 JOIN형태 - JOIN with WHERE
INNER JOIN
- JOIN을 활용한 쿼리에서도 WHERE문을 이용하여 조건을 걸 수 있음
셀프조인
셀프 조인이란?
- 동일 테이블 사이의 조인
- 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 별칭 필수
셀프 조인 예시 - 계층형 질의
ALPHA 관리자(+) = BETA.사원번호; 이렇게 되어야함(위에 잘못된거)
서브쿼리 심화
동작하는 방식에 따른 서브쿼리 분류
서브쿼리 분류
- 서브쿼리에 메인쿼리의 컬럼이 포함되는지에 따라 구분
- 연관 서브쿼리와 비연관 서브쿼리로 나눌 수 있음
연관 서브쿼리
- 메인쿼리의 컬럼이 서브쿼리에 포함되며, 메인쿼리의 컬럼은 서브쿼리에 특정 조건으로 사용된다
비연관 서브쿼리
- 메인쿼리 컬럼이 서브쿼리에 포함되지 않으며, 주로 메인쿼리에 특정한 값을 제공할 때 사용된다
반환되는 데이터 형태에 따른 서브쿼리 분류 - 단일 행 서브쿼리
서브쿼리 분류
- 단일 행 서브쿼리
- 다중 행 서브쿼리
- 다중 컬럼 서브쿼리
단일 행 서브쿼리
- 서브쿼리의 결과가 한 개의 행을 반환하며, 단일 행 비교 연산자(=,<,>,<=,>=)와 같이 사용된다
반환되는 데이터 형태에 따른 서브쿼리 분류 - 다중 행 서브쿼리
다중 행 서브쿼리
- 서브쿼리의 결과가 두 개 이상 행을 반환할 수 있으며, 다중 행 비교 연산자(in, any, all, exists)와 같이 사용된다
다중 행 서브쿼리 - EXISTS
반환되는 데이터 형태에 따른 서브쿼리 분류 - 다중 컬럼 서브쿼리
다중 컬럼 서브쿼리
- 서브쿼리의 결과가 여러 개의 컬럼을 반환하며, 메인쿼리의 조건과 동시에 비교된다
스칼라 서브쿼리
스칼라 서브쿼리
- 스칼라 서브쿼리는 하나의 속성을 가지면서, 하나의 행만을 반환하는 쿼리이다. 그리고 이는 SELECT, WHERE, HAVING 절 등에서 사용할 수 있다.
스칼라 서브쿼리 예시 - DUAL
DUAL이란 테이블은 단순히 구문을 지키기 위해서 써주는 것이지 어떠한 값도 가지지 않음(MySQL이나 MariaDB에서는 DUAL 생략해도 상관 X)
뷰(VIEW)
VIEW
- 뷰는 다른 테이블에서 파생된 테이블이다
- 물리적으로 데이터가 저장되는 것이 아니라, 논리적으로만 존재하며 뷰를 사용한 질의 시에는 DBMS에서 뷰 정의에 따라 질의를 재작성하여 수행한다
VIEW의 장점
| 특징 | 설명 |
|---|---|
| 독립성 | 테이블 구조가 변경되어도 뷰를 사용하고 있는 응용 프로그램은 변경하지 않아도 된다 |
| 편리성 | 자주 사용되는 복잡한 쿼리를 미리 뷰로 정의해 놓으면, 추후 쿼리는 간단한 형태로 표현할 수 있다. |
| 보안성 | 사용자의 권한에 따라 열람 가능한 데이터를 다르게할 수 있다. 권한에 따라 확인 가능한 컬럼을 정의하여 뷰를 생성하면, 기본 테이블 노출 없이 접근 제어를 할 수 있다. |
VIEW의 특징
- 생성된 뷰는 또 다른 뷰를 생성하는데 사용될 수 있다.
- 뷰의 정의는 변경할 수 없으며, 삭제 후 재생성이 필요하다.
- 뷰를 통한 갱신에는 제약이 따른다. 갱신을 위해서는 기본적으로 원천 테이블의 기본키가 포함되어야 한다.
- 원천이 되는 테이블이나 뷰가 삭제되면 이를 기반으로 하는 뷰도 함께 삭제된다.
VIEW 정의 쿼리
그룹 함수 & 윈도우 함수
데이터 분석을 위한 함수
- 윈도우 함수
- 집계 함수
- 그룹 함수
