CORELATED SUBQUERY

자기 부서의 평균보다 큰 salary를 받는 사원의 정보를 조회하시오.

일반적으로 위 문장을 쿼리로 작성하기 위해서는 다음과 같은 과정을 진행한다.

1. 자신이 해당하는 부서의 평균을 구한다.
2. 자신의 SALART가 해당 부서의 평균보다 크면 정보를 출력한다.

-- 1)
select avg(salary)
from hr.employees
group by department_id;

-- 2)
select *
from hr.employees
where salary > (1번 결과)

이러한 방법을 CORELATED SUBQUERY를 이용하면 쉽게 개선할 수 있다.

select *
from hr.employees e
where salary > (
	select avg(salary)
    from hr.employees
    where department_id = e.department_id
    );

위에서 작성된 쿼리를 보면 서브쿼리 안에 메인 쿼리의 칼럼데이터가 들어간 것을 볼 수 있다.

일반적으로 서브쿼리가 먼저 실행되지만, 이 경우 서브쿼리를 먼저 실행할 수 없다는 것을 알 수 있다.

이처럼 main쿼리의 데이터를 sub쿼리에서 사용하는 서브쿼리를 상호관련 서브쿼리(CORELATED SUBQUERY)라고 부르며 실행되는 과정은 다음과 같다.

1. 메인 쿼리절을 실행하여 from절의 테이블을 읽어와 active set 결과를 생성한다.
2. 생성된 active set에서 첫 번째 행을 후보행으로 배정하여 후보행 값을 서브쿼리로 전달한다.
3. 전달받은 후보행 값을 바탕으로 서브 쿼리절 실행한다.
4. 서브쿼리의 결과를 받아 나머지 연산 진행. True면 결과집합에 저장, False면 버린다.
5. 마지막 행 까지 후보행을 다음행으로 재설정하면서 2~4 반복

WHERE EXIST (서브쿼리)

존재 여부를 판단하는 연산자로 EXISTS를 사용할 수 있다. 단순히 존재 여부를 판단하기 때문에 BOOLEAN 값을 반환하며 기준 칼럼, 칼럼의 이름을 작성하면 안된다는 특징이 있다. (서브쿼리 포함)

이렇게 되면 서브쿼리에서도 select 문에 칼럼명을 쓸 수 없기 때문에 임의의 값('x')을 넣어 해결한다.

사원이 존재하는 부서의 정보를 출력하시오

select *
from hr.departments
where department_id in (
	select department_id
    from hr.employees )

select *
from hr.departments d
where EXISTS (
	select 'x'
    from hr.employees e
    where e.department_id = d.department_id)

IN 연산자를 이용해도 풀 수 있지만 EXISTS연산자를 사용할 때가 더욱 성능이 좋다.

그 이유는 EXISTS연산자는 내부적으로 BREAK를 수행하기 때문에 IN과 달리 모든 데이터를 탐색하지 않기 때문이다. 또한 값을 직접적으로 비교하는 것이 아니라 존재 여부만을 판단하기 때문에 좀 더 좋은 퍼포먼스를 보여준다.

NOT EXISTS

EXISTS와 반대되는 개념이다. 하지만 NOT IN과는 차이가 존재한다.

NOT IN의 경우 서브쿼리 안의 데이터가 NULL이면 AND연산자가 진행되면서 정상적인 결과를 출력하지 않지만, EXISTS의 경우 FALSE값이 TRUE 값으로 변하는 것이기 때문에 정상적인 결과를 출력할 수 있다.

select *
from hr.departments d
where NOT EXISTS (
	select 'x'
    from hr.employees e
    where e.department_id = d.department_id);