오라클 SQL, 서브쿼리, 복수행 서브쿼리(Multi-Row Subquery)

 

오라클 SQL, 서브쿼리, 복수행 서브쿼리(Multi-Row Subquery)

복수행 서브쿼리

복수행 서브쿼리(multi-row subquery)는 서브쿼리에서 여러 결과가 반환되는 구조입니다. IN, ANY, SOME(ANY와 동일), ALL, EXISTS 같은 복수행 연산자를 사용합니다(2.5절 ‘연산자’ 참조). 

예문을 따라 하면서 알아봅시다.

5.3.1 부서별로 급여를 가장 적게 받는 직원 정보 출력 : IN

IN 연산자는 오른쪽 서브쿼리에서 반환되는 행(ROW)과 컬럼값을 비교하는 데 사용합니다. IN 연산자를 사용한 복수행 서브쿼리를 만들어 봅시다. 

직원(EMP) 테이블에서 부서별로 급여를 가장 적게 받는 직원의 부서, 이름, 급여를 출력 하는 예문을 만들어 봅시다. IN 연산자 오른쪽 서브쿼리에서 반환되는 행(ROW)은 부서별로 가장 적은 급여가 추출되어 메인쿼리와 IN 연산자에 의해 비교됩니다. 결국 부서별로 급여를 가장 적게 받는 직원을 선택하게 됩니다.

SELECT DEPTNO, ENAME, SAL  ❷ FROM    EMP WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MIN(SAL)  ❶                                                FROM    EMP                                                GROUP BY DEPTNO);

<실행 결과>

	DEPTNO	ENAME	SAL
1	30	JAMES	950
2	10	MILLER	1300
3	20	SMITH	800

❶ 서브쿼리가 먼저 실행이 되어 아래의 결과를 반환합니다.

	DEPTNO	MIN(SAL)
1	30	950
2	10	1300
3	(null)	5000
4	20	800

❷ 메인쿼리의 WHERE 절에 있는 IN 연산자가 위 결과를 바탕으로 실행되어 최종 결과를 반환합니다. 

SELECT DEPTNO, ENAME, SAL

FROM   EMP

WHERE (DEPTNO, SAL) IN ( (30, 950), (10, 1300), (NULL, 5000), (20, 800)  )

DEPTNO가 NULL인 3번째 데이터는 연산 결과도 NULL이 되므로 출력에서 제외되었습니다.

위와 같은  WHERE 비교를 페어와이즈(pairwise) 방식이라 합니다. 컬럼을 쌍으로 묶어서 비교하는 것인데  만약 아래처럼 쿼리문을 작성한다면 예상치 못한 결과가 나올 수도 있습니다.

현재 EMP 테이블은 다행히도 최소급여인 1300, 950, 800을 다른 부서직원들이 가지고 있지 않아서 페어와이즈 방식으로 하지 않더라도 결과는 같지만 다른 부서원이 어떤 부서의 최소값을 가지면 출력됩니다.

SELECT DEPTNO, ENAME, SAL FROM EMP WHERE SAL IN (SELECT MIN(SAL) ❶                             FROM    EMP                             GROUP BY DEPTNO) AND DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP); ❷

<실행 결과>

	DEPTNO	ENAME	SAL
1	20	SMITH	800
2	30	JAMES	950
3	10	MILLER	1300

❶ : 서브쿼리의 결과로 800, 950,  1300, 5000 이 나옵니다. 

❷ : 서브쿼리의 결과로 10, 20, 30, null 이 나옵니다.

결국 위 쿼리는 SAL IN (800, 950,  1300, 5000) AND DEPTNO IN (10, 20, 30, null) 로 조건절이 실행되고, null은 비교대상에서 제외되므로 급여가 5000인 데이터는 부서코드가 null 이기에 제외 되었습니다. 만약 30번 부서원중 급여가 20번 부서의 최소값인 800을 가진 사원이 있었다면 출력이 되었겠죠? 결국 부서별 최소 급여의 조건에 맞지 않으므로 위 쿼리문은 잘못 사용된 것 입니다.

5.3.2 특정 부서의 최소 급여보다 급여를 많이 받는 직원정보 출력 : ANY

ANY 연산자는 조건을 만족하는 값이 하나라도 있으면 결과를 리턴하는 SQL 함수  입니다. 이를 이용하여 직원의 급여가 10번 부서원들의 최소급여 보다 많이 받는 직원의 정보를 출력해 봅시다.

ANY 연산자를 이용하여 10번 부서 급여의 최소값보다 많은 급여를 받는 직원정보를 쿨력해 봅시다.

SELECT ❸ENAME, SAL  FROM    EMP WHERE❷SAL > ANY (SELECT SAL FROM EMP ❶                                      WHERE DEPTNO = 10);

<실행 결과>

	ENAME	SAL
1	KING	5000
2	FORD	3000
3	SCOTT	3000
4	JONES	2975
5	BLAKE	2850
6	CLARK	2450
7	ALLEN	1600
8	TURNER	1500

<실행 순서>

❶ EMP 테이블에서 10번 부서 직원들의 급여 목록을 확인합니다. 

<실행 결과>

	SAL
1	2450
2	1300

❷ 조건절이 실행됩니다. 10번 부서의 급여 목록을 받아왔으니, 결과를 반영하여 WHERE SAL > ANY (1300, 2450)가 실행됩니다. > ANY 연산자이므로 최솟값인 1300보다 큰 행을 추출합니다. 즉 직원 테이블의 모든 행을 하나씩 읽어 들여 그 직원의 급여가 1300 보다 큰지, 2450 보다 큰지 비교하여 둘중 하나라도 만족하면 참이 되고 추출 됩니다.

❸ 서브쿼리 결과에서 이름과 급여 정보를 출력합니다.

SAL < ANY(1300, 2450) 조건으로도 출력해봅시다.

SELECT ENAME, SAL  FROM    EMP WHERE ❶SAL < ANY (SELECT SAL FROM EMP                                       WHERE DEPTNO = 10);

<실행 결과>

	ENAME	SAL
1	SMITH	800
2	JAMES	950
3	ADAMS	1100
4	WARD	1250
5	MARTIN	1250
6	MILLER	1300
7	TURNER	1500
8	ALLEN	1600

직원 테이블의 모든 행을 탐색 하면서 급여가 1300 보다 적은지, 급여가 2450 보다 적은지를 비교하여 둘 중 하나라고 만족하면 참이 되어 추출 됩니다. 즉 ❶ SAL값이 서브쿼리 결과의 최댓값보다 적어야 조건을 만족하므로  급여가 2450보다 적은 직원의 이름과 급여 정보를 출력합니다. ​

 

 

#오라클 #서브쿼리 #subquery #복수행서브쿼리