[SQLD] SQL 활용

JOIN

여러 개의 테이블을 사용해서 새로운 테이블을 만드는 것

• EQUI JOIN(등가 조인)

SELECT * FROM emp, dept
WHERE emp.deptno = dept.deptno;

=를 사용해서 테이블을 연결한다.

 

• INNER JOIN

SELECT * FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno;

ON을 사용해서 테이블을 연결한다.

 

• INTERSECT 연산

SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

두 테이블의 교집합을 출력한다.

 

• Non-EQUI JOIN(비등가 조인)

=을 사용하지 않고 >, <, >=, <= 등을 사용한다.

정확하게 일치하지 않는 것을 조인한다.

 

• OUTER JOIN

LEFT OUTER JOIN: 두 테이블에서 같은 것을 조회하고 왼쪽 테이블에만 있는 것을 포함해서 조회한다.

RIGHT OUTER JOIN: 두 테이블에서 같은 것을 조회하고 오른쪽 테이블에만 있는 것을 포함해서 조회한다.

 

• CROSS JOIN

SELECT * FROM emp CROSS JOIN dept;

조건구 없이 테이블을 하나로 조인한다.

카테시안 곱이 발생해서 두 테이블의 행의 수를 곱한만큼의 행이 조회된다.

 

• UNION 연산

두 테이블을 하나로 합친다. 합치면서 중복을 제거한다. UNION ALL을 사용하면 중복을 제거하지 않는다.

두 테이블의 칼럼수, 데이터 타입이 모두 일치해야 한다.

 

• MINUS 연산

두 테이블의 차집합을 조회한다.

 

서브쿼리

SELECT문 내에서 또 다른 SELECT문을 사용하는 것

• WHERE구에 SELECT문 사용

SELECT * FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE deptno = 10);

 

• FROM구에 SELECT문 사용 (인라인 뷰, Inline view)

SELECT * FROM (SELECT ROWNUM num, ename FROM emp) a
WHERE num < 5;

FROM구에 SELECT문을 사용한 것을 Inline view라고 한다. 가상의 테이블을 만드는 효과가 있다.

 

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

단일 행 서브쿼리	- 서브쿼리 실행 결과가 하나의 행만 조회된다. - 비교 연산자 =, <, >, <=, >=, <>를 사용한다.
다중 행 서브쿼리	- 서브쿼리 실행 결과가 여러 개의 행이 조회된다. - IN, ANY, ALL, EXISTS를 사용한다.

 

IN(서브쿼리)	- 메인쿼리의 비교조건이 서브쿼리의 결과 중 하나라도 동일하면 참이다.
ALL(서브쿼리)	- 메인쿼리와 서브쿼리의 결과가 모두 동일하면 참이다.
ANY(서브쿼리)	- 메인쿼리와 비교조건이 서브쿼리의 결과 중 하나 이상 동일하면 참이다.
EXISTS(서브쿼리)	- 메인쿼리와 서브쿼리의 결과가 하나라도 존재하면 참이다.

 

파티션

대용량의 테이블을 여러 개의 데이터 파일에 나누어 저장한다.

Range 파티션	- 칼럼값의 범위를 기준으로 나누어 저장한다. - 예) sal 속성값 2000~4000은 Datafile1.dbf에 저장하고 5000~7000은 Datafile2.dbf에 저장한다.
List 파티션	- 특정 값을 기준으로 나누어 저장한다. - 예) deptno 속성값이 10인 것은 Datafile1.dbf에 저장하고 20인 것은 Datafile2.dbf에 저장한다.
Hash 파티션	- DBMS가 내부적으로 해시함수를 사용해서 나누어 저장한다.

 

파티션 인덱스

Global Index	- 여러 개의 파티션에서 하나의 인덱스를 사용한다.
Local Index	- 파티션 별로 각자의 인덱스를 사용한다.
Prefixed Index	- 파티션 키와 인덱스 키가 동일하다.
Non Prefixed Index	- 파티션 키와 인덱스 키가 다르다.

 

윈도우 함수

행과 행 간의 관계를 정의하기 위해서 제공되는 함수이다.

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

SELECT 윈도우함수() OVER (PARTITION BY 칼럼 ORDER BY 칼럼)
FROM 테이블;​

 

• 순위 함수

ROW_NUMBER	- 동일한 순위에 대해서 고유의 순위를 부여한다. 예) 1, 2, 3, 4
RANK	- 동일한 순위는 동일한 값이 부여된다. 예) 1, 2, 2, 4
DENSE_RANK	- 동일한 순위를 하나의 건수로 계산한다. 예) 1, 2, 2, 3

• 집계 함수

SUM	- 파티션 별로 합계를 계산한다.
AVG	- 파티션 별로 평균을 계산한다.
COUNT	- 파티션 별로 행의 수를 계산한다.
MAX, MIN	- 파티션 별로 최대값, 최소값을 계산한다.

• 행 순서 관련 함수

FIRST_VALUE	- 파티션에서 가장 처음에 나오는 값을 구한다. - MIN 함수로 같은 결과를 구할 수 있다.
LAST_VALUE	- 파티션에서 가장 나중에 나오는 값을 구한다. - MAX 함수로 같은 결과를 구할 수 있다.
LAG	- 이전 행을 가지고 온다.
LEAD	- 특정 위치의 행을 가지고 온다. 기본값 1

• 비율 관련 함수

 

그룹 함수

• ROLLUP: GROUP BY 칼럼에 대해서 subtotal을 계산한다.
• CUBE: 결합 가능한 모든 집계를 계산한다.
• GROUPING SET: GROUP BY에 나오는 칼럼의 순서와 관계없이 개별적으로 처리한다.
• GROUPING: 그룹 함수에 의해서 소계, 합계 등이 계산되면 1을 반환하고 그렇지 않으면 0을 반환한다.