SQLD를 준비하며 공부했던 내용으로, 2020 이기적 SQL 개발자(Developer) 이론서 + 기출문제를 참고했다.
조인(Join)
(1) EQUI(등가)조인(교집합)
조인은 여러 개의 릴레이션을 사용해 새로운 릴레이션을 만드는 과정
조인의 가장 기본은 교집합을 만드는 것
두 개의 테이블 간 일치하는 것을 조인
EQUI 조인은 EMP 테이블과 DEPT 테이블에서 DEPTNO 칼럼을 사용하여 같은 것을 조인함
조인문에 추가 조건 및 정렬을 할 수 있음
SELECT * FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO
AND EMP.ENAME LIKE '임%'
ORDER BY ENAME;
EQUI 조인을 한 후에 실행 계획을 확인해서 내부적으로 두 개의 테이블을 어떻게 연결했는지 확인할 수 있음
위의 예를 보면 DEPT 테이블과 EMP 테이블 전체를 읽은 다음에 (TABLE ACCESS FULL)해시 함수를 사용해서 두 개의 테이블을 연결한 것
EQUI 조인을 한 후에 실행 계획을 확인해서 내부적으로 두 개의 테이블을 어떻게 연결했는지 확인할 수 있음
위의 예를 보면 DEPT 테이블과 EMP 테이블 전체를 읽은 다음(TABLE ACCESS FULL)해시 함수를 사용해서 두 개의 테이블을 연결한 것, 해시 함수는 테이블을 해시 메모리에 적재한 후 해시 함수로써 연결하는 방법, 해시 조인은 EQUI 조인만 사용 가능한 방법
해시 조인: 해시 조인은 먼저 선행 테이블을 결정하고 선행 테이블에서 주어진 조건(WHERE구)에 해당하는 행을 선택함, 해당 행이 선택되면 조인 키(Join Key)를 기준으로 해시 함수를 사용해서 해시 테이블을 메인 메모리에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾음, 후행 테이블의 조인 키를 사용해서 해시 함수를 적용하여 해당 버킷을 검색함
(2) INNER JOIN
ON문을 사용해서 테이블을 연결
INNER JOIN구에 두 개의 테이블명을 서술하고 ON구조 조인 조건을 서술함
조인문에 추가 조건 및 정렬을 할 수 있음
SELECT * FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO
AND EMP.ENAME LIKE '임%'
ORDER BY ENAME;
(3) INTERSECT 연산
두 개의 테이블에서 교집합(공통된 값)을 조회
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT;
Non-EQUI(비등가) 조인
OUTER JOIN
두 개의 테이블 간에 교집합(EQUI JOIN을 조회하고 한쪽 테이블에만 있는 데이터도 포함시켜서 조회
ex) DEPT 테이블, EMP 테이블 OUTER JOIN하면 DEPTNO가 같은 것을 조회하고 DEPT 테이블에만 있는 DEPTNO도 포함시킴
이 때 왼쪽 테이블의 행만 포함하면 LEFT OUTER JOIN, 오른쪽 테이블의 행만 포함하면 RIGHT OUTER JOIN
FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN 모두를 하는 것
(+) 기호 사용
SELECT * FROM DEPT, EMP
WHERE EMP.DEPTNO (+) DEPT.DEPTNO;
LEFT OUTER JOIN과 RIGHT OUTER JOIN
LEFT OUTER JOIN: 두 개의 테이블에서 같은 것을 조회하고 왼쪽 테이블에만 있는 것 포함해서 조회
SELECT * FROM DEPT LEFT OUTER JOIN EMP
ON EMP.DEPTNO = DEPT.DEPTNO;
RIGHT OUTER JOIN: 두 개의 테이블에서 같은 것을 조회하고 오른쪽 테이블에만 있는 것 포함해서 조회
SELECT * FROM DEPT RIGHT OUTER JOIN EMP
ON EMP.DEPTNO = DEPT.DEPTNO;
CROSS JOIN
조인 조건구 없이 2개의 테이블을 하나로 조인함
조인구가 없기 때문에 카테시안 곱이 발생
행이 14개인 테이블과 행이 4개인 테이블을 조인하면 56개의 행 조회
CROSS JOIN은 FROM절에 'CROSS JOIN'구 사용
SELCT * FROM EMP CROSS JOIN DEPT;
UNION: 두 개의 테이블을 하나로 만드는 연산
두 개의 테이블의 칼럼 수, 칼럼의 데이터 형식 모두가 일치해야 함, 두 개의 테이블을 하나로 합치면서 중복된 데이터를 제거함, 정렬(SORT)과정 발생시킴
SELECT DEPTNO FROM EMP
UNION
SELECT DEPTNO FROM DEPT;
UNION ALL: 두 개의 테이블을 하나로 합치는 것, UNION처럼 중복을 제거하거나 정렬을 유발하지 않음
SELECT DEPTNO FROM EMP
UNION ALL
SELECT DEPTNO FROM DEPT;
차집합을 만드는 MINUS
MINUS 연산은 두 개의 테이블에서 차집합을 조회함, 먼저 쓴 SELECT문에는 있고 뒤에 쓰는 SELECT문에는 없는 집합을 조회함
DEPT와 EMP를 MINUS 연산하면 DEPT에만 있는 행을 조회함
MINUS와 동일한 연산은 EXCEPT (MS-SQL에서)
SELECT * FROM DEPT
MINUS
SELECT * FROM EMP;
계층형 조회(Connect by)
계층형으로 데이터를 조회할 수 있음
부장에서 차장, 차장에서 과장, 과장에서 대리 순으로 트리 형태의 구조를 위에서 아래로 탐색하면서 조회하는 것, 역방향 조회도 가능
트리 형태의 구조로 지릐를 수행하는 것으로 START WITH구는 시작조건을 의미하고 CONNECT BY PRIOR는 조인 조건임, Root 노드로부터 하위노드의 질의를 실행함
계층형 조회에서 MAX(LEVEL)을 사용하여 최대 계층 수를 구할 수 있음, 계층형 구조에서 마지막 Leaf Node의 계층값을 구함
MAX(LEVEL)이 4이면 트리의 최대 깊이는 4
SELECT MAX(LEVEL)
FROM Limbest.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
SELECT LEVEL, EMPNO, MGR, ENAME
FROM Limbest.EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
계층형 조회 결과를 명확히 보기 위해 LPAD 함수 사용
SELECT LEVEL, LPAD(' ', 4 * (LEVEL -1) ) ||EMPNO,
MGR, CONNECT_BY_ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
4LEVEL-1에서, ROOT이면 LEVEL값이 1이 되어 4(1-1) = 0 이라 LPAD(' ', 0)이므로 아무 의미 없음
LEVEL값이 2이면 4*(2-1)=4로, 왼쪽 공백 4칸을 화면에 찍음
LPAD는 트리 형태로 보기 위해서 사용한 것, LEVEL 값은 Root에 1을 되돌리고 그다음 자식은 2, 그다음 자식은 3이 나오기 때문
CONNECT BY 키워드
1) LEVEL: 검색 항목의 깊이를 의미함, 계층구조에서 가장 상위 레벨이 1이 됨
2) CONNECT_BY_ROOT: 계층구조에서 가장 최상위 값을 표시함
3) CONNECT_BY_ISLEAF: 계층구조에서 가장 최하위를 표시함
4) SYS_CONNECT_BY_PATH: 계층구조의 전체 전개 경로를 표시함
5) NOCYCLE: 순환구조가 발생지점까지만 전개됨
6) CONNECT_BY_ISCYCLE: 순환구조 발생 지점을 표시함
서브쿼리(Subquery)
Subquery는 SELECT문 내에 다시 SELECT문을 사용하는 SQL문
Subquery의 형태는 FROM구에 SELECT문을 사용하는 인라인 뷰(view)와 SELECT문에 Subquery를 사용하는 스칼라 서브쿼리(Scala Suqbquery) 등이 있음
WHERE구에 SELECT문을 사용하면 서브쿼리(Subquery), 서브쿼리 밖에 있는 SELECT문은 메인쿼리(Main Query)
SELECT *
FROM EMP
WHERE DEPTNO =
(SELECT DEPTNO FROM DEPT
WHERE DEPTNO = 10);
FROM구에 SELECT문을 사용하여 가상의 테이블을 만드는 효과를 얻을 수 있음
SELECT *
FROM (SELECT ROWNUM NUM, ENAME
FROM EMP) a
WHERE NUM < 5;
단일 행 서브쿼리와 다중 행 서브쿼리
SELECT ENAME, DNAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND EMP.EMPNO
IN (SELECT EMPNO FROM EMP
WHERE SAL > 2000);
SELECT *
FROM EMP
WHERE DEPTNO <= ALL(20, 30);
SELECT ENAME, DNAME, SAL
FROM EMP, DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND EXISTS (SELECT 1 FROM EMP
WHERE SAL >2000);
스칼라 subquery는 반드시 한 행과 한 칼럼만 반환하는 서브쿼리
만약 여러 행이 반환되면 오류 발생
SELECT ENAME AS "이름"
SAL AS "급여",
(SELECT AVG(SAL)
FROM EMP
) AS "평균급여"
FROM EMP
WHERE EMPNO=1000;
연관 Subquery는 Subquery 내에서 Main Query 내의 칼럼을 사용하는 것을 의미함
FROM EMP a
WHERE a.DEPTNO =
(SELECT DEPTNO FROM DEPT b
WHERE b.DEPTNO=a.DEPTNO);
그룹함수(GROUP FUNCTION)
GROUP BY의 칼럼에 대해서 Subtotal을 만들어 줌
ROLLUP을 할 때 GROUP BY구에 칼럼이 두 개 이상 오면 순서에 따라서 결과가 달라짐
SELECT DECODE(DEPTNO, NULL,'전체합계',DEPTNO),
SUM (SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO);
부서별, 직업별 ROLLUP을 실행하면 부서별 합계, 직업별 합계, 전체합계가 모두 조회됨
ROLLUP으로 실행되는 칼럼별로 Subtotal을 만들어 줌
SELECT DEPTNO, JOB,
SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB);
ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계값을 구분하기 위해서 만들어진 함수
SELECT DETPTNO, GROUPING(DEPTNO),
JOB, GROUPING(JOB), SUM(SAL)
FROM EMP
GROUP BY ROLLUP(DEPTNO, JOB)
GROUPING 함수의 기능을 사용하면 사용자가 필요로 하는 데이터를 SELECT문으로 작성하여 제공할 수 있음
GROUP BY에 나오는 칼럼의 순서와 관계없이 다양한 소계를 만들 수 있음
GROUP BY에 나오는 칼럼의 순서와 관계없이 개별적으로 모두 처리함
SELECT DEPTNO, JOB, SUM(SAL)
FROM EMP
GROUP BY GROUPING SETS(DEPTNO, JOB);
CUBE 함수에 제시한 컬럼에 대해서 결합 가능한 모든 집계를 계산함
다차원 집계를 제공하여 다양하게 데이터를 분석할 수 있게 함
조합할 수 있는 모든 경우의 수가 모두 조합됨
SELECT DETPNO, JOB, SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO, JOB);
윈도우 함수(Window Function)
SELECT WINDOW_FUNCTION(ARGUMENTS)
OVER (PARTITION BY 칼럼
ORDER BY WINDOWING절)
FROM 테이블명;
윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공함
1) RANK: 특정항목 및 파티션에 대해 순위를 계산함, 동일한 순위는 동일한 값이 부여됨
2) DENSE_RANK: 동일한 순위를 하나의 건수로 계산함, 2등이 2명이어도 3등이 있음
3) ROW_NUMBER: 동일한 순위에 대해서 고유의 순위를 부여함
SELECT ENAME, SAL,
RANK() OVER (ORDER BY SAL DESC) ALL RANK,
RANK() OVER (PARTITION BY JOB ORDER BY SAL
DESC) JOB_RANK
FROM EMP;
RANK함수는 순위를 계산하며 동일한 순위에는 같은 순위가 부여됨
윈도우 함수를 제공함
1) SUM: 파티션 별로 합계를 계산함
2) AVG: 파티션 별로 평균을 계산함
3) COUNT: 파티션 별로 행 수를 계산함
4) MAX와 MIN: 파티션 별로 최댓값과 최솟값을 계산함
SELECT ENAME, SAL,
SUM(SAL) OVER (PARTITION BY MGR) SUM MGR
FROM EMP;
행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있음
특정 위치의 행을 출력할 수 있음
1) FIRST_VALUE: 파티션에서 가장 처음에 나오는 값을 구함, MIN 함수를 사용해서 같은 결과를 구할 수 있음
SELECT DEPTNO, ENAME, SAL,
FIRST VALUE(ENAME) OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS
DEPT_A FROM EMP;
2) LAST_VALUE: 파티션에서 가장 나중에 나오는 값을 구함, MAX 함수를 사용해서 같은 결과를 구할 수 있음
SELECT DEPTNO, ENAME, SAL
LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND
UNBOUNDED FOLLOWING) AS
DEPT A FROM EMP;
3) LAG: 이전 행을 가지고 옴
SELECT DEPTNO, ENAME, SAL,
LAG(SAL) OVER(ORDER BY SAL DESC) AS PRE_SAL
FROM EMP;
4) LEAD: 윈도우에서 특정 위치의 행을 가지고 옴, 기본값은 1
SELECT DEPTNO, ENAME, SAL,
LEAD(SAL,2) OVER(ORDER BY SAL DESC) AS PRE_SAL
FROM EMP;
비율 관련 함수: 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등 조회 가능
1) CUME_DIST: 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회, 누적 분포상에 위치를 0~1사이의 값을 가짐
2) PERCENT_RANK: 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회
SELECT DEPTNO, ENAME, SAL,
PERCENT_RANK() OVER(PARTITION BY DEPTNO
ORDER BY SAL DESC) AS PERCENT SAL
3) NTILE: 파티션별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회
SELECT DEPTNO, ENAME, SAL,
NTILE(4) OVER(ORDER BY SAL DESC) AS N_TILE
FROM EMP;
4) RATIO_TO_REPORT: 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼값의 백분율을 소수점까지 조회
테이블 파티션
Partition 기능
Range Partition
List Partition
Hash Partition
파티션 인덱스
1) Global Index: 여러 개의 파티션에서 하나의 인덱스를 사용함
2) Local Index: 해당 파티션 별로 각자의 인덱스를 사용함
3) Prefixed Index: 파티션 키와 인덱스 키가 동일함
4) Non Prefixed Index: 파티션 키와 인덱스 키가 다름