[ Oracle ] 서브쿼리(SubQuery) | 서브쿼리 예제

 

Oracle SubQuery 특징

 

 

• () 안에 작성한다.
• 외부쿼리(=메인쿼리)와 비교할 때 알맞은 연산자를 사용한다. (단일행/복수행, 단일 컬럼/복수 컬럼)
• 연관성이 있는 쿼리(correlated subquery) 또는 없는 쿼리
• group by, order by 절을 제외하고 모든 절에 사용 가능하다.

 

복수행이 리턴될 때

 

 

• < any : 서브 쿼리 리턴 값 중 최대값
• < any : 서브 쿼리 리턴 값 중 최소값
• < all : 서브 쿼리 리턴값 중 최소값
• > all : 서브 쿼리 리턴 값 중 최대값

 

 

Scott 스키마를 활용한 Subquery 활용

 

 

< DEPT 테이블 구조 >

 

 

 

 

 

 

< EMP 테이블 구조 >

 

 

 

 

 

문제 :  부서명이 'SALES' 인 사원의 사번과 이름은?

 

 

< join 을 이용한 경우 >

 

select empno, ename from emp e

join dept d on d.deptno=d.deptno

where d.dname='SALES';

 

 

 

< SubQuery 를 이용한 경우 >

 

 

select empno, ename from emp
where deptno in (select deptno
from dept
where dname='SALES');

 

 

 

 

문제 : 사번이 '7844'인 사원의 job 과 동일한 job 인 사원의 사번, 이름, job 을 출력!

 

 

select empno,ename,job
from emp 
where job in (select job from emp where empno='7844');

 

 

 

 

 

 

 

문제 : 사번이 '7521' 인 사원의 job 과 동일하고

'7900' 인 사번의 급여보다 많은 급여를 받는 사원의 사번, 이름, job, 급여를 출력하라

 

 

select empno, ename, job, sal
from emp
where job in (select job from emp where empno='7521') and
sal > (select sal from emp where empno='7900');

 

 

 

 

 

 

문제 : 가장 적은 급여를 받는 사원의 사번, 이름, 급여를 출력!

 

 

 

select empno, ename, sal from emp
where sal = (select min(sal) from emp);

 

 

 

 

문제 : 부서별 최소 급여 중에서 30번 부서의 최소급여보다는 큰 최소급여인 부서의

부서번호, 최소 급여를 출력하라

 

 

select deptno,min(sal) 
from emp 
group by deptno
having min(sal)>(select min(sal) from emp where deptno='30');

 

 

 

 

 

문제 : job이 CLERK 인 사원이 2명 이상 있는 부서의 부서번호, 부서명을 출력하라

 

 

< Join 으로 풀기 >

 

 

select d.deptno, d.dname
from emp e join dept d on e.deptno=d.deptno
where e.job='CLERK'
group by d.deptno,d.dname
having count(*)>=2;

 

 

 

 

<SubQuery 로 풀기>

 

 

 

select deptno, dname
from dept
where deptno = (
select deptno from emp 
where job='CLERK'
group by deptno
having count(*)>=2
);

 

 

 

 

 

 

<correlated subquery 로 풀기>

 

 

select deptno,dname
from dept d
where 2 <=(select count(*)
            from emp
            where job='CLERK' and d.deptno=deptno);

 

 

 

 

 

 

문제 : job 이 'CLERK' 인 사원이 한명이라도 있는 부서의 부서명만 출력하라

 

 

exists : 존재하냐를 묻는 키워드이다.

 

 

select dname
from dept d
where exists (select *
              from emp e
              where job='CLERK' and
              d.deptno=e.deptno);

 

 

 

 

 

문제 : 각 부서별로 최소 급여를 받는 사원의 부서번호, 부서명, 사번, 이름, 급여를 출력하라

 

 

select d.deptno,d.dname, e.empno, e.ename, e.sal
from dept d join emp e on d.deptno=e.deptno
where (d.deptno,e.sal) in 
(select deptno,min(sal) from emp group by deptno);

 

 

 

 

이런식으로 풀어도 된다.

 

 

 

select deptno, (select dname from dept where e.deptno=deptno),
empno, ename, sal
from emp e
where (deptno,sal) in (select deptno, min(sal)
from emp group by deptno);

 

 

문제 : 직속상사가 없는 사원을 포함해서

부하직원사번, 부하직원명, 직속상사사번, 직속상사명을  출력하라

 

 

select empno, ename, mgr, 
(select ename from emp where e.mgr=empno)
from emp e;

 

 

 

 

 

 

문제 : 평균 급여보다 많거나 같고 최대 급여보다는 적은 급여를 받는 사원의

사번, 이름, 급여를 출력하라

 

 

select empno, ename, sal
from emp
where 
sal>=(select avg(sal) from emp) 
and
sal < (select max(sal) from emp);

 

 

 

 

 

 

select empno, ename, sal
from emp e
join
(select avg(sal) a,max(sal) m from emp) i
on e.sal >= i.a and e.sal < i.m

 

 

이런 식으로 풀어도 된다.

 

 

 

 

 

 

* rownum : 쿼리의 결과로 반환되는 로우에 붙혀지는 일련번호로 의사컬럼이다.

 

 

 

 

 

문제 : 월급이 높은 순으로 사번, 이름, 월급을 상위 5명만 출력하라

 

 

select empno, ename, sal 
from (select * from emp order by 3 desc)
where rownum <=5
order by 3 desc;