과정 목표

본 과정에서는 SELECT 문장의 보다 진보된 특징에 대해서 배울 것입니다. 알려지지 않은 조건 값을 근거로 값을 구하기 위해 다른 SQL 문장의 WHERE 절에 서브쿼리를 작성합니다. 본 과정은 단일 행 서브쿼리와 다중 행 서브쿼리를 다룹니다.

 

문제를 해결하기 위한 서브쿼리 사용

Jones보다 많은 급여를 받는 종업원을 찾기 위한 질의를 작성하고자 합니다.
이 문제를 해결하기 위해서 두개의 질의가 필요합니다. 하나의 질의는 Jones의 급여를 알기 위한 것이고, 두번째 질의는 그것보다 많은 급여를 받는 종업원을 찾는 것입니다.
하나의 질의를 다른 질의 내부에 두는 방법으로 두개의 질의를 조합하여 이 문제를 해결할 수 있습니다.
내부 질의 또는 서브쿼리는 외부질의 또는 메인 쿼리(main query)에 의해 사용되는 값을 리턴합니다. 서브쿼리의 사용은 두개의 질의를 순차적으로 수행하여 두번째 질의의 검색 값으로 첫번째 질의의 결과를 사용하는 것입니다.

 

서브쿼리

서브쿼리는 다른 SELECT 문장의 절에 내장된 SELECT 문장입니다. 서브쿼리를 사용하여 간 단한 문장을 강력한 문장으로 만들 수 있습니다. 테이블 자체의 데이터에 의존하는 조건으로 테이블의 행을 검색할 필요가 있을 때 서브쿼리는 아주 유용합니다.

다음의 SQL 절에 서브쿼리를 작성할 수 있습니다.

구문형식에서:

        operator        >, = 또는 IN 같은 비교 연산자를 포함합니다.

주: 비교 연산자는 단일 행 연산자(>, =, >=, <, <>, <=)와 다중 행 연산자(IN, ANY, ALL)가 있습니다.

서브쿼리는 종종  중첩된 SELECT, 부속 SELECT 또는 내부 SELECT 문장으로 불리워집니다. 일반적으로 서브쿼리를 먼저 실행하고, 그것의 결과를 메인 쿼리 또는 외부 질의에 대한 질의 조건을 완성하는데 사용합니다

서브쿼리 사용

슬라이드에서 내부 질의는 종업원 7566의 급여를 결정합니다. 외부 질의는 내부 질의의 결과를 받으며, 이 급여보다 많이 받는 모든 종업원을 디스플레이하기 위해 이 결과를 사용합니다.

 

서브쿼리 사용 지침

 

단일 행 서브쿼리

단일 행 서브쿼리는내부 SELECT 문장으로부터 하나의 행을 리턴하는 질의입니다. 이런 유형의 서브쿼리는 단일 행 연산자를 사용합니다. 슬라이드는 단일 행 연산자의 목록을 보여줍니다.

예

업무가 종업원 7369과 똑같은 종업원을 디스플레이합니다.  

SQL> SELECT   ename, job
  2  FROM     emp
  3  WHERE    job =
  4                 (SELECT  job
  5                  FROM     emp
  6                  WHERE    empno = 7369);


ENAME      JOB
---------- ---------
JAMES      CLERK
SMITH      CLERK
ADAMS      CLERK
MILLER     CLERK

 

단일 행 서브쿼리 실행

위의 예는 업무가 종업원 7369의 업무와 같고, 급여가 종업원 7876보다 많은 종업원을 디스플레이합니다.

예는 세개의 질의 블록으로 구성됩니다: 한 개의 외부 질의와 두 개의 내부 질의. 내부 질의 블록들이 먼저 실행되고, 질의 결과 CLERK과 1100을 리턴합니다. 그런 다음에 외부 질의 블록이 처리되고 외부 질의의 검색 조건을 완성하기 위해 내부 질의에 의해 리턴된 값을 사용합니다.

내부 질의 두개는 단일 값(CLERK과 1100)을 리턴하므로 이 SQL 문장을 단일 행 서브쿼리 라고 부릅니다.

주: 외부 질의와 내부 질의는 다른 테이블로부터 데이터를 구할 수 있습니다.

 

서브쿼리를 가진 HAVING 절

서브쿼리를 WHERE 절 뿐만아니라 HAVING 절에서도 사용할 수 있습니다. 오라클 서버는 서브쿼리를 실행하고 그 결과를 메인 쿼리의 HAVING 절에 리턴합니다.
슬라이드의 SQL 문장은 부서 20보다 최소 급여가 많은 모든 부서를 디스플레이합니다.

   DEPTNO   MIN(SAL)
--------- ---------
       10      1300
       30       950


예

가장 적은 평균 급여를 찾습니다.

SQL> SELECT     job, AVG(sal)
  2  FROM       emp
  3  GROUP BY   job
  4  HAVING     AVG(sal) = (SELECT    MIN(AVG(sal))
  5                         FROM      EMP
  6                         GROUP BY  job);

 

서브쿼리 에러

서브쿼리의 일반적인 에러중의 하나는 단일 행 서브쿼리에 대해서 하나 이상의 행이 리턴되 는 것입니다.

위의 SQL 문장에서 GROUP BY 절(deptno)을 포함하는 서브쿼리는 각 그룹에 대해 다중 행을 리턴합니다. 이 경우 서브쿼리의 결과는 800, 1300 그리고 950이 될 것입니다.

외부 질의는 서브쿼리의 결과(800,950,1300)를 받으며 이러한 결과는 WHERE 절에서 사용 됩니다. WHERE 절은 오직 하나의 값만을 필요로 하는 단일 행 비교 연산자인 동등(=) 연산자를 포함합니다. = 연산자는 서브쿼리로부터 하나이상의 값을 받을 수 없으므로 에러를 발생시킵니다.

이 에러를 정정하려면 = 연산자를 IN으로 변경하면 됩니다.

 

다중 행 서브쿼리

하나 이상의 행을 리턴하는 서브쿼리를 다중 행 서브쿼리 라고 부릅니다. 다중 행 서브쿼리는 단일 행 연산자 대신에 다중 행 연산자를 사용합니다. 다중 행 연산자는 하나 이상의 값을 요구 합니다.

SQL> SELECT  ename, sal, deptno
  2  FROM    emp
  3  WHERE   sal IN (SELECT   MIN(sal)
  4                  FROM     emp
  5                  GROUP BY deptno);

예

부서에 속한 종업원의 최소 급여와 동일한 급여를 받는 종업원을 찾습니다. 결과적으로, 부서에서 최소 급여를 받는 종업원 정보에, 그 급여와 동일한 급여를 받는 다른 부서의 종업원의 정보를 가져올 것입니다.

내부 질의가 먼저 실행되어 세개의 결과 800, 950, 1300을 포함하는 질의 결과를 산출합니다. 그런 다음에 메인 쿼리 블록이 처리되고 검색 조건을 완성시키기 위해서 내부 질의에 의해 리 턴된 값을 사용합니다. 사실, 메인 쿼리는 오라클 서버에게는 다음처럼 보입니다.

SQL> SELECT   ename, sal, deptno
  2  FROM     emp
  3  WHERE    sal IN (800, 950, 1300);

 

다중 행 서브쿼리(계속)

ANY 연산자(그리고 그것의 synonym SOME 연산자)는 값을 서브쿼리에 의해 리턴된 각각의 값과 비교합니다. 위의 예는 급여가 어떤 clerk보다 작으면서 clerk이 아닌 종업원을 디스플레이 합니다. Clerk의 최대 급여는 $1300입니다. SQL 문장은 clerk이 아니고 $1300 이하를 버는 모든 종업원을 디스플레이합니다.

<ANY 는 최대값보다 작음을 의미합니다.  >ANY는 최소값보다 많음을 의미합니다.  =ANY는 IN과 같습니다.

 

다중 행 서브쿼리(계속)

ALL 연산자는 값을 서브쿼리에 의해 리턴된 모든 값과 비교합니다. 위의 예는 급여가 모든 부서의 평균보다 많은 종업원을 디스플레이합니다. 부서의 가장 많은 평균 급여는 $2916.66 이므로 질의는 $2916.66보다 많은 급여를 받는 종업원을 리턴합니다.

>ALL은 최대값보다 크다는 것을 의미하며 <ALL은 최소값보다 작다는 것을 의미합니다.

NOT 연산자는 IN, ANY 그리고 ALL 연산자에서도 사용할 수 있습니다.

 

  1. 다음 문장 중 문법에 틀린 것은?
    A. SELECT DEPTNO, NAME, SAL FROM EMP WHERE DEPT_ID=10, SALARY>=2000;
    B. SELECT DEPTNO 사번, SAL "월 급여" FROM EMP;
    C. SELECT EMPNO, SAL FROM EMP WHERE JOB = 'Sales Representitive';
    D. SELECT NAME, JOB, SAL FROM EMP WHERE SAL< (SELECT AVG(SAL) FROM EMP);
    답
    X 정답:A


  2. 다음 문장 중 문법에 맞는 것은?
    A. SELECT empno, ename FROM emp WHERE sal = ( SELECT avg(sal) FROM emp GROUP BY deptno);
    B. SELECT ename, job FROM emp WHERE job IN ( SELECT job FROM emp ORDER BY job);
    C. SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING (SELECT MIN(sal) FROM emp WHERE deptno=20) < MIN(sal);
    D. SELECT empno, ename, job FROM emp WHERE sal < ANY(SELECT sal FROM emp WHERE job='CLERK');
    답
    X 정답:D