서브 쿼리는 SELECT한 결과를 조건 비교시 사용하거나 UPDATE, INSERT등에 사용되는 내장된 SELECT 문장이며 메인 쿼리 이전에 한번만 실행 된다. 테이블 자체의 데이터에 의존하는 조건으로 테이블의 행을 검색할 필요가 있을 때 서브쿼리는 아주 유용하게 이용될 수 있다.
EMP 테이블에서 SMITH의 급여보다 급여가 많은 사람을 추출하는 경우
괄호로 싸인 부분이 서브 쿼리 인데 Inner Query or Sub Query 라고 하며 Inner Query의 결과를 비교 조건으로 사용하는 외부에 있는 것을 Main Query or Outer Query 라고 한다. 서브쿼리(Sub Query)는 메인 쿼리 실행 전에 한번씩 실행되며 그 결과가 메인 쿼리(Main Query)에 전달된다.
[서브 쿼리 지침]
n 서브 쿼리는 괄호로 싸야 한다.
n 단일 행 및 복수 행 서브 쿼리는 연산자의 우측에 나타나야 한다.
n 서브 쿼리에는 ORDER BY 절을 포함 할 수 없다.
5.2 단일행 서브쿼리(Single-Row Sub Query)
서브 쿼리에서 하나의 결과가 반환되는 구조이며 이와 같은 구조에서 사용되는 연산자는 단일 행 연산자( > , >= , < , <= , = , <>) 이다.
--EMP 테이블에서 “SMITH”와 같은 JOB을 가지는 사원들의 ENAME, SAL, JOB을 추출하려 한다고 하자. 만약 SMITH 사원의 JOB 이 “CLERK” 이라는 것을 알고 있다면 다음과 같이 쉽게 할 수 있을 것이다. 그러나 아래와 같은 경우 “SMITH” 사원의 JOB이 바뀌게 되면 어떻게 할 것인가? “SMITH”의 JOB을 바뀔 때 마다 기억을 한다는 것은 어려운 일다. 그렇다고 질의(Query) 문 을 아래와 같이 매번 두 번 만드는 것도 번거러운 일 이다.
SQL> select job from emp
2 where ename = 'SMITH';
JOB
---------
CLERK
SQL> select ename, sal, job from emp
2 where job = 'CLERK';
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
ADAMS 1100 CLERK
JAMES 950 CLERK
MILLER 1300 CLERK
-- 위의 두 예문을 합친 단일 행 서브 쿼리 예문이다.
-- 서브 쿼리는 아래와 같이 테이블 자체의 데이터에 의존하는 비교 조건으로 데이터를 검색 할 때 유용 하다.
SQL> select ename, sal, job from emp
2 where job = (select job from emp
3 where ename = 'SMITH');
ENAME SAL JOB
---------- ---------- ---------
SMITH 800 CLERK
ADAMS 1100 CLERK
JAMES 950 CLERK
MILLER 1300 CLERK
--아래의 예문은 EMP 테이블에서 급여가 가장 적은 사원의 이름과 급여를 출력 하는 예문이다.
SQL> select ename, sal from emp
2 where sal = (select min(sal) from emp);
ENAME SAL
---------- ----------
SMITH 800
-- EMP 테이블에서 부서코드가 30번인 부서의 급여 최소값보다 해당 부서 급여의 최소값이 큰 부서만 출력하되 부서 순으로 오름차순으로 정렬 하시오. 이 예문에서 기억 해야 하는 사실은 첫째 서브 쿼리는 WHERE절 뿐 아니라 HAVING절에서도 사용 가능하며 둘째 WHERE절이 각 행에 조건을 줘서 선택되는 행을 제어 하듯이 HAVING절은 GROUP BY에 의해 그룹화 되는 그룹에 조건을 줄 때 사용하는 것으로 반드시 HAVING은 GROUP BY 뒤에 와야 하며, 셋째 HAVING이 사용되면 대부분 GROUP BY가 있지만 GROUP BY 없는 HAVING의 사용도 가능 하다. 넷째 ORDER BY절은 SELECT문의 마지막에 오며, 다섯째 SELECT절에 그룹 함수 외의 컬럼이 나타나면 반드시 GROUP BY절에 해당 컬럼이 나타나야 한다.
SQL> select deptno, min(sal) from emp
2 group by deptno
3 having min(sal) > (select min(sal) from emp
4 where deptno = 30)
5 order by deptno;
DEPTNO MIN(SAL)
---------- ----------
10 1300
--GROUP BY 없이 사용되는 HAVING 예문(테이블 전체를 하나의 그룹으로 간주)
--EMP 테이블의 SAL의 최대값은 5000 이다. 급여 평균은 2073.21429인 상태
SQL> select max(sal) from emp;
MAX(SAL)
----------
5000
SQL> select avg(sal) from emp;
AVG(SAL)
----------
2073.21429
--SAL의 최대값을 구하는데 SAL의 평균이 2000보다 크다고 했으므로 현재의 하나 밖에 없는 그룹(테이블 전체)에는 SAL의 평균이 2073이므로 MAX(SAL)은 5000이 된다.
SQL> select max(sal) from emp
2 having avg(sal) > 2000;
MAX(SAL)
----------
5000
--EMP Table에는 여러 종류의 직무(JOB)가 있다. 다음 예문은 각 JOB의 평균 급여가 최대인 JOB과 그 평균 급여를 출력 하는 예문이다.
SQL> select job, avg(sal) from emp
2 group by job
3 having avg(sal) = (select max(avg(sal)) from emp
4 group by job);
JOB AVG(SAL)
--------- ----------
PRESIDENT 5000
--부서코드가 10인 사원들 중 최대/최소 급여를 받는 사원의 이름, 급여, 부서를 출력
SQL> select ename, sal, deptno from emp
2 where deptno = 10
3 and sal in ( (select max(sal) from emp where deptno = 10),
4 (select min(sal) from emp where deptno = 10)
5 );
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
MILLER 1300 10
--SMITH와 같은 JOB, 같은 부서를 가지는 사원의 이름, 직무, 부서를 출력하는데 SMITH는 출력하지 마시오
SQL> select ename, job, deptno from emp
2 where job = (select job from emp where ename = 'SMITH')
3 and deptno = (select deptno from emp where ename = 'SMITH')
4 and ename != 'SMITH';
ENAME JOB DEPTNO
---------- --------- ----------
ADAMS CLERK 20
서브 쿼리에서 여러 건의 결과가 반환되는 구조 이다. 이와 같은 구조에서 사용되는 연산자는 IN, ANY, SOME(ANY와 동일), ALL, EXISTS 등과 같은 복수 행 연산자 이다. 이 연산자들은 이전의 SQL 연산자 부분에 자세히 나와 있으니 참조하길 바라며 아래의 예문을 따라 하면서 이해해 보자.
--EMP 테이블에서 각 부서별로 급여를 가장 적게 받는 사원의 부서, 이름, 급여를 출력 하는 예문이다. IN 연산자 오른쪽 서브 쿼리에서 리턴되는 행(ROW)은 여러 건이다. 아마도 부서별로 가장 적은 급여가 추출되어 Outer Query와 IN 연산자에 의해 비교되는 것이다. 결국 부서별로 급여를 가장 적게 받는 사원을 선택 하게 되는데 IN연산자는 OR로 풀어 쓸 수 있음을 기억 하자.
SQL> select deptno, ename, sal from emp
2 where (deptno, sal) in (select deptno, min(sal) from emp
3 group by deptno);
DEPTNO ENAME SAL
------ ---------- ----------
30 JAMES 950
20 SMITH 800
10 MILLER 1300
-- 위와 같은 WHERE 비교를 PAIRWISE방식이라 한다. 즉 칼럼을 쌍으로 묶어서 비교하는 것이다. 만약 아래처럼 쿼리문을 작성한다면 예상치 못한 결과가 나올 수도 있다. 현재 EMP 테이블은 다행히도 10번 부서의 최소급여인 1300을 다른 부서사원들이 가지고 있지 않아서 PAREWISE 방식으로 안 하더라도 결과는 같이 나온다. 만약 20번 부서 사원이 1300을 가지고 있다면 그 사원도 출력될 것이다.
select deptno, ename, sal from emp
where sal in (select min(sal) from emp
group by deptno)
and deptno in (select distinct deptno from emp)
--간단히 ANY의 개념에 대해 이해하자. 여러 값 중 하나하고만 조건을 만족시키면 되므로 OR로 플어쓸 수 있다.
SQL> SELECT empno, sal
2 FROM emp
3 WHERE sal > ANY (2000, 3000, 4000);
EMPNO SAL
---------- ----------
7782 2450
7698 2850
7566 2975
7788 3000
7902 3000
7839 5000
6 개의 행이 선택되었습니다.
SQL> SELECT empno, sal
2 FROM emp
3 WHERE sal > 2000 OR sal > 3000 OR sal > 4000;
EMPNO SAL
---------- ----------
7782 2450
7698 2850
7566 2975
7788 3000
7902 3000
7839 5000
6 개의 행이 선택되었습니다.
--아래 예문은 ANY를 이용한 서브 쿼리 예문이다. EMP 테이블에서 부서코드가 10번인 사원 급여의 임의 값보다 큰 급여를 가지는 사원의 이름, 급여를 출력하는 하는 것이다. 서브 쿼리에서 괄호안을 풀면 두번째 줄은 where sal > any (2450, 5000, 1300)의 의미가 되고 2450, 5000, 1300을 대입하여 Outer Query를 수행 했을 때 하나라도 만족하면 되는 것이므로, where sal > 1300의 의미와 같은 것이다. 만약 ANY를 사용하고 등호를 사용 했다면 IN의 의미와 같다는 것도 기억 하자. 즉 sal = any (2450, 5000, 1300)과 sal in (2450, 5000, 1300)과 같은 의미이다.
SQL> select sal from emp
2 where deptno = 10;
SAL
----------
2450
5000
1300
SQL> select ename, sal, job from emp
2 where sal > any (select sal from emp
3 where deptno = 10);
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
JONES 2975 MANAGER
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
SCOTT 3000 ANALYST
KING 5000 PRESIDENT
TURNER 1500 SALESMAN
FORD 3000 ANALYST
8 개의 행이 선택되었습니다.
--아래 예문은 sal = any (SELECT절) 과 sal in (SELECT절)과 같다는 것을 보이는 예문이다.
SQL> select ename, sal, job from emp
2 where sal = any (select sal from emp
3 where deptno = 10);
ENAME SAL JOB
---------- ---------- ---------
MILLER 1300 CLERK
CLARK 2450 MANAGER
KING 5000 PRESIDENT
SQL> select ename, sal, job from emp
2 where sal in (select sal from emp
3 where deptno = 10);
ENAME SAL JOB
---------- ---------- ---------
MILLER 1300 CLERK
CLARK 2450 MANAGER
KING 5000 PRESIDENT
-- 아래 ALL 예문을 보면 ALL의 의미에 대해 이해가 될 것이다. 모든 값을 만족하려면 결국 AND로 모든 것을 비교하는 것이다.
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal > ALL (2000, 3000, 4000);
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
SQL> SELECT empno, ename, sal
2 FROM emp
3 WHERE sal > 2000 AND sal > 3000 AND sal > 4000;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
--아래 예문도 ALL을 사용한 것이다. ALL은 서브 쿼리에서 리턴되는 데이터들 모두가 Outer Query에서 조건을 만족시켜야 하는 것이다. 즉 sal > all (SELECT절)의 경우 SELECT절에서 추출되는 자료의 최대값보다 많다는 의미이고, sal < all (SELECT절)의 경우 SELECT절에서 추출되는 자료의 최소값보다 적다는 의미이다.
SQL> select ename, sal, job from emp
2 where sal > all (select sal from emp
3 where deptno = 20);
ENAME SAL JOB
---------- ---------- ---------------
KING 5000 PRESIDENT
-- ALL을 ANY를 이용하여 변경했다.
SQL> select ename, sal, job from emp
2 where not (sal <= any ( select sal from emp
3 where deptno = 20));
ENAME SAL JOB
---------- ---------- ---------------
KING 5000 PRESIDENT
SQL> select ename, sal, job from emp
2 where sal < all (select sal from emp
3 where deptno = 20);
선택된 레코드가 없습니다.
-- EXISTS실습을 위해 테이블을 하나 만들자.(EMP 테이블에서 10번 부서 사원들로 EMP_10 생성)
SQL> create table emp_10 as select * from emp where deptno = 10;
테이블이 생성되었습니다.
-- 아래는 EMP 테이블 사원 중 EMP_10에 존재하는 사원들을 추출하는 예문이다.
SQL> SELECT empno, ename, sal
2 FROM emp e
3 WHERE EXISTS (SELECT 1 FROM emp_10 WHERE empno = e.empno);
EMPNO ENAME SAL
---------- ---------- ----------
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
-- 아래는 UPDATE문에서 EXISTS를 사용하는 예문이다.
-- EMP 테이블에서 입사년도가 1980년인 사원이 존재하면 수당을 0으로 UPDATE
SQL> UPDATE emp e
2 SET comm = 0
3 WHERE EXISTS (SELECT 1
4 FROM emp
5 WHERE EMPNO = e.empno
6 AND to_char(hiredate,'YYYY') = '1980' );
1 행이 갱신되었습니다.
SQL> rollback;
롤백이 완료되었습니다.
댓글 없음:
댓글 쓰기