- 서브 쿼리란? 조인의 또 다른 형태로 하나의 SQL 내에 존재하는 또 다른 SELECT 문을 의미한다.
- 서브 쿼리의 위치는 SELECT, FROM, WHERE, ORDER BY 절에서 모두 사용가능하나 GROUP BY 절에서는 사용할 수가 없다.
- 메인 쿼리와 서브 쿼리의 관계 : 메인 쿼리는 가장 바깥쪽에서 동작되는 쿼리문이고, 서브 쿼리는 SELECT, FROM, WHERE, ORDER BY 절 내에서 동작되는 쿼리문이다. 서브 쿼리 종류의 따라서 메인 쿼리와 서브 쿼리의 실행 순서가 결정된다.
- 예시메인 쿼리와 서브 쿼리
-- 메인 쿼리
SELECT empno, ename
FROM emp
WHERE deptno IN (
-- 서브 쿼리
SELECT deptno
FROM dept
WHERE dname = 'DEV')
; - 서브 쿼리의 종류
- NESTED 서브 쿼리 : WHERE 절에서 동작되는 서브 쿼리로 메인 쿼리와의 연결고리가 없이 동작되는 서브 쿼리를 의미한다.
- 예시NESTED 서브 쿼리
-- 메인 쿼리
SELECT empno, ename
FROM emp
WHERE deptno = (
-- NESTED 서브 쿼리
SELECT deptno
FROM dept
WHERE dname = 'DEV')
; - 그림상황에 따라서 메인 쿼리가 먼저 실행될 수도 있다.
- CORRELATED 서브 쿼리 : WHERE 절에서 동작되는 서브 쿼리로 메인 쿼리와의 연결고리가 있어 동작 시에 메인 쿼리보다 나중에 실행되게 되는 서브 쿼리로 메인 쿼리에서 정보를 받아와야 동작이 가능한 서브 쿼리를 의미한다.
- 예시CORRELATED 서브 쿼리
-- 메인 쿼리
SELECT empno, ename
FROM emp
WHERE EXISTS (
-- CORRELATED 서브 쿼리
SELECT 'X'
FROM dept
WHERE dept.deptno = emp.deptno
AND dept.dname = 'SALES')
; - 그림
- Inline View : FROM 절 내에 있는 서브 쿼리를 의미한다.
- 예시Inline View
SELECT empno,
deptno
FROM ( -- Inline View
SELECT empno,
deptno
FROM emp
WHERE ename = 'SMITH')
WHERE empno = '1234'
; - SCALAR 서브 쿼리 : SELECT, WHERE, ORDER BY 절 모두에서 사용하며, 단 하나의 데이터와 단 하나의 컬럼을 가져와야하는 서브 쿼리를 의미하며, 함수의 성질을 갖는다.
- 예시SCLAR 서브 쿼리
-- 메인 쿼리
SELECT e.ename,
(-- SCALAR 서브 쿼리
SELECT d.dname
FROM dept d
WHERE d.deptno = e.deptno) as dname
-- 1 Row, 1 Column
FROM emp e
;
-- 함수로 생성하여 사용
CREATE OR REPLACE FUNCTION f_dmn(a_dno IN dept.deptno%TYPE)
RETURN VARCHAR2
RESULT_CACHE
RELIES_ON(dept)
AS
h_dnm dept.dname%type := NULL;
BEGIN
SELETE dname INTO h_dnm
FROM dept
WHERE deptno = a_dno;
RETURN h_dnm;
END;
/
SELECT ename,
f_dnm(deptno) as dname
FROM emp
; - 그림
- 함수
- 자주 사용하는 함수
- 합계 출력하는 함수로 GROUP BY 절에서 사용된다.
- ROLLUP() : 괄호 내에 컬럼을 기준으로 합계를 구하는 함수이다.
- CUBE() : 괄호 내에 컬럼을 기준으로 합계와 소계를 모두 구하는 함수이다.
- GROUPING SETS() : 괄호 내에 컬럼을 기준으로 합계와 소계를 모두 구하는 함수로 ROLLUP(), CUBE() 함수의 기능을 모두 사용 가능하다.
- 분석 함수 (Analytical Function)
- 구문 형식은 아래와 같다.Analytical Function 형식
SELECT Anlytical_Function (arguments) OVER ([Partition By 컬럼] [Order By 절] [Windowing 절])
FROM 테이블 명
WHERE 조건 - 구문 형식에 대한 설명은 아래와 같다.
- Arguments : 함수에 따라서 0~3개의 인자가 지정된다.
- Partition By 절 : 전체 집합을 기준에 의해 소그룹으로 나눈다.
- Order By 절 : 어떤 항목에 대한 정렬 기준을 기술한다.
- Windowing 절 : 함수에 의해서 제어하고자 하는 데이터 범위를 정의하며, 해당 절에 아무것도 없는 경우 데이터 범위는 전체를 의미한다.
- Row를 사용한 범위 선정 방법
- Range를 사용한 범위 선정 방법
- 함수 종류
- ROW_NUMBER() : 순서대로 Row 수를 나타낸다.
- RANK() : 순서대로 순위를 매기는 함수로 동일 데이터에 대해서 동일한 순위를 매기며, 그 이후는 떨어진만큼의 순서를 나타낸다. ( 1, 1, 1, 4)
- DENSE_RANK() : 순서대로 순위를 매기는 함수로 동일 데이터에 대해서 동일한 순위를 매기며 그 이후는 순서대로 진행한다. (1, 1, 1, 2)
- SUM() : 테이블에 대해서 기준별로 누계를 구한다.
- RATIO_TO_REPORT() : Partition By 절만 존재하며 기준에 대한 백분율을 구한다.
참고도서 : 실전사례로 살펴보는 SQL 튜닝비법
