Oracle Correlated Subquery example
오라클 상관관계 서브커리 ( Correlated Subqueries ) 사용 예
개요
상관관계 서브커리는 일반 서브커리와는 형식적으로는 약간의 차이점이 있지만 실행 절차면에서는 큰 차이점을 가지고 있다.
일반 서브커리와 상당히 다른 개념을 가지고 있어서 어렵게 느껴지기 때문에 다른 문장으로 대신 표현하는 경우가 많다.
그러나 상관관계 서브커리는 일단 그 원리를 알고 나면 그 만큼 강력한 수단으로 사용될 수 있고 우리에게 힘이 된다
여기서는 상관관계 서브커리와 일반 서브커리의 차이점을 이해하고 응용하는 예를 보이고자 한다
개념을 보다 잘 이해하기 위해서 Inline View 혹은 WITH 절을 사용한 문장을 상호 비교해가면서 차이점을 확인한다면 좋을 것 같다
SELECT 문장의 실행과정
일반 문장의 실행과정
SELECT * from emp WHERE deptno=20;
EMP 테이블의 첫 행을 포인터가 가리키고 WHERE 조건을 검사한다
WHERE 조건검사 결과 true 이면 그 행이 리턴되고 아니면 그냥 지나간다
포인터가 다음 행을 가리킨다
위의 과정이 테이블 마지막 행이 나올 때까지 반복된다
일반 서브커리가 사용된 문장의 실행과정
SELECT empno, ename
FROM emp
WHERE sal= ( SELECT max(sal) from emp WHERE deptno=20 );
위와 같은 WHERE절의 일반 서브커리는 최초에 한번만 실행되고 그 결과집합은 모든 행의 조건을 검사할 때마다 사용된다
바깥 문장이 실행되기 전에 일단 WHERE 절의 서브커리가 먼저 실행되어 결과집합이 메모리에 생성된다
바깥 문장이 실행되면 대상 테이블의 첫 행에 포인터를 맞추고 WHERE 조건을 검사할 때 위에서 생성된 결과집합이 사용된다
바깥 문장의 WHERE 조건이 참이면 현재 포인터가 가리키는 행이 리턴되고 아니면 무시한다
대상 테이블의 행을 가리키는 포인터가 다음 행으로 이동한다
대상 테이블의 마지막 행에 도달할 때까지 위의 절차가 반복된다
상관관계 서브커리 ( Correlated Subqueries ) 실행과정
SELECT ename, sal, deptno FROM emp e
WHERE sal=
(
SELECT max(sal) FROM emp
WHERE deptno=e.deptno -- 바깥 문장의 포인터가 현재 가리키는 행의 부서번호
);
서브커리에 앞서 바깥 문장이 실행되면 대상 테이블의 첫 행을 포인터가 가리키고 WHERE 조건에 포함된 서브커리도 그 때마다 실행된다
WHERE절의 서브커리가 실행될 때 바깥 문장의 포인터가 가리키는 그 행을 서브커리에서도 참조(바깥 문장에서 지정한 테이블의 별칭 사용)할 수 있다
WHERE 절이 최종적으로 true 로 판정되면 바깥 문장에서 포인터가 가리키는 행은 리턴되고 아니면 무시된다
다음 행으로 포인터가 이동한다
대상 테이블의 마지막 행까지 도달할 때까지 위의 절차가 반복된다
위의 문장 실행결과
BLAKE 2850 30
KING 5000 10
FORD 3000 20
일반 서브커리와 상관관계 서브커리의 실행절차상의 차이점
일반 서브커리는 바깥 커리에 앞서 한번만 실행되지만 상관관계 서브커리는 다르다
상관관계 서브커리에서는 바깥 문장이 가리키는 포인터가 다른 행으로 이동할 때마다 WHERE 절의 서브커리도 한번씩 실행된다
일반 서브커리와 상관관계 서브커리의 형식적인 차이점
일반 서브커리를 가진 문장에서는 안쪽 서브커리에서 지정한 테이블 별칭을 바깥 문장에서 사용한다
select * from
(
select rownum rn, t1.* from
(
select ename, deptno from emp2 order by deptno
) t1
)
where rn between 4 and 6;
상관관계 서브커리는 바깥 문장에서 지정한 테이블 별칭을 안쪽 서브커리에서 사용한다
SELECT ename, sal, deptno FROM emp e
WHERE sal=
(
SELECT max(sal) FROM emp WHERE deptno=e.deptno
);
상관관계 서브커리 응용 포인트
바깥 커리에서 특정 행이 선택되기 전의 상태(포인터가 어떤 행을 현재 가리키고 있는 상태)라도 WHERE 조건 절에 있는 서브커리에서 그 행을 참조할 수 있다는 점과 바깥 커리의 포인터가 다른 행으로 변경될 때마다 WHERE 절의 상관관계 서브커리는 반복해서 실행되므로 바깥 커리의 현재 참조 행의 내용(구성 컬럼 값)에 따라서 동적인 WHERE 조건을 구성할 수 있다는 장점을 가진다
상관관계 서브커리 응용 예 1
바깥 커리의 포인터가 가리키는 현재 사원의 급여와 해당부서 급여평균을 동시에 출력하는 예
SELECT empno, ename, sal,
(
select ROUND( avg(sal) ) from emp where deptno=e.deptno
) "부서급여평균"
FROM emp e
ORDER BY deptno;
EMP 테이블에서 급여순위를 "RANK" 라는 임시컬럼으로 출력하는 예
SELECT t1.*,
(
SELECT RANK(t1.sal) WITHIN GROUP
( ORDER BY sal DESC ) FROM emp2
) AS rank
FROM
(
SELECT * FROM emp2 ORDER BY sal DESC
) t1;
결과------------------------------------------------
7839 KING PRESIDENT 81/11/17 5000 10 1
7902 FORD ANALYST 7566 81/12/03 3000 20 2
7566 JONES MANAGER 7839 81/04/02 2975 20 3
7698 BLAKE MANAGER 7839 81/05/01 2850 30 4
7782 CLARK MANAGER 7839 81/06/09 2450 10 5
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 6
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 7
7934 MILLER CLERK 7782 82/01/23 1300 10 8
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 9
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 9
7900 JAMES CLERK 7698 81/12/03 950 30 11
7369 SMITH CLERK 7902 80/12/17 880 20 12
EMP 테이블에서 각 직원들이 관리하는 부하직원의 수를 출력하는 상관관계 서브커리
SELECT empno eno, ename, mgr,
(
SELECT count(*) FROM emp WHERE mgr=e1.empno
) "부하직원의 수"
FROM emp e1;
상관관계 서브커리 연습문제
사장을 제외한 각 부서 최고 급여자의 이름과 급여액, 부서번호를 출력하라
SELECT ename, sal, deptno FROM emp e
WHERE empno=
(
SELECT empno FROM emp WHERE sal=
(
SELECT max(sal) from
(
SELECT * from emp WHERE job!='PRESIDENT'
)
WHERE deptno=e.deptno
)
)
ORDER BY deptno;
/*
CLARK 2450 10
FORD 3000 20
BLAKE 2850 30
*/
사번, 이름, 관리자이름, 해당부서 최고급여, 해당부서 최소급여를 출력해보세요
SELECT empno, ename,
(select ename from emp where empno=e.mgr) "관리자",
(select max(sal) from emp where deptno=e.deptno) "부서최고급여",
(select min(sal) from emp where deptno=e.deptno) "부서최소급여"
FROM emp e;