결과가 하나 이상인 서브쿼리

– 서브쿼리를 통해 가져온 결과가 하나이상인 경우 결과를 모두 만족하거나 결과 중 하나만 만족하거나 해야하는 경우가 있다.

• 이때 다음과 같은 연산자를 사용하면 된다.
• IN : 서브쿼리의 결과 중 하나라도 일치하면 조건은 참이 된다.
• ANY, SOME : 서브쿼리의 결과와 하나이상 일치하면 조건은 참이된다.
  IN과는 달리 조건을 자유롭게 줄 수 있다.
• ALL : 서브쿼리의 결과와 모두 일치해야 조건은 참이된다.

ex)

-- 3000이상의 급여를 받는 사원들과 같은 부서에 근무하고 있는 사원의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where deptno in (select deptno
                from emp
                where sal>=3000);

ex)

-- 직무가 CLERK인 사원과 동일한 부서에 근무하고 있는 사원들의 사원번호, 이름, 입사일을 가져온다.
select empno, ename, hiredate
from emp
where deptno in (select deptno
                from emp
                where job = 'CLERK');

ex)

-- KING을 직속상관으로 가지고 있는 사원들이 근무하고 있는 근무 부서명, 지역을 가지고 온다.
select dname,loc
from dept
where deptno in (select deptno
                from emp
                where mgr = (select empno
                            from emp
                            where ename = 'KING'));

ex)

-- CLERK들의 직속상관의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where empno in (select mgr
                from emp
                where job = 'CLERK');

ex)

-- 각 부서별 급여 평균보다 더 많이 받는 사원의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal > all (select avg(sal)
                from emp
                group by deptno);

select empno, ename, sal
from emp
where sal > (select max(avg(sal))
            from emp
            group by deptno);

all을 사용하는 경우 대부분 max함수를 사용하여 대치가 가능하다.

ex)

-- 각 부서별 급여 최저치보다 더 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal > all (select min(sal)
                from emp
                group by deptno);

select empno, ename, sal
from emp
where sal > (select max(min(sal))
                from emp
                group by deptno);

ex)

-- SALESMAN보다 급여를 적게 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal < all (select sal
                from emp
                where job = 'SALESMAN');

select empno, ename, sal
from emp
where sal <  (select min(sal)
            from emp
            where job = 'SALESMAN');

ex)

-- 각 부서별 최저 급여 액수 보다 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal> any (select min(sal)
                from emp
                group by deptno);