12.Subquery
by SANGGI JEON
Subquery
- 하나의 SQL 질의문 속에 다른 SQL 질의문이 포함되어 있는 상태
- ORACLE에서 Subquery의 결과 값을 Chache에 저장 해놓는다.
- FROM절에 있는 Subquery는 INLINE VIEW라고 함
SELECT ename
FROM emp
WHERE sal > ( SELECT sal
from emp
WHERE ename = 'SCOTT')
## 결과 값
ENAME
--------------------
KING
1. Single-Row Subquery
- Subquery의 결과가 한 ROW인 경우
- Single-Row Operator를 사용해야 함 (=, >, >=, <, ,=, <>)
SELECT ename, sal, deptno
FROM emp
WHERE ename = (SELECT MIN(ename) FROM emp);
## 결과 값
ENAME SAL DEPTNO
-------------------- ---------- ----------
ADAMS 1100 20
SELECT ename, sal
FROM emp
WHERE sal < (SELECT AVG(sal) FROM emp);
## 결과 값
ENAME SAL
-------------------- ----------
SMITH 800
ALLEN 1600
WARD 1250
MARTIN 1250
TURNER 1500
ADAMS 1100
JAMES 950
MILLER 1300
SELECT ename, deptno
FROM emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE dname = 'SALES');
## 결과 값
ENAME DEPTNO
-------------------- ----------
ALLEN 30
WARD 30
MARTIN 30
BLAKE 30
TURNER 30
JAMES 30
2. Multi-Row Subquery
- Subquery의 결과가 둘 이상의 Row
- Multi-Row에 대한 연산을 사용해야 함 (ANY, ALL, IN, EXIST 등)
SELECT ename, sal, deptno
FROM emp
WHERE ename IN (SELECT MIN(ename)
FROM emp
GROUP BY deptno);
## 결과 값
ENAME SAL DEPTNO
-------------------- ---------- ----------
ALLEN 1600 30
CLARK 2450 10
ADAMS 1100 20
SELECT ename, sal, deptno
FROM emp
WHERE ename = ANY (SELECT MIN(ename)
FROM emp
GROUP BY deptno);
## 결과 값
ENAME SAL DEPTNO
-------------------- ---------- ----------
ALLEN 1600 30
CLARK 2450 10
ADAMS 1100 20
3. Correlated Query
3.1. Outer Query 와 Inner Query
- Outer query의 한 Row를 얻음
- 해당 Row를 가지고 Inner Query를 계산
- 계산 결과를 이용하여 Outer query의 WHERE절을 Evaluate
- 결과가 참이면 해당 Row를 결과에 포함시킴
SELECT ename, sal, deptno
FROM emp outer
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE deptno = outer.deptno);
## 결과 값
ENAME SAL DEPTNO
-------------------- ---------- ----------
ALLEN 1600 30
JONES 2975 20
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
-
같은 결과 값을 얻더라도 사람마다 스타일이 다를 수 있음
-
예제1. 각 부서별로 최고급여를 받는 사원을 출력하시오.
SELECT deptno, empno, ename, sal
FROM emp
WHERE (deptno, sal) IN (SELECT deptno, max(sal)
FROM emp
GROUP BY deptno);
## 결과 값
DEPTNO EMPNO ENAME SAL
---------- ---------- -------------------- ----------
30 7698 BLAKE 2850
20 7788 SCOTT 3000
10 7839 KING 5000
20 7902 FORD 3000
SELECT e.deptno, e.empno, e.ename, e.sal
FROM emp e, (SELECT s.deptno, max(s.sal) msal
FROM emp s
GROUP BY deptno) m
WHERE e.deptno = m.deptno AND e.sal = m.msal;
## 결과 값
DEPTNO EMPNO ENAME SAL
---------- ---------- -------------------- ----------
30 7698 BLAKE 2850
10 7839 KING 5000
20 7788 SCOTT 3000
20 7902 FORD 3000
SELECT deptno, empno, ename, sal
FROM emp e
WHERE e.sal = (SELECT max(sal)
FROM emp
WHERE deptno = e.deptno)
## 결과 값
DEPTNO EMPNO ENAME SAL
---------- ---------- -------------------- ----------
30 7698 BLAKE 2850
20 7788 SCOTT 3000
10 7839 KING 5000
20 7902 FORD 3000
4. Top-K Query
- ROWNUM: 질의의 결과에 가상으로 부여되는 Oracle의 Pseudo Column
- Top-K Query: 조건을 만족하는 상위 K 개의 결과를 빨리 얻음
SELECT rownum, ename, sal
FROM (SELECT *
FROM emp
WHERE hiredate LIKE '81%'
ORDER BY sal DESC)
WHERE rownum < 4;
## 결과 값
ROWNUM ENAME SAL
---------- -------------------- ----------
1 KING 5000
2 FORD 3000
3 JONES 2975
Subscribe via RSS