11. SUB QUERY

이전부터 많이 썼던 서브쿼리는, 하나 이상의 조건을 한 문단에 처리하기 위해 사용하는 기법이다.

쿼리 안에 다른 쿼리를 담는 형태를 의미한다.

바깥에 있는 쿼리를 메인 쿼리, 내부에 있는 쿼리를 서브 쿼리라고 한다.

 

서브쿼리의 간단한 예시는 다음과 같다.

SELECT *
FROM EMP
WHERE SAL>(SELECT SAL
FROM EMP
WHERE ENAME = 'SMITH');

매우 익숙한 모습이다.

 

서브쿼리는 주의할 점이 있다.

다음과 같은 쿼리를 보자.

SELECT *
FROM EMP
WHERE SAL= (SELECT SAL
FROM EMP
WHERE JOB = 'MANAGER');

이 쿼리를 실행시켜보면

다음과 같은 오류가 뜬다.

내부에 있는 쿼리만 실행시켜보면,

3개 행이 실행되어 있음을 볼 수 있다. 그렇기 때문에 서브쿼리에서 어떤 값을 써야할 지 알 수 없게 되어버린 것이다. 이렇게 행이 두개 이상 나오게 되는 것을 다중 행 서브쿼리라고 한다.

 

단일행 서브쿼리에 들어가는 연산자는 다음과 같다.

=	같다
<>	같지 않다
>	크다
>=	크거나 같다
<	작다
<=	작거나 같다.

다음과 같은 연산자는 아주 익숙하므로, 바로 다중 행 서브쿼리를 보자.

IN	서브쿼리 결과와 같은 값을 찾는다.
EXISTS	서브쿼리의 값이 있을 경우 메인쿼리를 수행한다
>ANY	서브쿼리 결과 중 최솟값 반환
<ANY	서브쿼리 결과 중 최대값 반환
>ALL	서브쿼리 결과 중 최대값 반환
<ANY	서브쿼리 결과 중 최솟값 반환

따라서, 위의 오류는 다음과 같이 해결할 수 있을 것이다.

SELECT *
FROM EMP
WHERE SAL IN (SELECT SAL
FROM EMP
WHERE JOB = 'MANAGER');

 

ANY를 쓰는 서브쿼리의 예시를 보자.

SELECT NAME,EMP_TYPE,TO_CHAR(PAY,'$999,999,999') SALARY
FROM EMP2
WHERE PAY>ANY(SELECT PAY FROM EMP2 WHERE EMP_TYPE='정규직');

>ANY는 최솟값을 찾는 다중 행 서브쿼리 연산자이다.

다음 쿼리는 정규직의 최솟값보다 높은 연봉을 받는 사람을 찾는 쿼리이다.

 

다중행 쿼리를 이용하지 않으려면 그룹함수를 이용하면 되기는 한다.

SELECT NAME,EMP_TYPE,PAY
FROM EMP2
WHERE PAY>(
SELECT MIN(PAY) 
FROM EMP2 
WHERE EMP_TYPE='정규직'
);

 

ALL을 이용한 예시도 보자.

SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE WEIGHT<ALL(SELECT WEIGHT FROM STUDENT WHERE GRADE=2);

<ALL이므로, 이 값도 최솟값을 찾는 다중행 서브쿼리이다.

즉, 2학년 학생 중 무게가 제일 적은 학생보다 작은 무게를 가지는 학생을 찾는 쿼리이다.

 

이것 역시 다중행 서브쿼리 연산자를 이용하지 않으려면 그룹함수를 이용하면 된다.

SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE WEIGHT<(SELECT MIN(WEIGHT) FROM STUDENT WHERE GRADE=2);

 

서브쿼리와 그룹함수가 함께 쓰인 예시도 보자.

SELECT D.DNAME,E.NAME,E.PAY 
FROM DEPT2 D, EMP2 E
WHERE E.DEPTNO=D.DCODE AND PAY<ALL(
SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO
);

이 함수는 부서의 평균연봉 중 제일 낮은 평균보다 낮은 연봉을 받는 사람을 찾는 쿼리이다.

 

이 쿼리도 마찬가지로 그룹함수화 할 수 있는데,

SELECT D.DNAME,E.NAME,E.PAY 
FROM DEPT2 D, EMP2 E
WHERE E.DEPTNO=D.DCODE AND PAY<(
SELECT MIN(APAY)
FROM
(SELECT AVG(PAY) "APAY"
FROM EMP2
GROUP BY DEPTNO));

앞서 말한 적 있는데, SELECT 가 많아질 수록 연산이 느려진다. 따라서, 그룹함수보다 연산자등을 써서 SELECT를 줄이는 것도 한 방법이다.