SQLD 02_02 SQL 활용 요약

JOIN

EQUI 등가조인

조인은 여러 개의 릴레이션을 사용해 새로운 릴레이션을 만드는 과정이다

가장 기본은 교집합을 만드는 것으로 두 테이블 간 일치하는 것을 조인 !

 

'=' 로 두개의 테이블을 연결 경우

SELECT * FROM EMP, DPT WHERE EMP.NO = DPT.NO;

INNER JOIN

'ON' 를 사용해 테이블을 연결한다

SELECT * FROM EMP INNER JOIN DPT ON EMP.NO = DPT.NO;

INTERSECT 연산

두 테이블에서 교집합 ( 공통된 값 ) 를 조회한다

 

Non-EQUI 비등가조인

"=" 사용하지 않고 ">", "<", ">=", "<=" 등 사용하여 정확하게 일치하지 않는 것을 조인 !

 

OUTER JOIN

두 테이블간 교집합을 조회하고 한 쪽 테이블에만 있는 데이터도 포함하여 조회한다

왼쪽 테이블에만 있는 행도 포함하면 LEFT OUTER JOIN

오른쪽 테이블의 행만 포함시키면 RIGHT OUTER JOIN !

 

FULL OUTER JOIN 은 LEFT OUTER JOIN 과 RIGHT OUTER JOIN 모두를 사용하는 것으로

Oracle DB에서 "(+)" 기호를 사용한다

SELECT * FROM DPT, EMP WHERE EMP.NO (+)= DPT.NO;

CROSS JOIN

조건 없이 2개의 테이블을 하나로 조인한다

카테시안 곱이 발생

FROM 절에서 사용하면 된다

SELECT * FROM EMP CROSS JOIN DPT;

UNION , UNION ALL

두 테이블을 하나로 만든다 " 합집합 " 개념

두 테이블의 칼럼 수, 칼럼의 데이터 형식 모두가 일치해야 한다

UNION 중복된 데이터를 제거하고 정렬 과정이 존재하지만

UNION ALL 중복을 제거하거나 정렬이 존재하지 않는다 !

MINUS

두 테이블에서 차집합을 조회한다

---

SUBQUERY

select 문 내에 다시 select문을 사용하는 sql 문이다

FROM 절에서 SELECT 절을 사용하는 경우 인라인뷰

SELECT 절에서 서브쿼리를 사용하면 스칼라 서브쿼리

WHERE 절에서 SELECT 문을 사용하면 서브쿼리

서브쿼리 : 괄호에 묶인 부분

SELECT * FROM EMP WHERE DPTNUM = (SELECT DPTNUM FROM DPT WHERE DPTNUM = 10);

인라인뷰 : FROM 절의 SELECT 문

SELECT * FROM (SELECT ROWNUM NUM, ENAME FROM EMP) a WEHRE NUM < 5;

단일행 서브쿼리와 다중행 서브쿼리

단일행 서브쿼리는 반환하는 행이 1개고 비교연산자 ( =, <, <=, >=, <>) 사용한다

다중행 서브쿼리는 반환하는 행이 여러개이고 IN ANY ALL EXIST 를 사용한다

 

IN : 여러개의 행 중 하나만 참이 되어도 참이 되는 연산

ALL  : 메인쿼리와 서브쿼리의 결과가 모두 동일하면 참이 되는 연산

EXISTS : 어떤 데이터의 존재 여부( T, F ) 를 확인

---

GROUP FUNCTION

그룹함수 총정리 !

ROLLUP

GROUP BY ( 순서상관있음 ) 에 대하여 부분합을 만들어준다

소계 / 합계 등이 계산되면 GROUPING 함수는 0 을 반환한고 아니면 1을 반환

GROUPING SETS

GROUP BY ( 순서상관없음 ) 개별적으로 처리하여 다양한 소계를 만든다

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

쿼리는 DEPTNO JOB 을 각 그룹으로 합계를 계산한다

CUBE

결합 가능한 모든 집계를 계산한다

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

쿼리는 전체합계, 부서별합계, 직업별합계, 부서별/직업별 합계를 조회한다

---

WINDOW FUNCTION

윈도우 함수는 행과 행의 관계를 정의하기 위해 제공한다

순위, 합계, 평균, 행 위치 등을 조작할 수 있다

 

윈도우 함수 구조

SELECT 윈도우 함수(인수) OVER (PARTITION BY 칼럼명 ORDER BY WINDOWING절)
FROM 테이블명;

-- 첫번째 행부터 마지막 행까지
ROW BETWEEN UNBOUNDED PERCEDING AND UNBOUNDED FOLLOWING

-- 첫번째 행부터 현재 행까지
ROW BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

RANK FUNCTION

윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 순위함수를 제공

RANK : 동일한 순위는 동일한 값이 부여

DENSE_RANK : 동일한 순위를 하나의 건수로 계산

ROW_NUMBER : 동일한 순위에 대해 고유의 순위를 부여 = 행의 개수

집계함수

SUM / AVG / COUNT : 파티션 별 합계 / 평균 / 행 수 계산

MAX / MIN : 파티션 별 최대 /  최소값 계산

행 순서 관련 함수

특정 위치의 행을 출력할 수 있다

FIRST_VALUE / LAST_VALUE  : 가장 처음값 = MIN() / 가장 나중값 = MAX()

LAG / LEAD : 이전 행, 특정 위치의 행 ( 기본값 : 1 )

---

TABLE PARTITION

파티션은 대용량의 테이블을 여러 개의 데이터 파일에 분리해서 저장한다

입력 / 수정 / 삭세 / 조회의 성능이 향상된다 ( 범위가 줄어서 )

파티션별로 독립적인 관리가 가능해진다

 

RANGE PARTITION : 값의 범위를 기준으로 나누어 저장

LIST PARTITION :  특정 값을 기준으로 나누어 저장

HASH PARTITION : DBMS 내부적으로 해시함수를 사용해서 분할

COMPOSITE PARTITION : 여러 개의 파티션 기법을 조합해서 사용

파티션 유형

구분	내용
Global Index	여러 개의 파티션에서 한 개 사용
Local Index	해당 파티션 별 각자 사용
Prefixed Index	파티션 키와 키가 동일
Non Prefixed Index	파티션 키와 키가 다름

---

참고

https://cafe.naver.com/sqlpd

데이터 전문가 포럼 (빅데이터분석기사... : 네이버 카페