Subquery

1. SUBQUERY의 종류
- 하나의 질의문 내부에 하나 이상의 다른 질의문이 포함되어 그 결과를 이용할 때, 내부에 포함된 쿼리문을 의미한다.

(Scalar Subquery, Inline View, Nested Subquery, Correlated Subquery 등이 Subquery의 속한다.)
- GROUP BY를 제외하고는 모든 절에는 SUBQUERY가 위치할 수 있다.

- Scalar Subquery는 SELECT, FROM, WHERE, ORDER BY에 모두 존재 가능.
- Inline View는 FROM절에만 존재하며,
- Nested Subquery, Correlated Subquery는 WHERE절에만 존재


2. NESTED SUBQUERY
- 서브쿼리가 WHERE 절에서 사용된 경우, Nested Subquery라 함.
- Nested Subquery가 Main Query보다 먼저 실행될 때 속도를 낼 수 있는 유형임
- 단, 서브쿼리 쪽에서 조회하는 Main Query 값에 인덱스가 없으면 서브쿼리는 먼저 실행되지 않음

ex)
SELECT empno, ename
FROM emp
WHERE deptno = (SELECT deptno 
                FROM dept
WHERE dname = 'SALES');
-> deptno에 인덱스가 있다면 Nested Subquery가 먼저 실행,
     다만, 인덱스가 있더라도 인덱스를 사용할 수 없는 환경(!= 연산자 등)이라면 Nested Subquery가 먼저 실행되지 않음.

위 쿼리의 실행계획은 아래 그림과 같다. Subquery 3번이 먼저 실행된다.

3. CORRELATED SUBQUERY

- 서브쿼리가 WHERE절에서 사용되고 메인 쿼리에서 데이터를 하나씩 읽을 때마다 서브쿼리가 실행되어 데이터를 리턴하는 서브쿼리를 의미함.
- 메인쿼리에서 데이터를 읽고 있는 Row 수만큼 서브쿼리가 실행됨.
(메인쿼리보다 나중에 실행된다.)

ex)
SELECT ename, empno
FROM emp
WHERE EXISTS  (SELECT 'X' FROM dept
 WHERE dept.deptno = emp.deptno
 AND dept.dname = 'SALES');
dept.deptno = emp.deptno 때문에 emp에서 데이터를 먼저 가져온다.

위 쿼리의 실행계획은 아래와 같다.

4. SCALAR SUBQUERY

- 단 하나의 데이터와 단 하나의 칼럼에 대한 정보를 리턴함
- Scalar Subquery 사용 위치
(Select List 항목, 함수의 인자,WHERE절의 조건, Order by 절, case조건절, case 결과절)
- S결과 값이 0개인 경우, NULL / 결과 값이 2개인 경우, ORA-1427를 반
- 메인쿼리보다 나중에 실행된다.

ex)
SELECT  e.ename,
d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

 

- 위 쿼리는 아래와 같이 변경 가능.

SELECT  e.ename
(SELECT d.dname
 FROM dept d
 WHERE d.deptno = e.deptno) AS dname
FROM emp e;

- 아래와 같이 PL/SQL로 함수(User Define Function)를 만들 수도 있음.

CREATE OR REPLACE FUCTION f_dnm(a_dno IN dept.deptno%TYPE)
RETURN VARCHAR2
RESULT_CACHE
RELIES ON(dept)
AS
 h_dnm dept.dname%type := NULL;
BEGIN
 SELECT dname INTO h_dnm
 FROM dept
 WHERE deptno = a_dno;

RETURN h_dnm;
END;
/

SELECT  ename,
f_dnm(deptno) AS dname
FROM emp;


5. ROLLUP() & CUBE()

 

ROLLUP() : 데이터의 총계를 나타낼 때 사용하는 함수

ROLLUP 함수의 예시

CUBE() : 데이터의 소계를 나타낼 때 사용하는 함수

CUBE 함수의 예

예시A) 
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY d.dname, e.job -> R
ORDER BY 1, 2;

예시A 쿼리의 결과

예시B)
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY ROLLUP(d.dname, e.job)
ORDER BY 1, 2;

예시B 쿼리의 결과

예시C)
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY CUBE(d.dname, e.job)
ORDER BY 1, 2;

예시C 쿼리의 결과

6. GROUPING SETS()

- 여러 개의 GROUP BY 쿼리를 동시에 실행한 것과 같은 결과를 나타냄
- GROUPING SETS()로 ROLLUP(), CUBE(), ROLLUP()&CUBE() 구현이 가능함.

ex) 
SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY GROUPING SETS((d.dname, e.job), (d.dname), ())
ORDER BY 1, 2;
-> GROUP BY ROLLUP(d.dname, e.job)과 동일한 결과

SELECT  d.dname, e.job
COUNT(*) AS "Empl Cnt",
SUM(e.sal) AS "Tot Sal"
FROM dept d, emp e
WHERE  d.deptno = e.deptno
GROUP BY GROUPING SETS((d.dname, e.job), (d.dname), (e.job), ())
ORDER BY 1, 2;
-> GROUP BY CUBE(d.dname, e.job) 과 동일한 결과 


7. ANALYTIC FUNCTIONS

- 행과 행 간의 관계를 정의하거나 비교, 연산하기 위해 사용함

SELECT Analytic_Function (arguments) OVER
([Partition By 칼럼] [Order by 절] [Windowing 절])
FROM 테이블명
WHERE 
;

Argument : 함수에 따라 0 ~ 3개의 인자가 지정됨.
Partition By 절 : 전체 집합을 기준에 의해 소그룹으로 나눔.
Order By 절 : 어떤 항목에 대한 정렬 기준을 기술함.
Windowing 절 : 함수에 의해서 제어하고자 하는 데이터 범위를 정의함.

*종류
그룹 내 데이터 순위 : ROW_NUMBER, RANK, DENSE_RANK
그룹 내 비율 : RATIO_TO_REPORT


* EMP 테이블에서 부서(DEPTNO) 별로 급여가 높은 사람 순으로 순위를 구하기 위한 SQL
ex)
SELECT  deptno, ename, sal,
ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) As rno,
RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) as rk,
DENSE_RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) as drk
FROM emp;

ROW_NUMBER() OVER, RANK() OVER, DENSE_RANK() OVER의 차이

* EMP 테이블에서 부서 별로 각 사원을 기준으로 해서 급여에 대한 누계를 구하기 위한 SQL

ex) 
SELECT  deptno, ename, sal,
SUM(sal) OVER(PARTITION BY deptno ORDER BY sal ASC ROWS UNBOUNDED PRECEDING) AS csum
FROM emp;

* EMP테이블에서 업무별로 총급여를 기준으로 각 사원의 급여를 백분율(소수점)을 구하기 위한 SQL

ex)
SELECT  job, ename, sal
RATIO_TO_REPORT(sal) OVER (PARTITION BY job) AS rr
FROM emp;