조인(JOIN)
EQUI JOIN(등가 조인)
- 조인은 여러 개의 릴레이션을 사용하여 새로운 릴레이션을 만드는 과정
- 조인의 가장 기본은 테이블 간에 교집합을 만드는 것
- 두 개의 테이블 간에 일치하는 것을 조인
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
EQUI JOIN은 '='을 사용하여 두 개의 테이블을 연결하며, 추가 조건 및 정렬 가능
INNER JOIN
EQUI JOIN과 마찬가지로 ISO 표준 SQL로 INNER JOIN이 있음, INNER JOIN은 ON문을 사용하여 테이블 연결
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
HASH JOIN(해시 조인)
- 해시 조인은 먼저 선행 테이블을 결정하고 선행 테이블에서 주어진 조건에 해당하는 행을 선택
- 해당 행이 선택되면 조인 키(JOIN Key)를 기준으로 해시 함수를 사용하여 해시 테이블인 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾음
- 후행 테이블의 조인 키를 사용하여 해시 함수를 적용하여 해당 버킷을 검색
INTERSECT 연산
두 개의 테이블에서 교집합을 조회(공통된 값 조회)
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT
Non-EQUI JOIN(비등가 조인)
- Non-EQUI는 두 개의 테이블 간에 조인하는 경우 ‘=’을 사용하지 않고 (>, <, >=, <=)등을 사용
- 정확하게 일치하지 않는 것을 조인
OUTER JOIN
- 두 개의 테이블 간에 교집합(EQUI JOIN)을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜 조회
- 왼쪽 테이블에만 있는 행을 포함하려면
LEFT OUTER JOIN, 오른쪽 테이블의 행을 포함하려면RIGHT OUTER JOIN을 사용 FULL OUTER JOIN은LEFT OUTER JOIN과RIGHT OUTER JOIN모두를 하는 것- Oacle 데이터베이스는 OUTER JOIN을 할 때 “(+)”기호를 사용
SELECT * FROM DEPT, EMP WHERE EMP.DEPTNO (+)= DEPT.DEPTNO
위의 쿼리는 Oracle 데이터베이스의 OUTER JOIN 방법
CROSS JOIN
- 조인 조건구 없이 2개의 테이블을 하나로 조인
- 조인구가 없기에 카테시안 곱 발생
SELECT * FROM EMP CROSS JOIN DEPT
UNION
- 두 개의 테이블을 하나로 만드는 연산(2개의 테이블을 하나로 합치는 것)
- 두 개의 테이블의 칼럼 수, 칼럼의 데이터 형식 모두가 일치해야 함
- UNION 연산이 사용될 때 칼럼 수 혹은 데이터 형식이 다르면 오류 발생
- 테이블을 합치면서 중복된 데이터 제거
- 정렬 과정 발생
UNION ALL
- 두 개의 테이블을 하나로 만드는 연산
- 중복을 제거하거나 정렬을 유발하지 않음
MINUS
- 두 개의 테이블에서 차집합을 조회
- MS-SQL에서는 EXCEPT 사용
계층형 조회(CONNECT BY)
- Oracle 데이터베이스에서 지원하며, 계층형으로 데이터를 조회할 수 있음
- 트리(Tree) 형태의 구조로 질의를 수행
- START WITH는 시작 조건을 의미
- CONNECT BY PRIOR는 조인 조건을 의미
- Root 노드로부터 하위 노드의 질의를 실행
- MAX(LEVEL)을 사용하여 최대 계층 수를 구할 수 있음
CONNECT BY 키워드
| 키워드 | 설명 |
|---|---|
| LEVEL | 검색 항목의 깊이를 의미(최상위 레벨은 1) |
| CONNECT_BY_ROOT | 계층 구조에서 가장 최상위 값을 표시 |
| CONNECT_BY_ISLEAF | 계층 구조에서 가장 최하위를 표시 |
| SYS_CONNECT_BY_PATH | 계층 구조의 전체 전개 경로 표시 |
| NOCYCLE | 순환 구조가 발생지점까지만 전개 |
| CONNECT_BY_ISCYCLE | 순환 구조 발생 지점 표시 |
계층형 조회 키워드
| 키워드 | 설명 |
|---|---|
| START WITH 조건 | 계층 전개의 시작 위치를 지정 |
| PRIOR 자식 = 부모 | 부모에서 자식방향으로 검색을 수행하는 순방향 전개 |
| PRIOR 부모 = 자식 | 자식에서 부모방향으로 검색을 수행하는 역방향 전개 |
| NOCYCLE | 데이터를 전개하면서 이미 조회한 데이터를 조회하면 CYCLE이 형성되는데, 사이클이 발생되지 않게 함 |
| Order siblings by 칼럼 | 동일한 LEVEL인 형제노드 사이에서 정렬을 수행 |
서브쿼리(Subquery)
- SELECT문 내에 다시 SELECT문을 사용하는 SQL문
- FROM구에 SELECT문을 사용하면 인라인 뷰(Inline View)라고 함
- SELECT문에 서브쿼리를 사용하면 스칼라 서브쿼리(Scala Subquery)라고 함
- WHERE구에 SELECT문을 사용하면 서브쿼리라고 함
- 서브쿼리 밖에 있는 SELECT문은 메인쿼리라고 함
- 서브쿼리내에서 메인쿼리 내 칼럼을 사용하면 연관 서브쿼리(Correlated Subquery)라고 함
단일 행 서브쿼리와 다중 행 서브쿼리
- 단일 행 서브쿼리는 단 하나의 행만 반환하며, 비교 연산자(=, <, <=, >, >=, <>)를 사용
- 다중 행 서브쿼리는 여러 행을 반환하며, IN, ANY, ALL, EXISTS를 사용
다중 행 비교 연산자
| 연산 | 설명 |
|---|---|
| IN | 메인쿼리의 비교조건이 서브쿼리의 결과 중 하나만 동일하면 참 |
| ALL | - 메인쿼리와 서브쿼리의 결과가 모두 동일하면 참 - < ALL: 최솟값 반환 - > ALL: 최댓값 반환 |
| ANY | - 메인쿼리의 비교조건이 서브쿼리의 결과 중 하나 이상 동일하면 참 - < ANY: 하나라도 크면 참 - > ANY: 하나라도 작으면 참 |
| EXISTS | 메인쿼리와 서브쿼리의 결과가 하나라도 존재하면 참 |
그룹 함수(Group Function)
ROLLUP 함수
- GROUP BY의 칼럼에 대해 Subtotal을 만듦
- ROLLUP을 할 때 GROUP BY구에 칼럼이 두 개 이상 올 시 순서에 따라 결과가 달라짐
GROUPING 함수
- GROUPING 함수는 ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분할 수 있음
- GROUPING 함수의 기능을 사용하면 사용자가 필요로 하는 데이터를 SELECT문으로 작성하여 제공할 수 있음
GROUPING SETS 함수
- GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있음
- GROUP BY에 나오는 칼럼의 순서와 관계없이 개별적으로 모두 처리
CUBE 함수
- 제시한 칼럼에 대해 결합 가능한 모든 집계 계산
- 다차원 집계를 제공하여 다양하게 데이터를 분석할 수 있음
- 조합할 수 있는 경우의 수가 모두 조합되는 것
윈도우 함수(Window Function)
- 행과 행 간의 관계를 정의하기 위해 제공되는 함수
- 순위, 합계, 평군, 행 위치 등을 조작할 수 있음
윈도우 함수 구조
SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER (PARTITION BY 칼럼 ORDER BY WINDOWING절)
FROM 테이블
| 구조 | 설명 |
|---|---|
| ARGUMENTS(인수) | 윈도우 함수에 따라 0~N개의 인수 설정 |
| PARTITION BY | 전체 집합을 기준에 의해 소그룹으로 나눔 |
| ORDER BY | 어떤 항목에 대해 정렬 |
| WINDOWING | 행 기준의 범위 지정 (추후 설명) |
WINDOWING
| 구조 | 설명 |
|---|---|
| ROWS | 부분집합인 윈도우 크기를 물리적 단위로 행의 집합 지정 |
| RANGE | 논리적인 주소에 의해 행 집합 지정 |
| BETWEEN~AND | 윈도우의 시작과 끝의 위치를 지정 |
| UNBOUNDED PRECEDING | 윈도우의 시작 위치가 첫 번째 행임을 의미 |
| UNBOUNDED FOLLOWING | 윈도우 마지막 위치가 마지막 행임을 의미 |
| CURRENT ROW | 윈도우 시작 위치가 현재 행임을 의미 |
순위 함수(RANK Function)
특정 항목과 파티션에 대해 순위를 계산할 수 있는 함수
순위 관련 윈도우 함수
| 함수 | 설명 |
|---|---|
| RANK() | 특정 항목 및 파티션에 대해 순위 계산(동일한 순위는 동일한 값 부여) |
| DENSE_RANK() | 동일한 순위를 하나의 건수로 계산 |
| ROW_NUMBER() | 동일한 순위에 대해 고유의 순위 부여 |
집계 함수(AGGREGATE Function)
집계 관련 윈도우 함수
| 함수 | 설명 |
|---|---|
| SUM() | 파티션 별로 합계 계산 |
| AVG() | 파티션 별로 평균 계산 |
| COUNT() | 파티션 별로 행 수 계산 |
| MAX() | 파티션 별로 최댓값 계산 |
| MIN() | 파티션 별로 최솟값 계산 |
행 순서 관련 함수
- 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력 할 수 있음
- 특정 위치의 행을 출력할 수 있음
행 순서 관련 윈도우 함수
| 행 순서 | 설명 |
|---|---|
| FIRST_VALUE() | - 파티션에서 가장 처음에 나오는 값을 구함 - MIN 함수를 사용해서 같은 결과를 구할 수 있음 |
| LAST_VALUE() | - 파티션에서 가장 나중에 나오는 값을 구함 - MAX 함수를 사용해서 같은 결과를 구할 수 있음 |
| LAG() | 이전 행을 가져옴 |
| LEAD() | 윈도우에서 특정 위치의 행을 가져옴(기본값: 1) |
비율 관련 함수
비율 관련 윈도우 함수
| 함수 | 설명 |
|---|---|
| CUME_DIST() | 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율 조회(0~1 사이의 값) |
| PERCENT_RANK() | 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율 조회 |
| NTILE() | 파티션별로 전체 건수를 ARGUMENT 값으로 N등분 한 결과 조회 |
| RATIO_TO_REPORT() | 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회 |
테이블 파티션(Table Partition)
- 파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리하여 저장하는 기능
- 테이블의 데이터가 물리적으로 분리된 데이터 파일에 저장되면 입력, 수정, 삭제, 조회 성능이 향상
- 각각의 파티션 별로 독립적으로 괄리될 수 있음(파티션 별로 백업하고 복구가 가능하면 파티션 전용 인덱스 생성도 가능)
- 데이터를 조회할 때 데이터의 범위를 줄여 성능 향상
파티션 기법
- Range Partition: 테이블의 칼럼 중 값의 범위를 기준으로 여러 파티션으로 데이터를 나누어 저장하는 방법
- List Partition: 특정 값을 기준으로 분할하는 방법
- Hash Partition: DBMS가 내부적으로 해시 함수를 사용하여 데이터를 분할
- Composite Partition: 여러 개의 파티션 기법을 조합하여 사용하는 방법
파티션 인덱스
- Global Index: 여러 개의 파티션에서 하나의 인덱스 사용
- Local Index: 파티션 별로 각자의 인덱스 사용
- Prefixed Index: 파티션 키와 인덱스 키가 동일
- Non Prefixed Index: 파티션 키와 인덱스 키가 다름