OracleSQL - 서브쿼리

서브쿼리는 특정 테이블을 사용자의 설정에 맞게 재정의 할 수 있도록 해주는 기능이다. 쿼리문 안에 소괄호를 이용해 또 다른 쿼리문을 작성함으로써 그 쿼리문에 해당하는 정보를 이미 만들어 놓은 테이블처럼 사용할 수 있다.

 

예를들어 다음과 같이 EMP 테이블에서 각 직원의 연봉을 구하는 쿼리문을 작성해야 한다고 가정해보자. 하지만 테이블에는 급여(SAL) 정보밖에 존재하지 않는다. 그러면 쿼리문은 다음과 같을 것이다.

 

SELECT ENAME "사원이름", DEPT "부서"
        , (SAL * 12 + BONUS) "연봉"
FROM EMP;

정말 간단해 보인다. 하지만 WHERE 절을 추가하면 어떨까? 연봉이 1000만에서 2000만 사이인 사람을 구한다고 해보자. 쿼리문은 다음과 같다.

 

SELECT ENAME "사원이름", DEPT "부서"
        , (SAL * 12 + BONUS_FIRST + BONUS_SECOND -
          ((SAL * 12 + BONUS_FIRST + BONUS_SECOND) * 0.3)) "연봉"
FROM EMP
WHERE 1000 <=  (SAL * 12 + BONUS_FIRST + BONUS_SECOND -  
                         ((SAL * 12 + BONUS_FIRST + BONUS_SECOND) * 0.3))
AND 
           2000 >=  (SAL * 12 + BONUS_FIRST + BONUS_SECOND -
                         ((SAL * 12 + BONUS_FIRST + BONUS_SECOND) * 0.3));

연봉을 구하는 코드가 중첩되어 쓰이기 때문에 많이 지저분해 보인다. 만약 "연봉"과 같은 코드가 더 복잡한 연산으로 길어진다면, 더더욱 그래보일 것이다. 그러면 "연봉" 자체를 변수처럼 사용할 수는 없을까? 이것을 도와주는 것이 바로 서브 쿼리문이다. 위 문제를 서브 쿼리문으로 작성하면 다음과 같다.

 

SELECT T.사원이름, T.부서, T.연봉
FROM
(
    SELECT ENAME "사원이름", DEPT "부서"
            , ((SAL * 12 + BONUS_FIRST + BONUS_SECOND -  
              ((SAL * 12 + BONUS_FIRST + BONUS_SECOND) * 0.3)) "연봉"
    FROM EMP
) T
WHERE 1000 <= T.연봉 AND 2000 >= T.연봉

쿼리문을 사용하니 중첩되는 코드도 없어 더 깔끔해졌다.

 

서브 상관 쿼리(= 상관 서브 쿼리) 

서브 쿼리문을 조건절(WHERE나 HAVING절)에 쓰는 경우에 서브 상관 쿼리라고 한다. 

 

EMP 테이블에서 RANK() 함수를 사용하지 않고 사원명, 급여, 급여 등수를 구할때 서브 상관 쿼리를 이용한 쿼리문은 다음과 같다.

 

SELECT ENAME "사원명", SAL "급여"
        , (SELECT COUNT(*) + 1
           FROM EMP
           WHERE SAL > E.SAL)
FROM EMP E;

 

조금 더 복잡하게 EMP 테이블에서 사원명, 급여, 부서번호, 부서내급여등수, 전체급여등수 항목을 RANK() 함수 없이 사용해야 한다.

 

SELECT T.ENAME "사원명", T.SAL" 급여", T.DEPTNO "부서번호"
         , (SELECT COUNT(*) + 1
         , FROM EMP
         , WHERE DEPTONO = T.DEPNO ANS SAL > T.SA) "부서내급여등수"
         , (SELECT COUNT(*) + 1
         , FROM EMP
         , WHERE SAL > T.SAL) "전체급여등수"
FROM EMP T;     
(T와 같이 테이블 뒤에 사용자가 직접 명칭을 설정하면 다른 쿼리문에서 그것을 이용할 수 있다.)