오라클,SQL,상관 서브쿼리(Correlated Sub Query)

오라클,SQL,상관 서브쿼리(Correlated Sub Query)

​

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=615

 

오라클,SQL,상관 서브쿼리(Correlated Sub Query)

오라클,SQL,상관 서브쿼리(Correlated Sub Query)5.3 상관 서브쿼리(Correlated Sub Query) 서브 쿼리와 메인쿼리간에 서로 상관 참조하는 쿼리로 Inner Query에서 Outer Query의 어떤 컬럼을 이용하는 경우다. 일반적인 Query의 경우 서브 쿼리의 결과를 메인에서는 단순히 이용만 하지만 상관 서브 쿼리에서는 …

ojc.asia

5.3 상관 서브쿼리(Correlated Sub Query)

서브 쿼리와 메인쿼리간에 서로 상관 참조하는 쿼리로 Inner Query에서 Outer Query의 어떤 컬럼을 이용하는 경우다. 일반적인 Query의 경우 서브 쿼리의 결과를 메인에서는 단순히 이용만 하지만 상관 서브 쿼리에서는 서브 쿼리가 메인 쿼리의 값을 이용하여 값을 구하면 그 값을 다시 메인 쿼리에서 이용하는 구조이므로 서브쿼리는 값을 확인하는 확인자 역할을 하게된다. 예를 들어 서브 쿼리 에서 10번 부서에서 가장 많은 급여를 받는 사람, 20번 부서에서 가장 많은 급여를 받는 사람, 30번 부서에서 같은 결과를 구하고자 한다면 부서만 다르고 같은 내용을 입력 시켜야 하므로 이 경우 상관 쿼리로 만들어 처리하면 편리 하다.

[상관 서브쿼리 실행순서]

(1) Outer query를 실행하여 행을 하나 읽는다.

(2) (1)에서 읽은행의 값을 이용하여 서브쿼리에서 필요한 값을 넣고 Subquery를 수행한다.

(3) (2)의 결과값으로 Outer query의 WHERE절을 평가하여 읽은 행의 선택여부를 결정한다. 참이면 데이터 추출, 아니면 버리고 Outer Query의 다음 레코드를 읽음

(4) Outer query의 테이블에 행이 없을 때까지 (1)-(3)을 반복 수행한다.

--각 부서의 최대 급여를 받는 사원의 부서코드, 이름, 급여를 출력하는데 부서코드 순으로 오름차순 정렬하여 출력하는 예문 첫번째 상관서브쿼리로 구현 SQL> select deptno, ename, sal from emp e1 where sal = (select max(sal) from emp e2 where e1.deptno = e2.deptno) order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 -- 두번째 Pairwise 서브쿼리로 구현 SQL> select ename, sal, deptno from emp where (deptno, sal ) in (select deptno, max(sal) from emp group by deptno) order by deptno; -- 세번째 조인, 인라인뷰로 구현 SQL> select e1.ename, e1.sal, e1.deptno from emp e1, ( select deptno, max(sal) as msal from emp group by deptno ) e2 where e1.deptno = e2.deptno and e1.sal = e2.msal order by e1.deptno; --각 직무(JOB)별로 최대 급여를 받는 사원의 직무, 이름, 급여를 출력하는데 직무명으로 오름차순 정렬하여 출력하는 예문 SQL> select job, ename, sal from emp e1 where sal = (select max(sal) from emp e2 where e1.job = e2.job) order by deptno; JOB ENAME SAL --------- ---------- ---------- PRESIDENT KING 5000 CLERK MILLER 1300 MANAGER JONES 2975 ANALYST SCOTT 3000 ANALYST FORD 3000 SALESMAN ALLEN 1600 6 개의 행이 선택되었습니다. --자신이 속한 부서의 평균 급여보다 급여를 적게 받는 사원의 부서, 사원명, 급여를 출력하되 부서 순으로 오름차순 정렬하여 출력 SQL> select deptno, ename, sal from emp e1 where sal < (select avg(sal) from emp e2 where e1.deptno = e2.deptno) order by deptno; DEPTNO ENAME SAL ---------- ---------- ---------- 10 CLARK 2450 10 MILLER 1300 20 SMITH 800 20 ADAMS 1100 30 WARD 1250 30 JAMES 950 30 TURNER 1500 30 MARTIN 1250 --EMP 테이블에서 급여가 높은 사원 5명을 출력 하는 예문 SQL> select ename, sal from emp e1 where 5 > (select count(*) from emp e2 where e2.sal > e1.sal) order by sal desc; ENAME SAL ---------- ---------- KING 5000 SCOTT 3000 FORD 3000 JONES 2975 BLAKE 2850 --각 부서별로 급여가 높은 사람 2명씩 출력 하는 예문 SQL> select deptno, ename, sal from emp e1 where 2 > (select count(*) from emp e2 where e2.sal > e1.sal and e2.deptno = e1.deptno) order by deptno, sal desc; DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 20 SCOTT 3000 20 FORD 3000 30 BLAKE 2850 30 ALLEN 1600 6 개의 행이 선택되었습니다. --사원이 한명이라도 있는 부서명 출력 SQL> SELECT dname FROM dept d WHERE EXISTS ( SELECT 1 FROM emp WHERE deptno = d.deptno); DNAME -------------- ACCOUNTING RESEARCH SALES

​