연관성 있는 서브 쿼리

메인 쿼리와의 연관성이 있는 서브 쿼리, 즉 메인 테이블과 조인 조건이 걸린 서브 쿼리를 말한다.

입력

    SELECT a.department_id, a.department_name
      FROM departments a
     WHERE EXISTS ( SELECT 1
                      FROM job_history b
                     WHERE a.department_id = b.department_id );

결과

    DEPARTMENT_ID DEPARTMENT_NAME
    ------------- -----------------
    20            마케팅
    50            배송부
    60            IT
    80            영업부
    90            기획부
    110           경리부

    6개의 행이 선택됨

이 쿼리는 연관성 있는 서브 쿼리로 서브 쿼리 안에서 메인 쿼리에서 사용된 부서 테이블의 부서번호와 job_history 테이블의 부서번호가 같은 건을 조회하고 있다. 또한 EXISTS 연산자를 사용해서 서브 쿼리 내에 조인 조건이 포함되어 있다. 따라서 결과는 job_history 테이블에 있는 부서만 조회되었다. 또 다른 형태를 살펴 보자.

입력

    SELECT a.employee_id,
           ( SELECT b.emp_name
               FROM employees b
              WHERE a.employee_id = b.employee_id) AS emp_name,
           a.department_id,
           ( SELECT b.department_name
               FROM departments b
              WHERE a.department_id = b.department_id) AS dep_name
    FROM job_history a;

결과

    EMPLOYEE_ID EMP_NAME                 DEPARTMENT_ID DEP_NAME
    ----------- ------------------------ ------------- ------------
    102         Lex De Haan              60            IT
    101         NeenaKochhar             110           경리부
    101         NeenaKochhar             110           경리부
    201         Michael Hartstein        20            마케팅
    114         Den Raphaely             50            배송부
    122         PayamKaufling            50            배송부
    200         Jennifer Whalen          90            기획부
    176         Jonathon Taylor          80            영업부
    176         Jonathon Taylor          80            영업부
    200         Jennifer Whalen          90            기획부

    10개의 행이 선택됨

이 쿼리는 job_history 테이블을 조회하고 있는데 job_history에는 사번, 부서번호만 존재하므로 사원명과 부서명을 가져오려고 서브 쿼리를 SELECT 절에서 사용하였고 서브 쿼리 안의 WHERE 절에 조건을 추가했다. 이처럼 SELECT 절 자체에도 여러 개의 서브 쿼리를 넣을 수 있다. 또한 각 서브 쿼리가 독립적이므로 두 개의 서브 쿼리에서 사용된 사원, 부서 테이블의 별칭을 모두 b로 사용해도 무방하다. 또 다른 예를 살펴 보자.

입력

    SELECT a.department_id, a.department_name
      FROM departments a
     WHERE EXISTS ( SELECT 1
                      FROM employees b
                     WHERE a.department_id = b.department_id → ①
                       AND b.salary > ( SELECT AVG(salary) → ②
                                          FROM employees )
                   );

결과

    DEPARTMENT_ID DEPARTMENT_NAME
    ------------- -----------------
    20            마케팅
    30            구매/생산부
    40            인사부
    50            배송부
    60            IT
    70            홍보부
    80            영업부
    90            기획부
    100           자금부
    110           경리부

    10개의 행이 선택됨

이 쿼리에서는 2개의 서브 쿼리가 사용되었다. 먼저 ②에서 평균급여를 구하고 이 값보다 큰 급여의 사원을 걸러낸 다음(연관성 없는 서브 쿼리), ①에서 평균급여 이상을 받는 사원이 속한 부서를 추출한 것(연관성 있는 서브 쿼리)이다. 이처럼 서브 쿼리는 여러 단계에 걸쳐 사용이 가능하다.