# 12 SUB QUERY(서브 쿼리) , ROWNUM

SUB QUERY(서브 쿼리)

 

: 서브 쿼리는 하나의 select 문장의 안에 포함된 또 하나의 select 문이다.

 

그렇기 때문에 서브 쿼리문을 포함하고 있는 쿼리문을 메인쿼리, 포함된 또 하나의 쿼리문을 서브 쿼리라고 한다. 

 

서브쿼리는 비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호 안에 넣어야 한다. 

서브 쿼리는 메인 쿼리가 실행되기 전에 한 번만 실행된다. 

 

 

(예) 'JONES'의 부서명을 출력하시오 

방법 1
>> select deptno from emp where ename='JONES';  // deptno 20을 구해서 아랫 문장에 넣어줌 
      select dname from dept where deptno=20 ; 

방법 2 (조인 방법)
>> select e.ename, e.deptno, d.dname from emp e, dept d where e.deptno = d.deptno ;

방법 3(서브 쿼리 방법)
>> select dname from dept where deptno=(select deptno from emp where ename='JONES');

 

Q ) CLARK과 같은 부서에서 근무하는 사원의 이름과 부서 번호를 출력하는 쿼리를 작성하시오         

(단 클락은 제외)

>> select ename, deptno from emp
   where deptno=(select deptno from emp where ename='CLARK') and ename<>'CLARK';

 

Q ) WARD와 동일한 직급을 가진 사원의 이름과 월급을 출력하는 쿼리문을 작성하시오 

>> select ename, sal from emp where JOB=(select job from emp where ename='WARD') AND ename<> 'WARD';

 

Q ) 평균 급여 이상으로 받는 사원의 이름과 급여, 부서 번호를 출력하되 급여가 많은 순서로 출력되도록 하시오. 

>> select ename, sal, deptno from emp where sal>=(select avg(sal) from emp ) order by sal desc;

 

 

 

---

단일행 서브 쿼리 

: 서브 쿼리를 수행한 결과가 1건만 나오고 이 결과를 메인 쿼리로 전달해서 메인 쿼리를 수행하게 된다. 

단일행 서브 쿼리는 수행 결과가 오직 하나의 row로만 반환하는 서브 쿼리를 갖는 것을 말한다.

 

: 메인 쿼리의 where 절에는 단일행 비교 연산자를 이용한다. 

= 같다 / > 크다 / < 작다  /  >=  크거나 같다 / <= 작거나 같다 /  <> 같지 않다. 

 

 

Q ) 자신의 직속상관이 'king'인 사원의 이름과 급여를 출력하시오

 

서브 쿼리 방법
>>  select ename, sal from emp where mgr = (select empno from emp where ename ='KING');
조인 방법
>>  select e.ename, e.sal from emp e , emp f where e.mgr =f.empno and f.ename='KING';

 

 

Q )부서 번호가 10번인 사원들 중에서 최대 급여를 받는 사원의 이름과 월급 직급을 출력하시오.

>> select ename, sal, job from emp where sal=(select max(sal) from emp where deptno=10);

 

 

Q )'DALLAS'에서 근무하는 사원의 이름, 부서 번호 근무지를 출력

>> select e.ename, e.deptno, d.loc from emp e , dept d where d.deptno = e.deptno and d.loc='DALLAS';

>> select ename, emp.deptno, loc from emp, dept 
where emp.deptno = (select deptno from dept where loc='DALLAS') and loc='DALLAS';

Q ) 'CLARK' 보다 먼저 입사한 사원의 이름과 월급, 입사일을 출력하는 쿼리문 작성 (조인 , 서브 쿼리 2 방법 이용)

 

서브 쿼리 이용
>> select ename, sal, hiredate from emp where hiredate<(select hiredate from emp where ename='CLARK');

조인 이용
>>select  e.ename , e.sal, e.hiredate from emp e, emp f where  e.hiredate < f.hiredate and f.ename='CLARK';

 

 

---

다중행 서브 쿼리 

: 다중행 서브 쿼리는 반환되는 결과가 하나 이상의 행일때 사용하는 서브쿼리

  다중행 서브쿼리는 반드시 Multiple Row Operator(다중행 연산자)와 함께 사용해야 한다. 

 

종류	의미
IN	메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나라도 일치하면 참
ANY,SOME	메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상 일치하면 참
ALL	메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참
EXISTS	메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 하나라도 일치하면 참  검색 결과가 하나라도 존재하면 참

>  ALL : 최댓값 보다 큰 
<  ALL :  최소값 보다 작은 
>  ANY : 최소값 보다 큰 
<  ANY : 최대값 보다 작은

수직선 개념으로 이해하면 편하다. 

 

 

 

 

IN

Q ) IN 연산자를 이용하여 부서별로 가장 급여를 많이 받는 사원의 이름, 급여, 부서번호를 출력하시오 

 

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

 

Q ) job 이 'MANAGER'인 사원이 속한 부서의 부서번호와 부서명, 지역을 출력 하시오 

 

>> select * from dept where deptno IN (select deptno from emp where job='MANAGER');

 

 

 

 

Q ) 급여를 3000이상 받는 사원이 속한 부서의 모든 사원을 출력하는 쿼리를 작성하시오 

 

>> select* from emp where deptno in (select deptno from emp where sal>=3000  );
  (급여가 3000 이상인 사람들의 부서번호를 모두 뽑아 in으로 연산)

>> select ename, deptno, sal from  emp where deptno in (select distinct deptno  from emp where sal>=3000);
(중복을 제거한 버전)        
중복을 배제하지 않으면 연산에 많은 메모리와 시간을 소요

 

 

ALL

: aLL 연산자는 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모두 일치하면 참 

 

 

Q ) 30번 소속 사원들 중에서 급여를 가장 많이 받는 사원 보다 더 많은 급여를 받는 사람의 이름과 급여를 출력하시오

>> select ename, sal from emp where sal > ALL (select sal from emp where deptno =30);   // 다중행 서브쿼리

>> select ename, sal from emp where sal >(select max(sal) from emp where deptno=30);   // 단일행 서브쿼리 

ANY

 

Q ) 30번 소속 사원 중에 가장 작은 월급을 받는 사원 보다 더 많은 월급을 받는 사람을 출력하는 쿼리 작성 

 

>> select ename, sal from emp where sal>(select min(sal) from emp where deptno=30);   // 단일행

>> select ename, sal from emp where sal > ANY(select sal from emp where deptno =30);  // 다중행 

 

 

Q ) 이름에 'T'를 포함하고 있는 사원들과 같은 부서에서 근무하고 있는 사원들의 이름과 사원 번호를 출력하시오 

 

>> select ename, empno, deptno from emp where deptno in (select deptno from emp where ename like '%T%');

>> select ename, empno, deptno from emp where deptno
IN(select deptno from emp where  INSTR(ename,'T')<>0) ; 
 (INSTR(ename,'T') <> 0   : 0이아니라면 즉 이름안에 T가 존재한다면 deptno 출력 )

 

(해설)

이름, 사원번호, 부서번호 를 emp에서 가져온다

where  사원번호가 이름에 T가들어간사람들의 사원번호들을 뽑아와서 

in 그중에 같은 사람이 있다면 사원번호를 가져옴. 

 

 

 

 

 

 

 

 

Q ) 급여가 평균급여보다 많고 이름이 'S'가 들어가는 사원과  / 동일한 부서에서 근무하는 모든 사원의 이름, 급여를 출력하시오  (사원S의 급여가 평균보다 많은것임)

 

>> select ename, sal, deptno from emp
where deptno in (select deptno from emp where sal >(select avg(sal) from emp ) and ename like '%S%') ;

 

 

Q ) 영업 사원들 보다 급여를 많이 받는 사원들의 이름과 직급, 급여를 출력하시오 

  (단 영업 사원들은 제외하시오/ 최댓값 개념)

 

(다중행 서브쿼리)
>> select ename, job, sal  from emp where sal > all(select sal from emp where job='SALESMAN') ;
(이미 모든 세일즈맨의 월급보다 크다 라고 주었기 때문에 세일즈맨은 제외 됨 따로 job<>'SALESMAN' 안해주어도 됨)

(단일행 서브쿼리)
>> select ename, job, sal  from emp where sal > (select max(sal) from emp where job ='SALESMAN');

 

 

 

 

Q ) 10번과 20 번 부서에서 근무하는 사원들의 평균 급여 보다 많이 받는 사원들의 이름, 급여, 부서번호를 출력하시오

 

>> select ename, sal, deptno from emp where sal>(select avg(sal) from emp where deptno in (10,20));

>> select ename, sal, deptno from emp where sal>(select avg(sal) from emp where deptno <> 30);

>> select ename, sal, deptno from emp where ename in
(select ename from emp where sal>(select avg(sal) from emp where deptno in (10,20)));

 

 

 

 

Q ) 'WARD'와 동일한 직급을 가진 사원의 이름과 직급을 출력하시오(단일행 서브 쿼리 이용)

>> select ename, job from emp where job = (select job from emp where ename='WARD');

 

 

 

Q ) 부서별로 그룹화 하여 가장 큰 월급을 받는 사람의 이름, 부서번호 출력 

(다중 컬럼 서브쿼리)

>>select * from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

 

 

 

 

 

exists

 

: 중복이 없다는 가정하에 COST(검색 단가)를 줄이기 위해 조건을 충족하는 값을 찾았을때 바로 검색을 중지하는 함수

 

 

(예) select * from emp where exists(select ename from emp where ename ='WARD');

 

 

 

 

Q ) 직급이 'MANAGER'인 사원들이 받는 급여의 최소 급여보다 많이 받는 사원들의 이름과 , 급여 부서번호를 출력하되

부서번호가 20번인 사원들은 제외한다. 

 

(단일행 쿼리)
>> select ename, sal, deptno from emp where sal>(select min(sal) from emp where job='MANAGER') and deptno<>20;

(다중행 쿼리)
>> select ename, sal, deptno from emp where sal>any(select sal from emp where job='MANAGER') and deptno<>20;

 

ROWNUM 

 

: sub Query에서 반환대는 각 행에 1부터 시작하는 순차 값을 할당함.

 

(예) select rownum, ename, sal from emp;

 

 

Q )  emp 테이블에서 최상위 소득자 3명의 이름과 급여를 표시하시오 

>> select rownum as RANK ,ename,sal from (select ename ,sal from emp order by sal desc) where rownum <=3;

 

★

ROWNUM 함수를 ORDER BY 함수와 함께 사용하면 순번이 뒤죽박죽이 된다. 
그 이유는 먼저 순번을 매기고 난 후 ORDER BY 정렬을 하기 때문이다. 
그러므로 ORDER BY 를 먼저 수행시킨 후 ROWNUM을 실행 시킬 수 있도록 코드를 작성해 주어야 한다. 

(예)

>>select rownum, kbs.ename ,kbs.sal from (select* from emp order by sal desc) kbs;

 

 

Q ) 'SALES' 부서의 모든 사원의 이름과 급여를 출력하되 월급이 높은 사람이 먼저나오고 거기에 순번을 붙이시오

select ROWNUM as RANK, ename,sal
from (select* from emp order by sal desc)
where deptno=(select deptno from dept where dname='SALES');