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

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

• 이때 다음과 같은 연산자를 사용하면 된다.
• 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);

Comment and share

서브쿼리

• 쿼리문 안에 들어가는 쿼리문을 서브쿼리라고 한다.
• 쿼리문 작성시 사용되는 값을 다른 쿼리문을 통해 구해야 할 경우 사용한다.

ex)

-- SCOTT사원이 근무하고 있는 부서의 이름을 가져온다.
select dname
from dept
where deptno = (select deptno
                from emp
                where ename = 'SCOTT');
                
-- join으로도 가능하다.
select a2.dname
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.ename = 'SCOTT';

ex)

-- SMITH와 같은 부서에 근무하고 있는 사원들의 사원번호, 이름, 급여액, 부서이름을 가져온다.
select a1.empno, a1.ename, a1.sal, a2.dname
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.deptno = (select deptno
                                            from emp
                                            where ename = 'SMITH');

ex)

-- MARTIN과 같은 직무를 가지고 있는 사원들의 사원번호, 이름, 직무를 가져온다.
select empno, ename, job
from emp
where job = (select job
            from emp
            where ename = 'MARTIN');

ex)

-- ALLEN과 같은 직속상관을 가진 사원들의 사원번호, 이름, 직속상관이름을 가져온다.
-- a : 사원의 정보
-- b : 직속상관 정보
select a.empno, a.ename, b.ename
from emp a, emp b
where a.mgr = b.empno and a.mgr = (select mgr
                                from emp
                                where ename = 'ALLEN');

ex)

-- WARD와 같은 부서에 근무하고 있는 사원들의 사원번호, 이름, 부서번호를 가져온다.
select a.empno, a.ename, b.deptno
from emp a, dept b
where a.deptno = b.deptno and a.deptno = (select deptno
                                        from emp
                                        where ename = 'WARD');

ex)

-- SALESMAN의 평균 급여보다 많이 받는 사원들의 사원번호, 이름, 급여를 가져온다.
select empno, ename, sal
from emp
where sal > (select avg(sal)
            from emp
            where job = 'SALESMAN');

ex)

-- DALLAS 지역에 근무하는 사원들의 평균급여를 가져온다.
select trunc(avg(sal))
from emp a, dept b
where a.deptno = b.deptno and b.loc = (select loc
                                    from dept
                                    where loc = 'DALLAS');

ex)

-- SALES 부서에 근무하는 사원들의 사원번호, 이름, 근무지역을 가져온다.
select a1.empno, a1.ename, a2.loc
from emp a1, dept a2
where a1.deptno = a2.deptno and a1.deptno = (select deptno
                                            from dept
                                            where dname = 'SALES');

ex)

-- CHICAGO 지역에 근무하는 사원들 중 BLAKE가 직속상관인 사원들의 사원번호, 이름, 직무를 가져온다.
select empno, ename, job
from emp
where deptno = (select deptno
                from dept
                where loc = 'CHICAGO')
                and mgr = (select empno
                        from emp
                        where ename = 'BLAKE');

Comment and share