SUB-QUERY) 상호연관 서브쿼리 (exists, not exists)

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

상호연관 서브쿼리

- 상관 서브쿼리

- 메인쿼리의 값을 전달 받아 서브쿼리를 수행 후 결과 값을 메인쿼리에 반환하는 쿼리

- 일반 서브쿼리는 블럭 잡아서 단독으로 실행 가능한 반면, 상관 서브쿼리는 그렇지 않음

- 메인 쿼리의 매 행마다 다른 값을 서브쿼리에 전달

 

직급별 평균급여보다 많은 급여를 받는 사원 조회

[조인 버전]

select
    e.emp_name, e.job_code, e.salary, s.avg_sal
from
    employee e left join (
        select
            job_code,
            trunc(avg(salary)) avg_sal
        from
            employee
        group by
            job_code
    ) s
    on e.job_code = s.job_code
where
    e.salary > s.avg_sal;

 

[상관서브쿼리 버전]

select
    emp_name, job_code, salary
from
    employee e
where
    salary > (
        select
            trunc(avg(salary))
        from
            employee
        where
            job_code = e.job_code
    );

 

선동일과 송종기의 정보로 예를 들어 설명해보자면, 메인 쿼리는 매 행마다 실행된다고 하였습니다.

 

따라서 처음 '선동일'의 행이 실행이 되었을 것이고, where절에 가니 salary > (서브쿼리~)를 만나게 됩니다.

서브쿼리를 실행하려고 보니 메인 쿼리의 e.job_code를 요구했죠.

그렇다면 현재 실행 중인 '선동일'의 job_code인 'J1'이 e.job_code에 넘어오게 됩니다.

그렇다면 서브쿼리의 where절에서 job_code = 'J1', 즉 job_code가 'J1'인 사원들을 추려내고 그에 대한 급여 평균을 구하고 다시 메인 쿼리에 해당 값을 넘겨주어 메인 쿼리의 where절을 실행 → true면 결과 집합 포함 / false면 미포함 하게 됩니다.'선동일'의 직급인 'J1'은 평균 급여가 800만원이므로 false! 결과 집합에 미포함 되었습니다.

 

그 후 다음 행으로 넘어가 '송종기' 행을 실행하게 되는데 위와 동일한 과정을 거친 후 '송종기'의 직급인 'J2'는 평균 급여가 472만원 대이므로, true! 결과집합에 포함 되었습니다.

 

이러한 과정을 거쳐 총 9명의 정보가 출력되었습니다.

 

부서코드별 평균 급여보다 많은 급여를 받는 사원 조회 (인턴사원 제외)

select
    emp_name, dept_code, salary
from
    employee e
where
    salary > (
        select
            avg(salary)
        from
            employee
        where
            dept_code = e.dept_code
    );

 

☞ exists 연산자

exists(서브쿼리)

- 서브쿼리의 결과 집합이 1행 이상인 경우에 true를 반환, 0행이면 false처리

 

부서 테이블에서 실제 부서원이 존재하는 부서인 지 조회

select
    *
from
    department d
where
    exists(
        select
            1
        from
            employee
        where
            dept_code = d.dept_id
    );

exists()는 서브쿼리의 결과 집합이 1행 이상이라면 true, 0행이라면 false를 리턴한다고 하였습니다.

마찬가지로 각 행마다 실행이 되는데 'D2', 'D3'의 부서코드를 예를 들어보겠습니다.

만일 현재 메인 쿼리에서 'D2'의 부서코드를 가진 행을 실행하였다고 했을 때 where절에서 exists(서브쿼리)를 만나 실행하게 될 것입니다.

여기서 메인쿼리에서 실행 중인 'D2'가 d.dept_id에 넘어오게 됩니다.

employee 테이블의 dept_code = 'D2'인 행을 추리게 되고 employee 테이블에서 dept_code가 'D2'인 행이 있기 때문에 true를 리턴하게 되어 결과집합에 포함시킵니다.

 

그 후 다음 행으로 넘어가 'D3'행을 실행하게 되고 위와 동일한 과정을 거치게 됩니다.하지만 employee 테이블에서 dept_code가 'D3'인 행이 없기 때문에 false를 리턴하게 되어 결과집합에 미포함시키게 되는 것입니다.

 

이러한 과정을 통해 위와 같은 결과가 출력되었습니다.

 

관리하는 사원이 있는 관리자 사원 조회

select
    emp_name
from
    employee e
where
    exists(
        select
            1
        from
            employee
        where
            manager_id = e.emp_id
    );

 

☞ not exists

- 서브쿼리가 행이 존재하면 false, 존재하지 않으면 true

select
    emp_name
from
    employee e
where
    not exists(
        select
            1
        from
            employee
        where
            salary > e.salary   -- 최대
--            salary < e.salary      -- 최소
    );