7.1 SubQuery

---

※ EMP, DEPT, SALGRADE 테이블 구조

 

---

※ Subquery

single row subquery	서브쿼리에서 메인쿼리로 하나의 값이 리턴되는 경우
multiple row subquery	서브쿼리에서 메인쿼리로 여러 개의 값이 리턴되는 경우
multiple column subquery	서브쿼리에서 메인쿼리로 여러 개의 컬럼값들이 리턴되는 경우

구문	subquery의 이름
select	scalar subquery
from	in line view
where	subquery
group by	<불가능>
having	subquery
order by	scalar subquery

 

1. single row subquery

# 문제 111. DALLAS에서 근무하는 사원들의 이름과 월급을 출력하시오. (JOIN 쓰지 마시오!!)   select ename, sal   from emp   where deptno = ( select deptno from dept where loc = 'DALLAS' )

 

2. multiple row subquery

# 문제 112. 관리자가 아닌 사원들의 이름을 출력하시오.   select ename   from emp   where empno not in (select mgr from emp);   select ename   from emp   where empno not in (select e.empno from emp e, emp ee where e.empno = ee.mgr);   select ename   from emp   where empno not in (select nvl(mgr,0) from emp);

- 첫번째 쿼리는 mgr 값이 null이 있기 때문에 에러 따라서 nvl 처리를 해주거나 subquery 안에서 그것을 해결해 주거나...

# 문제 113. 직업이 SALESMAN인 사원들의 최대월급보다 많이 받는 사원들의 이름과 월급을 출력하시오.   select ename, sal   from emp   where sal > ( select max(sal) from emp where job = 'SALESMAN' );   select ename, sal   from emp   where sal > all(select sal from emp where job = 'SALESMAN');

- 첫번째 쿼리의 속도가 더 빠르다. 두번째는 서브쿼리 안에 sal의 값들을 여러개 주고 그 값들을 전부 비교해가면서 찾기 때문이다.

# 문제 114. 부서번호가 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);

# 문제 115. DALLAS에서 월급이 2등인 사원과 같은 월급을 받는 사원의 이름과 월급을 출력하시오.   select ename, sal   from emp   where sal = (     select sal         from(           select e.sal,dense_rank() over (partition by d.loc order by sal desc) 랭크             from emp e, dept d             where e.deptno = d.deptno and d.loc = 'DALLAS'             )         where 랭크 = 2 );

- 더 좋은 방법 있으시면 댓글 달아주세요...

 

3. multiple column subquery

# Non pair wise VS pair wise   select ename, sal, comm, job   from emp   where sal in (select sal from emp where job = 'SALESMAN')     and nvl(comm,-1) in (select nvl(comm,-1) from emp where job = 'SALESMAN');    select ename, sal, comm, job   from emp   where (sal, nvl(comm,-1) ) in (select sal,nvl(comm,-1) from emp where job = 'SALESMAN');

- 여기서 결과가 동일하다. 하지만 values(KING,1250,300,'PRESIDENT')가 있다면 non-pair에서는 뽑히고 pair에서는 뽑히지 않는다. 그 이유는 non-pair의 경우에는 4X4의 sal과 comm의 경우의 수를 모두 포함할 수 있다. 반면에 pair의 경우에는 [[1250,500],[1250,1400],[1600,300],[1500,0]] 딱 이 4가지 경우의 수만들 체크한다.