문법 :
SELECT select_list
FROM
table 또는 view
WHERE
조건 연산자 ( SELECT select_list
FROM table
WHERE 조건 ) ;
sub query 는 메인 쿼리 안에 포함되어 있는 또다른 별도 Select 문이다.
여러번의 Select문을 수행해야 얻을 수 있는 결과를 하나의 중첩된 Select 문으로 쉽게 얻을 수 있다.
sub query 가 먼저 실행되고, 그 결과가 메인 쿼리에 전달되어 실행된다.
두개의 테이블을 sub query 로 조인할 때에는 반드시 일치되는 공통 칼럼을 사용해야 한다.
- sub query 는 Select문의 select 절, FROM 절, Where절, Having 절에 올 수 있다.
. select 절에 오는 sub query 는 스칼라 서브쿼리,
. FROM 절에 오는 서브쿼리는 inline view 서브쿼리
- sub query 는 SELECT 문만 아니라 다른 문(INSERT, DELETE, UPDATE) 에도 쓸 수 있다
- sub query 에는 반드시 괄호를 사용한다.
- operator(연산자) 는 단일행 연산자 ( > , = , >= , < , <= , != )와
복수행 연산자 ( IN , ANY ,ALL,EXISTS )를 사용 할 수 있다.
- 단일행 sub query : sub query 의 결과가 1개의 행만 나오며, 반드시 단일행 연산자를 사용해야 한다.
- 복수행 sub query : sub query 가 여러 개의 행을 리턴하며, 반드시 복수행 연산자를 사용해야 한다.
- sub query 에는 order by 를 사용할 수 없다.
- sub query 에는 NULL 값이 있으면 결과가 출력되지 않는다.
예제
단일행 sub query
-- 1. EMP 테이블에서 'SCOTT' 보다 급여를 많이 받는 사람의 이름과 급여를 출력
SQL> select ename, sal
from emp
where sal > (select sal from emp where ename = 'SCOTT');
-- 2. 사원테이블에서 BLAKE 보다 급여가 많은 사원들의 사번, 이름, 급여 출력
SQL> select empno, ename, sal
from emp
where sal > (select sal from emp where ename ='BLAKE');
-- 3. 사원테이블에서 MILLER 보다 늦게 입사한 사원의 사번, 이름, 입사일을 검색
SQL> select empno, ename, hiredate
from emp
where hiredate > (select hiredate from emp where ename = 'MILLER');
select hiredate from emp where ename = 'MILLER';
-- 4. emp 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934 보다 많은 사원의 사원번호, 이름, 담당업무, 입사일자, 급여를 출력
SQL> select empno, ename, job, hiredate, sal
from emp
where job = (select job from emp where empno=7521)
and sal > (select sal from emp where empno = 7934);
-- 5. 서브쿼리에서 그룹함수 사용
select empno, ename, job, sal, deptno
from emp
where sal < (select avg(sal) from emp);
-- 6. 사원테이블에서 사원 전체 평균 급여보다 급여가 많은 사원들의 사번, 이름, 급여를 검색
SQL> select empno, ename, sal
from emp
where sal > (select avg(sal) from emp);
-- 7. emp 테이블에서 20번 부서의 최소급여보다 많은 모든 부서의 최소급여 출력
-- having 절에서의 서브쿼리
SQL> select deptno, min(sal)
from emp
group by deptno
having min(sal) > (select min(sal) from emp where deptno = 20);
복수행 sub query
- 복수행 서브쿼리 : IN, ALL, ANY
- 복수행 서브쿼리란 sub query 의 결과가 2건 이상 출력되는 것을 말한다.
- 메인 쿼리의 비교 조건이 sub query 의 결과 중에서 하나라도 일치하면 검색 가능
-- 1. emp 테이블에서 업무별로 최소 급여를 받는 사원의 사원번호, 이름, 업무, 입사일자, 급여, 부서번호 출력
SQL> select empno, ename, job, hiredate, sal, deptno
from emp
where sal IN (select min(sal) from emp group by job)
order by 3;
-- 2. 사원테이블에서 업무가 MANAGER인 사원의 최소급여보다 적은 급여를 받는 사원들의 이름 검색
SQL> select empno, ename, job, sal, deptno
from emp
where sal < ALL (select sal from emp where job = 'MANAGER');
-- ALL 을 사용한 것과 min(sal)을 사용한 것이 결과는 동일하다.
SQL> select empno, ename, job, sal, deptno
from emp
where sal < (select min(sal) from emp where job = 'MANAGER');
-- 3. ANY : 최소값보다 큰, ALL : 최소값보다 작은
SQL> select empno, ename, job, sal, deptno
from emp
where sal > ANY (select sal from emp where job = 'MANAGER');
SQL> select empno, ename, job, sal, deptno
from emp
where sal > (select min(sal) from emp where job = 'MANAGER');
-- 4. EXISTS 연산자 : sub query 에서 검색된 결과가 하나라도 존재하면 메인 쿼리 조건이 참이 되는 연산
-- 사원중에서 comm 을 받은 사원이 한 명이라도 있으면 모든 사원 출력
SQL> select *
from emp
where exists (select empno from emp where comm IS NOT NULL);
다중컬럼 서브쿼리 : pairwise (컬럼을 쌍으로 묶어서 동시에 비교하는 방식)
-- sub query 에서 여러 개의 컬럼 값을 검색하여 메인 쿼리의 조건절과 비교하는 sub query
--5-1. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력
-- 부서별 : group by 를 사용해야 한다는 의미, 최대 급여 : max(sal)
SQL> select deptno, empno, ename, sal
from emp
where (deptno, sal) IN (select deptno, max(sal) from emp group by deptno);
다중컬럼 서브쿼리 : unpairwise (칼럼별로 나누어 비교하고 나중에 AND 연산 처리하는 방식)
--5-2. 사원테이블에서 부서별로 가장 많은 급여를 받는 사번, 이름, 이메일, 급여 출력
SQL> select deptno, empno, ename, sal
from emp
where deptno in (select deptno from emp group by deptno)
and sal in (select max(sal) from emp group by deptno);
-- 6. 사원테이블에서 부서별 최대 급여를 받는 사원들의 사번, 이름, 부서코드, 급여를 검색
-- 오류가 생길 수 있는 쿼리문
SQL> select empno, ename, deptno, sal
from emp
where sal in (select max(sal) from emp group by deptno);
-- 정확한 쿼리문
select empno, ename, deptno, sal
from emp
where (deptno, sal) in (select deptno, max(sal) from emp group by deptno);
