Oracle
13. Chapter6 Advanced Query(Subquery, Set Operation)(20081031)
Gungume
2009. 1. 9. 10:49
Subquery in the FROM Clause (P6-5)
- rownum
- emp 테이블에 rownum 컬럼이 있는것처럼 조회됨
- 실제 emp 테이블에 rownum 컬럼이 있는것은 아니고 오라클이 붙혀줌
- 실제 emp 테이블에는 없고 레코드를 출력할 때 출력할 것들을 가져오고 그 다음에 rownum 번호를 붙혀줌
- 순서
(1) 결과로 선택할 row 결정
(2) rownum 번호 할당
(3) ORDER BY 실행
- emp 테이블에 대해서 rownum 출력
- SQL> SELECT rownum, empno, ename
2 FROM emp; - ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7499 ALLEN
3 7521 WARD
4 7566 JONES
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER
11 7876 ADAMS
12 7900 JAMES
13 7902 FORD
14 7934 MILLER - 14 rows selected.
- SELECT 결과에 따라서 rownum이 바뀜 = emp 테이블에 rownum 정보가 없다 (순서 : 1번에 해당~)
- // SMITH가 ROWNUM 1번
- SQL> SELECT rownum, empno, ename
2 FROM emp
3 WHERE deptno=20; - ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7369 SMITH
2 7566 JONES
3 7788 SCOTT
4 7876 ADAMS
5 7902 FORD - // CLARK이 ROWNUM 1번
- SQL> SELECT rownum, empno, ename
2 FROM emp
3 WHERE deptno=10; - ROWNUM EMPNO ENAME
---------- ---------- ----------
1 7782 CLARK
2 7839 KING
3 7934 MILLER
- rownum 번호 할당 후 ORDER BY절이 실행되므로 SELECT 결과를 보면 rownum 순서가 뒤죽박죽~(순서 : 2, 3번에 해당)
- SQL> SELECT rownum, empno, ename
2 FROM emp
3 ORDER BY ename; - ROWNUM EMPNO ENAME
---------- ---------- ----------
11 7876 ADAMS
2 7499 ALLEN
6 7698 BLAKE
7 7782 CLARK
13 7902 FORD
12 7900 JAMES
4 7566 JONES
9 7839 KING
5 7654 MARTIN
14 7934 MILLER
8 7788 SCOTT
1 7369 SMITH
10 7844 TURNER
3 7521 WARD - 14 rows selected.
- rownum을 이용해서 뭔가를 꺼낼 때 중간값을 이용할 수는 없다.
- 결과로 선택할 row를 정할 때 제일 처음 자료(emp 테이블에서는 SMITH)에 rownum 1을 붙혀준다.
- 조건에서 1은 해당이 안되기 때문에 SMITH는 패스하고 ALLEN에 다시 1을 붙혀준다. -> 계속 반복해도 결국 1만 반복~
- // 중간값 이용 불가
- SQL> SELECT empno, ename
2 FROM emp
3 WHERE rownum BETWEEN 5 AND 10; - no rows selected
- // 처음부터 이용하면 가능
- SQL> SELECT empno, ename
2 FROM emp
3 WHERE rownum BETWEEN 1 AND 10; - EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER - 10 rows selected.
- 'WHERE rownum BETWEEN 5 AND 10;' 형식으로 꼭 이용하고 싶은 경우 부질의를 사용
- 부질의에서 1~10에 해당하는 것을 구하고 그 결과를 바탕으로 5~10을 구함
- 이때 생성되는 테이블의 rn은 실제 컬럼임
- SQL> SELECT newemp.rn, empno, ename
2 FROM (
3 SELECT rownum as rn, empno, ename
4 FROM emp
5 WHERE rownum BETWEEN 1 AND 10) newemp
6 WHERE newemp.rn BETWEEN 5 AND 10; - RN EMPNO ENAME
---------- ---------- ----------
5 7654 MARTIN
6 7698 BLAKE
7 7782 CLARK
8 7788 SCOTT
9 7839 KING
10 7844 TURNER - 6 rows selected.
- 급여 많이 받는순 1위~3위, 급여 많이 받는순 3위~6위
- 급여 많이 받는순 -> 급여를 DESC로 정렬 -> rownum 번호 할당 후 ORDER BY를 실행하므로 'rownum <= 3' 구문으로는 안됨 -> 봉급순으로 정렬한 임시테이블 생성 후 처리해야함
- // 정렬을 거쳤기 때문에 제대로 안됨~
- SQL> SELECT rownum, ename, sal
2 FROM emp
3 WHERE rownum <= 3
4 ORDER BY sal DESC; - ROWNUM ENAME SAL
---------- ---------- ----------
2 ALLEN 1600
3 WARD 1250
1 SMITH 800
- 봉급순으로 정렬한 임시테이블 생성후 처리
- newsal 테이블에서는 봉급으로 정렬된 상태이므로 rownum 순서 = 봉급순
- 급여 많이 받는순 1위~3위
- SQL> SELECT rownum, newsal.ename, newsal.sal
2 FROM (SELECT ename, sal
3 FROM emp
4 ORDER BY sal DESC) newsal
5 WHERE rownum <= 3; - ROWNUM ENAME SAL
---------- ---------- ----------
1 KING 5000
2 SCOTT 3000
3 FORD 3000
- 급여 많이 받는순 3위~6위 -> 일단 급여순으로 정렬하고 1~6위 뽑아낸 후 거기서 다시 3~6위 뽑아냄
- SQL> SELECT newemp.nm, newemp.name, newemp.sal
2 FROM(
3 SELECT rownum nm, newsal.ename name, newsal.sal sal
4 FROM(SELECT ename, sal
5 FROM emp
6 ORDER BY sal DESC) newsal
7 WHERE rownum <= 6
8 ) newemp
9 WHERE newemp.nm BETWEEN 3 AND 6; - NM NAME SAL
---------- ---------- ----------
3 FORD 3000
4 JONES 2975
5 BLAKE 2850
6 CLARK 2450
- 커미션(comm)을 제일 적게 받는 사람
- SQL> SELECT rownum, newsal.ename, newsal.comm
2 FROM (SELECT ename, comm
3 FROM emp
4 ORDER BY comm) newsal
5 WHERE rownum <=2; - ROWNUM ENAME COMM
---------- ---------- ----------
1 TURNER 0
2 ALLEN 300
- InlineView - FROM 절에 서브쿼리를 써서 가상의 테이블이 만들어지는 것
- 각 사원의 봉급이 전체봉급에 몇 %인지 출력
- SELECT절 안에 Subquery 사용
- SQL> SELECT empno, ename, sal/
2 (SELECT sum(sal)
3 FROM emp)
4 *100 as "전체봉급의 몇 %"
5 FROM emp; - EMPNO ENAME 전체봉급의 몇 %
---------- ---------- ---------------
7369 SMITH 2.75624462
7499 ALLEN 5.51248923
7521 WARD 4.30663221
7566 JONES 10.2497847
7654 MARTIN 4.30663221
7698 BLAKE 9.81912145
7782 CLARK 8.44099914
7788 SCOTT 10.3359173
7839 KING 17.2265289
7844 TURNER 5.16795866
7876 ADAMS 3.78983635
7900 JAMES 3.27304048
7902 FORD 10.3359173
7934 MILLER 4.4788975 - 14 rows selected.
Correlated Subquery (P6-7)
- Correlated Subquery(상관 부질의)
- 내부질의에서 사용하는 테이블과 외부질의에서 사용하는 테이블의 상관관계를 알아야함
- 그냥 부질의와는 달리 부질의만으로는 실행이 안됨
- Example : 부서별로 최대봉급을 받는 사원의 이름
외부질의 = emp e, 내부질의 = () 안의 emp
외부질의에서 현재 참조하고 있는 레코드에 따라서 내부질의의 결과값이 달라짐
내부질의에서는 외부질의에서 현재 참조하고 있는 레코드의 부서번호와 동일한 부서번호 중 최대봉급을 얻어옴
그 결과를 가지고 외부질의에서 현재 참조하고 있는 레코드와 비교(sal 비교)
- SQL> SELECT deptno, ename, sal
2 FROM emp e
3 WHERE e.sal = (SELECT MAX(sal
4 FROM emp
5 WHERE deptno = e.deptno); - DEPTNO ENAME SAL
---------- ---------- ----------
30 BLAKE 2850
20 SCOTT 3000
10 KING 5000
20 FORD 3000
| e.deptno | e.name | e.sal | 결과 | deptno | ename | sal |
|---|---|---|---|---|---|---|
| 10 | king | 5000 | OK | 10 | king | 5000 |
| 10 | lee | 1000 | X | 10 | lee | 1000 |
| 20 | kim | 2000 | X | 20 | kim | 2000 |
| 20 | hong | 3000 | OK | 20 | hong | 3000 |
| 30 | park | 1000 | 30 | park | 1000 | |
| 30 | smith | 1500 | 30 | smith | 1500 |
- 사원이 한명이라도 있는 부서명을 출력하시오.
- // SELECT 1 -> 1은 별다른 의미 없음, 아무거나 쓰면 됨, EXISTS()는 뭔가 결과만 있으면 TRUE이기 때문에~
- SQL> SELECT dname FROM dept d
2 WHERE EXISTS(SELECT 1 FROM emp WHERE deptno=d.deptno); - DNAME
--------------
ACCOUNTING
RESEARCH
SALES
- SALGRADE 테이블의 봉급 등급 중 사원봉급이 하나도 속해있지 않은 봉급 등급은?
- SQL> SELECT grade FROM salgrade sg
2 WHERE NOT EXISTS(SELECT 1 FROM emp
3 WHERE sal BETWEEN sg.losal AND sg.hisal); - no rows selected
Set Operator (P6-8)
- 쿼리문의 결과를 합치는 연산
- 오래되고 자주 안쓰이는 데이터(과거정보)를 별도의 테이블로 구성하면 최신정보만 조회할 때 성능이 좋아짐
- 그러나 과거정보와 같이 조회해야한다면 Set Operator 를 이용해서 사용
- UNION - 합집합
중복데이터 제거
실행결과를 보면 두 결과가 마치 하나의 테이블인것처럼 나옴
컬럼의 갯수와 데이터 타입이 동일해야함
- SQL> SELECT ename FROM emp
2 UNION
3 SELECT dname FROM dept; - ENAME
--------------
ACCOUNTING
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
OPERATIONS
RESEARCH
SALES
SCOTT
SMITH
TURNER
WARD - 18 rows selected.
- UNION ALL - 합집합
중복데이터 포함
- INTERSECT - 교집합
첫번째 질의문 결과와 두번째 질의문 결과 중 동일한 부분
- MINUS - 차집합
첫번째 질의문 결과 - 두번째 질의문 결과
Hierarchical Query (P6-11)
- emp 테이블은 mgr 컬럼을 이용해서 계층구조를 가짐.
- 계층구조를 구할 수 있는 쿼리사용
- SQL> SELECT level, ename
2 FROM emp
3 START WITH mgr IS NULL
4 CONNECT BY PRIOR empno = mgr
5 ORDER BY level; - LEVEL ENAME
---------- ----------
1 KING
2 JONES
2 CLARK
2 BLAKE
3 SCOTT
3 ALLEN
3 MARTIN
3 MILLER
3 JAMES
3 TURNER
3 WARD
3 FORD
4 ADAMS
4 SMITH - 14 rows selected.
이 글은 스프링노트에서 작성되었습니다.