오라클 서브쿼리, 상관관계 서브쿼리(Corelated Subquery)

오라클 서브쿼리, 상관관계 서브쿼리(Corelated Subquery)

상관관계 서브쿼리(correlated subquery)는서브쿼리와 메인쿼리가 서로 참조하는 쿼리입니다. 서브쿼리에서 메인쿼리의 컬럼을 이용하는 경우로 상관 서브쿼리, 상호연관 서브쿼리 등으로도 부릅니다.

일반적인 쿼리는 서브쿼리 결과를 메인쿼리에서 단순히 이용만 합니다. 반면 상관관계 서브쿼리에서는 서브쿼리가 메인쿼리의 값을 이용하여 값을 구하면 ➝ 그 값을 다시 메인쿼리에서 이용합니다. 이때 서브쿼리는 값을 확인하는 확인자 역할을 하게 됩니다. 

예를 들어 서브쿼리에서 10번, 20번, 30번 부서에서 가장 많은 급여를 받는 사람을 구하고자 한다면 부서 외에 다른 코드는 같습니다. 이때 상관관계 서브쿼리로 만들어 처리하면 편리합니다.

상관관계 서브쿼리 실행 순서

1. 바깥쪽 메인쿼리를 실행하여 행을 하나 읽습니다. 

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

3. 2의 결과값으로 메인쿼리의 WHERE 절을 평가하여 읽은 행의 선택여부를 결정 합니다. 참이면 데이터 추출, 아니면 버리고 메인쿼리의 다음 레코드를 읽습니다.

4. 메인쿼리의 테이블에 행이 없을 때까지 1~3을 반복 수행합니다.

부서별 최대급여 받는 직원 정보를 추출하는데 상관관계 서브쿼리를 이용한 방법과 페어와이즈 서브쿼리를 이용한 방법으로 구현해 보겠습니다. 상관관계 서브쿼리는 바깥쪽 메인을 하나씩 읽어 안쪽 서브쿼리에 넣고 서브쿼리를 실행하여 그 결과랑 WHERE절을 판단하는 반면  페어와이즈 서브쿼리를 이용하는 방법은 서브쿼리를 전체 먼저 실행하고 그 결과를 가지고 메인쿼리를 한번 실행 합니다..

5.4.1 부서별 최대 급여를 받는 직원 정보 출력하기 : 상관관계 서브쿼리

상관관계 서브쿼리로 구현한 예문을 살펴봅시다.

부서별 최대 급여를 받는 직원의 부서코드, 이름, 급여를 출력합니다. 이때 부서코드순으로 오름차순 정렬하여 출력하세요.

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
1	10	CLARK	2450
2	20	FORD	3000
3	20	SCOTT	3000
4	30	BLAKE	2850

<실행 순서>

❶ EMP 테이블에서 맨 처음 행을 하나 읽습니다. 

❷ 읽은 직원의 부서코드를 서브쿼리로 넣고 서브쿼리를 먼저 실행합니다.

❸ WHERE 절에서 서브쿼리가 구한 최소급여를 메인쿼리에서 읽은 직원과 비교하여 즉 메인쿼리에서 읽은 직원의 급여가 자신이 속한 부서의 최소 급여인지를 WHERE 절을 비교하여 조건에 맞으면 보관하고 아니면 버립니다.

➍ 메인쿼리에서 다음 직원을 읽고 동일한 방식으로 진행하여 최종 결과를 만들어냅니다.

➎ 메인쿼리의 테이블에 행이 없을 때까지 ❶~❸을 반복 수행합니다.

5.4.2 부서별 최대 급여를 받는 직원 정보 출력하기 : 페어와이즈 서브쿼리

위 쿼리문을 페어와이즈 서브쿼리로 구현해봅시다.

부서별 최대 급여를 받는 직원의 부서코드, 이름, 급여를 출력합니다. 이때 부서코드순으로 오름차순 정렬하여 출력하세요.

SELECT DEPTNO, ENAME, SAL  FROM     EMP  WHERE (DEPTNO, SAL ) IN (SELECT DEPTNO, MAX(SAL)                                                  FROM    EMP                                                  GROUP BY DEPTNO) ORDER BY DEPTNO;

<실행 결과>

	DEPTNO	ENAME	SAL
1	10	CLARK	2450
2	20	FORD	3000
3	20	SCOTT	3000
4	30	BLAKE	2850

<실행 순서>

❶ 서브쿼리를 먼저 실행합니다.

<실행 결과>

	DEPTNO	MAX(SAL)
1	30	2850
2	10	2450
3	(null)	5000
4	20	3000

❷ 서브쿼리의 결과를 받아서 메인쿼리가 실행되어 최종 결과를 반환합니다.

SELECT DEPTNO, ENAME, SAL

FROM    EMP 

WHERE (DEPTNO, SAL ) IN ( (30, 2850), (10, 2450), (null, 5000), (20, 3000))

ORDER BY DEPTNO;

3번째 DEPTNO가 null인 데이터는 연산 결과도 null이 되므로 출력되지 않습니다.

5.4.3 직무별 최대 급여를 받는 직원 정보 출력하기 : 상관관계 서브쿼리

직무(JOB)별로 최대 급여를 받는 직원의 직무, 이름, 급여를 출력합니다. 직무명으로 오름차순 정렬하여 출력하는 예문입니다.

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
1	CLERK	MILLER	1300
2	MANAGER	JONES	2975
3	ANALYST	SCOTT	3000
	ANALYST	FORD	3000
	SALESMAN	ALLEN	1600
	PRESIDENT	KING	5000

❶ 메인쿼리를 통해 EMP 테이블에서 행 하나를 읽습니다.

❷ 읽은 행의 데이터 중 직무(JOB) 컬럼을 서브쿼리로 넣어주고 서브쿼리를 실행합니다.

❸ WHERE 절에서 서브쿼리가 구한 직무의 최대급여를 메인쿼리에서 읽은 직원의 급여와 비교하여, 즉 메인쿼리에서 읽은 직원의 급여가 자신이 속한 직무의 최대 급여인지를 WHERE 절을 비교하여 조건에 맞으면 보관하고 아니면 버립니다.

➍ 메인쿼리에서 다음 직원 데이터를 읽어 동일한 순서대로 작업을 진행하여 최종 결과 리스트를 만들어 반환합니다.

➎ 메인쿼리의 테이블에 행이 없을 때까지 ❶~❸을 반복 수행합니다.

 

#오라클 #ORACLE #서브쿼리 #Subquery #상관서브쿼리​