[Oracle] 💾 서브쿼리

서브쿼리

하나의 쿼리 문장 내에 포함된 또 하나의 쿼리 문장을 말한다. 비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호 안에 넣어야 한다. 메인 쿼리가 실행되기 전에 한 번 실행된다.

서브쿼리의 종류

• select절 서브 쿼리 : Scalar Subqueries, 스칼라 서브쿼리
• from절 서브 쿼리 : Inline-View Subqueries, 인라인 뷰 서브쿼리
• where절 서브 쿼리 : Nested Subqueries, 중첩 서브쿼리

select절 서브쿼리 (스칼라 서브쿼리)

-- 'ALLEN'의 부서명을 구하시오.
select ename, (select d.dname from dept d where d.deptno=emp.deptno) as dept_name from emp where ename='ALLEN';

select절 서브쿼리의 결과는 메인 쿼리에서 select할 수 있도록 반드시 단일 행이나 단일 값으로 리턴되어야 한다.

from절 서브쿼리 (인라인 뷰 서브쿼리)

from절 서브쿼리의 결과는 반드시 하나의 테이블로 리턴되어야 한다. 인라인 뷰를 사용하는 쿼리로는 TOP N 쿼리, 페이징 쿼리 등이 있다.

TOP-N 쿼리

상위 N개의 데이터를 출력하는 쿼리이다.

-- 최고 급여 3명
select * from (select * from emp order by sal desc) where rownum<=3;

서브쿼리를 쓰지 않고 where절과 order by절을 쓰게 되면, where절이 먼저 실행되어 order by와 상관없이 랜덤으로 정렬된 3명이 출력된다. 따라서 서브쿼리를 이용해 order by로 정렬을 먼저 실행해 주는 것이다.

페이징 쿼리

TOP-N 쿼리를 이용하지만 조금 더 복잡하다. 1-5 페이지는 TOP-N 쿼리로 쉽게 구현할 수 있지만 6-10 페이지부터는 rownum의 특성상(1이 있어야 2가 있고, 2가 있어야 3이 있다.) rownum<=10을 먼저 실행하고, rownum>=6을 실행해야 한다.

select * from 
(select rownum as rnum, new_desc.* 
from (select * from emp order by empno desc) new_desc 
where rownum<=10) 
where rnum>=6;

rownum은 select문마다 따로 부여되기 때문에 서브쿼리의 rownum을 저장해 주고 메인 쿼리에서 저장한 rownum을 써야 한다

작성 순서

1. 테이블의 정렬을 먼저 하는 select문을 작성한다. 그리고 그 select문에 별칭을 붙여 준다. (뒤에서 이용해야 함)
2. ≤ 범위를 걸어 주는 select문을 작성한다. 1의 select문에 별칭을 걸어 주었기 때문에 별칭.*으로 내용을 뽑아 올 수 있다. 그리고 이 select문의 rownum도 출력하면서 별칭을 붙여 준다. (뒤에서 써야 함)
3. ≥ 범위를 걸어 주는 select문을 작성한다. 이때 2의 rownum에 별칭을 걸어 주었기 때문에 2번 select문의 rownum을 이용할 수 있다.

where절 서브 쿼리 (중첩 서브쿼리)

가장 대중적인 서브쿼리이다. 단일 행, 복수 행 모두 리턴이 가능하다.

-- 최고 급여자의 job
select job from emp where sal=(select max(sal) from emp);

-- 7369번 사원의 부서명 (join 금지)
select dname from dept where deptno=(select deptno from emp where empno=7369);

-- 평균 급여 이상의 급여를 받는 사원 명단
select ename from emp where sal>=(select avg(sal) from emp);

-- 'ALLEN'과 같은 부서에 근무하는 사원 출력
select ename from emp where deptno=(select deptno from emp where ename='ALLEN') and ename!='ALLEN';
-- and ename!='ALLEN'은 ALLEN을 출력에서 제외하는 식이다.

-- SMITH와 같은 job을 가진 사원 출력
select ename from emp where job=(select job from emp where ename='SMITH');