[SQLD Part3] 02 SQL 활용

1 조인(Join)

1.1 EQUI JOIN (등가 교집합)
- '='을 사용해서 연결

SELECT *
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPTNO.DEPTNO;

1.1.2 INNER JOIN
- 'INNER JOIN'이라는 텍스트를 사용하고 where가 없음?

SELECT *
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
AND EMP.ENAME LIKE '임%'
ORDER BY ENAME;

* 해시조인
- 선행테이블에서 주어진 조건에 해당하는 행을 선택
- 해시 테이블을 메인 메모리에 생성
- 후행 테이블에서 주어진 조건에 만족하는 행을 찾음
- 조인키를 사용하여 해시함수 적용. 해당 버킷 검색
 
1.1.3 INTERSECT 연산
- 교집합 조회, 두 테이블에서 공통값 조회

SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;

 
1.2 NON-EQUI JOIN (비등가 조인)
- '=' 말고 '>','<','>=','<='을 사용
- 정확하게 일치하지 않는 것을 조인
 
1.3 OUTER JOIN
- 교집합을 조회하고 한쪽테이블에만 있는 데이터를 포함시킨다.

SELECT *
FROM DEPT, EMP
WHERE EMP.DEPTNO (+)= DEPT.DEPTNO;

-- LEFT OUTER JOIN
-- DEPT 테이블을 모두 가져오고 EMP 테이블을 매칭한 후 값 없으면 null로
SELECT *
FROM DEPT LEFT OUTER JOIN EMP
ON EMP.DEPTNO = DEPT.DEPTNO;

-- RIGHT OUTER JOIN
-- EMP 테이블을 모두 가져오고 DEPT 테이블을 매칭한 후 값 없으면 null로
SELECT *
FROM DEPT LEFT OUTER JOIN EMP
ON EMP.DEPTNO = DEPT.DEPTNO;

-- FULL OUTER JOIN
-- 양쪽 테이블 전부 가져오고 값이 매칭됐지만 없는 것은 null로
SELECT *
FROM DEPT FULL OUTER JOIN EMP
ON EMP.DEPTNO = DEPT.DEPTNO;

1.4 CROSS JOIN
- 조인 조건구 없이 2개 테이블 결합
- 카테시안 곱이 발생해서 14개 행 테이블과 4개 행 테이블이 조인하면 56개의 행이 조회된다.

SELECT *
FROM DEPT CROSS JOIN EMP;

SELECT *
FROM DEPT, EMP; -- 카데시안 곱 발생

1.5 UNION을 사용한 합집합 구현
1.5.1 UNION
- 두개의 테이블을 하나로
- 컬럼수와 데이터 타입이 일치해야한다.
- 중복된 데이터를 제거하고 정렬됨

-- 같은 테이블을 합쳐서 중복되지 않는 데이터를 만드려는것?
SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM EMP;

1.5.2 UNION ALL
- UNION과 같지만 중복제거나 정렬X

SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM EMP;

1.6 차집합을 만드는 MINUS
- MINUS연산은 차집합이다.
- MS-SQL에서는 EXCEPT

-- DEPT만 가지고 있는 데이터 보여주기
SELECT DEPTNO FROM DEPT
MINUS
SELECT DEPTNO FROM EMP;

2 계층형 조회(CONNECT BY)

SELECT MAX(LEVEL) -- 최대계층수
FROM Limbest.EMP
START WITH MGR IS NULL -- 시작조건
CONNECT BY PRIOR EMPNO = MGR; -- 조인조건(이전 행의 EMPNO과 현재 행의 MGR이 같은 것)

-- EMPNO:직번 MGR:관리자(이전 행에 있던 사람의 직번)
-- MGR이 null인 사람은 회장이라서
-- LEVEL: 검색 항목의 깊이

SELECT LPAD(' ',(LEVEL-1)*5)||ENAME -- ENAME을 문자열로 합쳐서 조회
FROM EMP
START WITH MGR IS NULL
CONNET BY PRIOR EMPNO = MGR;

-- LEVEL이 0이면 왼쪽에 아무것도 안붙임
-- LEVEL이 1이면 왼쪽에 빈값을 5개
-- LEVEL이 2이면 왼쪽에 빈값을 10개

- LPAD(문자열X의 왼쪽, 결과값의 총 byte 수, 채울문자열)
- RPAD(문자열X의 오른쪽, 결과값의 총 byte 수, 채울문자열)
 
* CONNECT BY 키워드
- CONNECT_BY_ROOT : 계층구조에서 최상위 값
- CONNECT_BY_ISLEAF : 계층구조에서 최하위 값
- SYS_CONNECT_BY_PATH : 계층구조의 전체 전개 경로를 표시
- NOCYCLE : 순환구조가 발생지점까지 전개
- CONNECT_BY_ISCYCLE : 순환구조 발생지점 표시
 
* 계층형 조회
- START WITH 조건 : 계층 전개의 시작 위치 지정
- PRIOR 부모 = 자식 : 부모에서 자식으로 순방향 검색
- PRIOR 자식 = 부모 : 자식에서 부모로 역방향 검색
- NOCYCLE : 중복조회 되지 않게
- Order siblings by 컬럼명 : 형제노드 사이에서 정렬 수행 (order by 대신 이것을 써야 정렬이됨)
 

3 서브쿼리

3.1 Main query와 Subquery
- 스칼라 서브쿼리 : select문의 서브쿼리
- 인라인뷰 : from 절에 select절을 씀
- 서브쿼리 : where 구에 서브쿼리
- 메인쿼리 : 서브쿼리 밖에 있는 select문
3.2 단일 행 서브쿼리
- 비교연산자를 사용하여 한 행만 조회
3.3 다중 행 서브쿼리
- 다중행 비교연산자를 사용하여 여러 개의 행 조회
* 다중 행 비교연산자
- IN : 서브쿼리 결과 중 하나만 동일해도 참

SELECT *
FROM EMP
WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

-- DEPTNO 별로 나온 SAL 중에 하나라도 참이면 TRUE

- ALL : 서브쿼리 결과와 모두 동일하면 참

SELECT *
FROM EMP
WHERE SAL < ALL(SELECT SAL FROM EMP WHERE DEPTNO = 30);

-- 30번 부서의 모든 급여들보다 작은 급여 출력

- ANY : 서브쿼리 결과 중 하나 이상 동일하면 참

SELECT *
FROM EMP
WHERE SAL < ANY(SELECT SAL FROM EMP WHERE DEPTNO = 30)

-- 30번 부서 사원들의 최대 급여보다 적은 급여를 받는 사원 정보 출력
-- 30번 부서 사원들이 갖고 있는 급여들 각각보다 적은 급여를 찾게 되기때문에
-- 결론은 급여 중 가장 높은 최대 급여보다 적은 급여를 받는 모든 사원 정보가 출력된다.

- EXISTS : 서브쿼리의 결과값으로 메인 쿼리를 수행할지말지 선택할 때 사용. 참이되면 메인쿼리 수행

SELECT *
FROM EMP
WHERE EXISTS (SELECT * FROM EMP WHERE DEPTNO = 10);

-- 서브쿼리의 결과 값이 존재하면 메인쿼리를 수행하여 EMP 테이블이 전부 조회

3.4 스칼라 서브쿼리
- 한 행과 한 컬럼을 반환하는 서브쿼리. 여러행 반환되면 오류.

SELECT ENAME AS "이름",SAL AS "급여",(SELECT AVG(SAL) FROM EMP) AS "평균급여"
FROM EMP
WHERE EMPNO = 1000;

3.5 연관 서브쿼리 (Correlated Subquery)
- 메인쿼리 내의 컬럼을 서브쿼리에서 사용

FROM EMP a
WHERE a.DEPTNO =
	(SELECT DEPTNO FROM DEPT b WHERE b.DEPTNO=a.DEPTNO);

* sql 개선 측면에서 서브쿼리
- Access 서브쿼리 : 쿼리의 변형이 없고 메인쿼리의 제공다 역할을 하는 서브쿼리
- Filter 서브쿼리 : 쿼리의 변형이 없고 확인자 역할을 하는 서브쿼리
- Early Filter 서브쿼리 : 쿼리의 변형이 없고 서브쿼리가 먼저 실행하여 데이터를 걸러낸다.

4 그룹함수

4.1 ROLLUP
- Group by 컬럼에 대해 각 합계를 만들어준다.

SELECT DECODE(DEPTNO,NULL,'전체합계',DEPTNO),SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO);

4.2 GROUPING
- ROLLUP, CUBE, GROUPING SETS 에서 생성되는 합계를 구분하기 위해 사용.

SELECT DEPTNO,GROUPING(DEPTNO),JOB,GROUPING(JOB),SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);

-- 전체 합계는 둘다 1/1, JOB 합계는 GROPUING(JOB)에만 1

SELECT DEPTNO, DECODE(GROUPING(DEPTNO),1,'전체합계') TOT, JOB, DECODE(GROUPING(JOB),1,'부분합계') T_DEPT, SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB);

4.3 GROUPING SETS
- 컬럼의 순서와 상관없는 다양한 합계. 각 컬럼별 데이터별 합계

SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO,JOB);

4.4 CUBE
- 결합 가능한 모든 집계 계산
- 부서별 합계, 직업별 합계, 부서별 직업별 합계

SELECT DEPTNO,JOB,SUB(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO,JOB);

5 윈도우 함수

5.1 윈도우 함수
- 행과 행 간의 관계를 정의하기 위해 제공하는 함수
- 순위, 합계, 평균, 행 위치 등 조작

SELECT WINDOW_FUNCTION(ARGUMENTS) OVER(PARTITION BY 컬럼 ORDER BY WINDOWING절)
FROM 테이블명;

SELECT EMPNO, ENAME, SAL,
SUM(SAL) OVER (ORDER BY SAL
		ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) TOTSAL
FROM EMP;

* WINDOWING
- ROWS : 물리적 행의 집합
- RANGE : 논리적 주소에 의한 행의 집합
- BETWEEN ~ AND : 윈도우의 시작과 끝 위치
- UNBOUNDED PRECEDING : 첫번째 행
- UNBOUNDED FOLLOWING : 마지막 행
- CURRENT ROW : 현재 행
* PARTION BY는 GROUP BY와 다르게 집합을 나누지만 행을 줄여서 정리하지는 않는다.
 
5.2 순위 함수
- RANK : 순위 계산. 동일 순위는 동일한 값 부여. ex) 1등,2등,2등,4등
- DENSE_RANK : 동일 순위 하나의 건수로 계산. ex) 1등,2등,2등,3등
- ROW_NUMBER : 동일 순위 고유 순위 부여. ex) 1등,2등,3등,4등

SELECT EMPNO, ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) ALL_RANK, -- 1등,2등,2등,4등
DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK -- 1등,2등,2등,3등
FROM EMP;

5.3 집계 함수
- SUM : 합계. ex) 파티션을 관리자에 만들면(PARTITION BY MGR) 관리자의 합계 (SUM(SAL))를 계산
- AVG : 평균
- COUNT : 행 수
- MAX와 MIN : 최댓값과 최소값
 
5.4 행 순서 함수
- FIRST_VALUE : 파티션의 가장 처음 값
- LAST_VALUE : 파티션의 가장 마지막 값
- LAG : 이전 행을 가져옴
- LEAD : 특정 위치의 행을 가져온다. 기본값이 1. 
 
5.5 비율 관련 함수
- PERSENT_RANK : 첫번째 행 0, 마지막 행 1, 중간은 행의 순서별로 백분율 표시
- NTILE : 파티션별로 N등분 한 결과를 조회
- RATIO_TO_REPORT : SUM에 대한 행 별 칼럼값의 백분율을 소수점까지 조회
- CUME_DIST : 누적 백분율 조회

6 테이블 파티션

6.1 파티션 기능
- 파티션 : 대용량 테이블을 여러 개 데이터 파일로 분리 저장
=> 성능 향상 (입력,조회,수정,삭제)
=> 독립적 관리 가능(인덱스 생성, 백업, 복구)
=> DB 관리 단위인 테이블 스페이스 간에 이동이 가능?
 
6.2 Range Partition
- 값의 범위를 기준으로 데이터 파일 분리
ex) SAL이 2000~4000 사이면 1번 파일, 5000~7000이면 2번 파일
 
6.3 List Partition
- 특정 값을 기준으로 데이터 파일 분리
ex) DEPTNO이 10번인 것 1번 파일, 20번인 것 2번 파일
 
6.4 Hash Partition
- 해시함수를 사용해서 데이터 분할 (시스템이 알아서 분할)
유사) Composite Partition (여러개의 파티션 기법 조합)
 
6.5 파티션 인덱스
① Global Index : 여러개 파티션에서 하나의 인덱스 사용
② Local Index : 해당 파티션 별로 각자의 인덱스 사용
③ Prefixed Index : 파티션 키와 인덱스 키가 동일. 파티션 키를 이용해서 인덱스를 만든다?
④ Non Prefixed Index : 파티션 키와 인덱스 키가 다르다.