[Oracle] 21.다중행 서브쿼리

in DB on Oracle


다중 행 서브쿼리



이번 장에서는 서브쿼리의 결과가 한 행이 아닌 여러 개일 때에 관한 예제를 살펴보겠습니다!

다중 행 서브쿼리는 반드시 다중 행 연산자와 함께 사용해야하며,

다중 행 연산자는 in, some/any, all, exist 가 있습니다.

in 연산자




  • 부서별로 가장 급여를 많이 받는 사원의 정보(사번, 사원이름, 급여, 부서번호) 조회




select
    employee_id "사원 번호",
    first_name||' '||last_name "사원 이름", 
    salary "급여", 
    department_id "부서 번호"
from 
    employees
where
    (nvl(department_id,0), salary) in              
    (select nvl(department_id,0), max(salary)
    from employees
    group by department_id)
order by department_id;




image



위의 예제에서 주의할 것이 in 연산자로 null을 비교 못하기에 null을 nvl을 써서 다른 값으로 치환해줘야합니다.

또, 정렬을 하고 싶으면 서브쿼리 밖에 ORDER BY문을 선언해야합니다.

not in도 마찬가지 입니다!


  • 자신이 상사가 아닌 사원들 조회




SELECT
    *
FROM
    employees
WHERE
    employee_id NOT IN (
        SELECT DISTINCT
            manager_id
        FROM
            employees
);



위의 처럼 서브쿼리의 결과로 manager_id가 null이 나올 수 있는데 null 처리를 안해주면

다음처럼 결과가 제대로 나오지 않습니다.


image



따라서 꼭 null을 nvl로 치환해주어야 합니다. 


SELECT
    *
FROM
    employees
WHERE
    employee_id NOT IN (
        SELECT DISTINCT
            nvl(manager_id,0)
        FROM
            employees
);




image



ALL : 모두 만족




  • 영업 사원들보다 급여를 많이 받는 사원들의 이름과 급여, 직급을 출력하되 영업사원은 포함 x




SELECT
    first_name|| ' '|| last_name "사원 이름",
    salary "급여",
    job_id "직급"
FROM
    employees
WHERE
    job_id NOT LIKE '%SA%'
    AND salary > ALL (
        SELECT
            salary
        FROM
            employees
        WHERE
            employees.job_id LIKE '%SA%'
);




image



ANY : 하나라도 만족



  • 영업 사원들의 최소 급여보다 많이 받는 사원들의 이름과 급여와 직급을 출력하되 영업 사원은 포함x




SELECT
    first_name|| ' '|| last_name "사원이름",
    salary "급여",
    job_id "직급"
FROM
    employees
WHERE
    job_id NOT LIKE '%SA%'
    AND salary > ANY (
        SELECT
            salary
        FROM
            employees
        WHERE
            employees.job_id LIKE '%SA%'
);




image