Oracle SQL - SUBQUERY, SET Operation

Oracle SQL - SUBQUERY, SET Operation

Subquery

하나의 SQL 질의문 속에 다른 SQL 질의문이 포함되어 있는 형태
예) 'SCOTT' 보다 급여가 많은 사람은?
급여가 많은 사람의 이름?

SELECT ename FROM emp WHERE sal > ???

'SCOTT'의 급여는?

SELECT sal FROM emp WHERE ename='SCOTT'

SELECT ename FROM emp
	WHERE sal > ( SELECT sal FROM emp
			WHERE ename = ‘SCOTT’ )

[연습] hr.employees
'Den' 보다 급여를 많이 받는 사원의 이름과 급여는?

Single-Row Subquery

Subquery의 결과가 한 ROW인 경우
  - Single-Row Operator를 사용해야 함: =, >, >=, <, <=, <>

SELECT ename, sal, deptno FROM emp
	WHERE ename = (SELECT MIN(ename) FROM emp);

SELECT ename, sal FROM emp
	WHERE sal < (SELECT AVG(sal)FROM emp);

SELECT ename, deptno FROM emp 
	WHERE deptno = (SELECT deptno FROM dept
			WHERE dname = ‘SALES’);

 

Multi-Row Subquery

 - Subquery의 결과가 둘 이상의 Row 
- Multi-Row에 대한 연산을 사용해야 함: ANY, ALL, IN, EXIST …

SELECT ename, sal, deptno
FROM emp
WHERE ename = (SELECT MIN(ename) FROM emp GROUP BY deptno);       -- X = 사용 불가

SELECT ename, sal, deptno
FROM emp
WHERE ename IN (SELECT MIN(ename) FROM emp GROUP BY deptno);

SELECT ename, sal, deptno
FROM emp
WHERE ename = ANY (SELECT MIN(ename) FROM emp GROUP BY deptno);

- ANY는 OR과 비슷, ALL은 AND와 비슷

 

Correlated Query

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);

각 부서별로 최고급여를 받는 사원을 출력하시요

SELECT deptno, empno, ename, sal FROM emp
WHERE (deptno,sal) IN (SELECT deptno, max(sal) FROM emp
					GROUP BY deptno);

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;

SELECT deptno, empno, ename, sal FROM emp e
WHERE e.sal = (SELECT max(sal) FROM emp
			WHERE deptno = e.deptno);

Top-K Query (Oracle)

 

  - ROWNUM : 질의의 결과에 가상으로 부여되는 Oracle의 Pseudo Column
  - Top-K Query: 조건을 만족하는 상위 k개의 결과를 빨리 얻기
     - 81년도에 입사한 사람 중 급여가 가장 많은 3명은 누구인가?

SELECT rownum, ename, sal
FROM (SELECT * FROM emp
		WHERE hiredate like '81%'
		ORDER BY sal DESC)
WHERE rownum < 4;

집합(SET) Operator

  - 두 집합의 결과를 가지고 집합 연산을 수행
  -  UNION, UNION ALL, INTERSECT, MINUS

SELECT ename FROM emp
UNION
SELECT dname FROM dept;

 

RANK 관련 함수

 

SELECT sal, ename,
RANK() OVER (ORDER BY sal DESC) AS rank,
DENSE_RANK() OVER (ORDER BY sal DESC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY sal DESC) AS row_number,
rownum AS "rownum"
FROM emp;

  - RANK : 중복순위 다음은 해당 개수만큼 건너뛰고 다음 순위 반환
  - DENSE_RANK : 중복순위와 상관 없이 다음 순위를 반환
  - ROW_NUMBER : 중복을 같은 순위로 두지 않고 무조건 순서대로 반환

 

Hierarchical Query (Oracle)

  - 트리 형태 구조를 추출하기 위한 질의
  - START WITH (ROOT 조건), CONNECT BY PROIR (연결조건)
  - LEVEL : 트리의 레벨을 나타내는 Pseudo Column

SELECT level, ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY level;