- 출처 : http://radiocom.kunsan.ac.kr/lecture/oracle/statement_subquery/subquery_correlated.html -
일반적인 서브쿼리는 sub query의 결과를 main query가 이용한다. 그러나 correlated 서브쿼리는 sub query가 main query의 값을 이용하고, 그렇게 구해진 sub query의 값을 다시 main query가 다시 이용하게 된다.
• correlated subquery는 한개의 행을 비교할 때마다 결과가 main으로 리턴딘다.
• correlated subquery는 한 행을 처리할 때마다 sub로 주고 값을 처리한 후에 그 결과를 다시 main으로 건내는 방식으로 내부적으로 성능이 저하딘다.
• 메인쿼리와 서브쿼리간에 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결한다.
참고로
서브쿼리가 FROM 절에 있으면 이를 Inline view라하고
서브쿼리가 WHERE 절에 있으면 이를 Nested subquery라 하며
Nested subquery중에서 참조하는 테이블이 parent, child관계를 가지면 이를 Correlated subquery라 한다.
【예제】
SQL> select deptno,ename,sal
2 from emp t1
3 where sal > (select avg(sal)
4 from emp t2
5 where t2.deptno=t1.deptno);
DEPTNO ENAME SAL
---------- ---------- ----------
30 ALLEN 1600
20 JONES 2975
30 BLAKE 2850
20 SCOTT 3000
10 KING 5000
20 FORD 3000
6 개의 행이 선택되었습니다.
SQL>
이 예제에서
하나, 메인쿼리에서 emp t1이라는 t1 별명을 서브쿼리로 전달한다.
두울, 메인쿼리에서 전달 받은 deptno로 평균을 계산한다.
세엣, 메인쿼리에서 평균 값보다 큰 것만 출력한다.
【예제】
| any 연산자사용 | correlated subquery 사용 |
|---|---|
SQL> select ename,hiredate,deptno,sal from emp 2 where hiredate < ANY (select hiredate 3 from emp 4 where deptno=30) 5 AND deptno <>30; ENAME HIREDATE DEPTNO SAL ---------- ------------ ---------- ---------- SMITH 17-DEC-80 20 800 JONES 02-APR-81 20 2975 CLARK 09-JUN-81 10 2450 KING 17-NOV-81 10 5000 SQL> |
SQL> select ename,hiredate,deptno,sal from emp 2 where hiredate < (select max(hiredate) 3 from emp 4 where deptno=30) 5 and deptno <> 30; ENAME HIREDATE DEPTNO SAL ---------- -------------- ---------- ---------- SMITH 17-12월-80 20 800 JONES 02-4월 -81 20 2975 CLARK 09-6월 -81 10 2450 KING 17-11월-81 10 5000 SQL> |
| 성능이 좋음 | 성능이 떨어짐 |
| correlated subquery는 row by row processing으로 한개의 행을 비교할 때마다 결과가 메인으로 리턴된다. 즉, 한 행을 처리할 때마다 sub로 주고 값을 처리한 후에 main으로 건너는 방식이므로 내부적으로 성능이 저하된다 | |
• Main은 subquery에서 리턴된 결과가 나올 때마다 sal 컬럼과 비교해서 리턴값보다 클 경우 ename,sal 컬럼값을 출력한다.
• Mainquery 안의 컬럼이 subquery에서 쓰이면 correlated subquery라고 할 수 있다.
다음 예제는 부서번호가 30인 어떤 사원보다도 먼저 입사한 사원을 출력 (단, 부서번호 30인 사원은 제외)
| any 연산자사용 | correlated subquery 사용 |
|---|---|
SQL> select ename,hiredate,deptno,sal from emp 2 where hiredate < ANY (select hiredate 3 from emp 4 where deptno=30) 5 AND deptno <>30; ENAME HIREDATE DEPTNO SAL ---------- ------------ ---------- ---------- SMITH 17-DEC-80 20 800 JONES 02-APR-81 20 2975 CLARK 09-JUN-81 10 2450 KING 17-NOV-81 10 5000 SQL> |
SQL> select ename,hiredate,deptno,sal from emp 2 where hiredate < (select max(hiredate) 3 from emp 4 where deptno=30) 5 and deptno <> 30; ENAME HIREDATE DEPTNO SAL ---------- -------------- ---------- ---------- SMITH 17-12월-80 20 800 JONES 02-4월 -81 20 2975 CLARK 09-6월 -81 10 2450 KING 17-11월-81 10 5000 SQL> |
| 성능이 좋음 | 성능이 떨어짐 |
| correlated subquery는 한개의 행을 비교할 때마다 결과가 메인으로 리턴된다. 즉, 한 행을 처리할 때마다 sub로 주고 값을 처리한 후에 main으로 건너는 방식이므로 내부적으로 성능이 저하된다 | |
job이 analyst인 모든 사원보다 급여를 많이 받는 타 업무의 사원을 출력 (단, 업무가 clerk인 사원은 제외)
| all 연산자사용 | correlated subquery 사용 |
|---|---|
SQL> select empno,ename,job,sal from emp
2 where sal > ALL (select sal from emp
3 where job='ANALYST')
4 AND job <> 'CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
SQL>
|
SQL> select empno,ename,job,sal from emp
2 where sal > (select max(sal) from emp
3 where job='ANALYST')
4 and job <> 'CLERK';
EMPNO ENAME JOB SAL
---------- ---------- --------- ----------
7839 KING PRESIDENT 5000
SQL>
|
| 성능이 좋음 | 성능이 떨어짐 |
| correlated subquery는 한개의 행을 비교할 때마다 결과가 메인으로 리턴된다. 즉, 한 행을 처리할 때마다 sub로 주고 값을 처리한 후에 main으로 건너는 방식이므로 내부적으로 성능이 저하된다 | |
다음 예는 부서번호가 30인 사원보다 먼저 입사한 사원을 출력(단, 부서번호 30인 사원은 제외)
| all 연산자사용 | correlated subquery 사용 |
|---|---|
SQL> select ename,hiredate,deptno from emp 2 where hiredate < ALL (select hiredate from emp 3 where deptno=30) 4 AND deptno <> 30; ENAME HIREDATE DEPTNO ---------- ------------ ---------- SMITH 17-DEC-80 20 SQL> |
SQL> select ename,hiredate,deptno from emp 2 where hiredate < (select min(hiredate) from emp 3 where deptno=30) 4 and deptno <> 30; ENAME HIREDATE DEPTNO ---------- -------------- ---------- SMITH 17-12월-80 20 SQL> |
| 성능이 좋음 | 성능이 떨어짐 |
| correlated subquery는 한개의 행을 비교할 때마다 결과가 메인으로 리턴된다. 즉, 한 행을 처리할 때마다 sub로 주고 값을 처리한 후에 main으로 건너는 방식이므로 내부적으로 성능이 저하된다 | |