[SQL학원_SQL교육_SQL강좌]5. 서브 쿼리(sub query) 5.1 서브 쿼리(sub query) 개요 5.2 단일행…

5. 서브 쿼리(SUB QUERY)

5.1 서브 쿼리(SUB QUERY) 개요

 

서브 쿼리는 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에 의해 그룹화 되는 그룹에 조건을 줄 때 사용하는 것으로 반드시 HAVINGGROUP 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

 

 

5.2 복수행 서브쿼리(Multi-Row Sub Query)

 

서브 쿼리에서 여러 건의 결과가 반환되는 구조 이다. 이와 같은 구조에서 사용되는 연산자는 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;

롤백이 완료되었습니다.

Comments