분석함수 III (서브 쿼리 응용,EXISTS)

SELECT *
FROM EMP;

위의 EMP을 이용하여 서브쿼리를 이용을 연습하여 보자. 서브쿼리는 복잡한 쿼리문을 간단하게 바꿀 수 있어 자주 사용되므로 충분히 익숙하게 만들어야 한다. 위의 테이블에서 부서번호별 급여의 합의 평균보다 큰 부서 번호와 그 부서번호의 급여의 합을 추출해보자.

 SELECT DEPTNO, SUM(SAL)
 FROM EMP
 GROUP BY DEPTNO;

부서별 급여의 합은 위와 같다. 그리고 그 평균은 다음과 같다.

 SELECT AVG(SUM(SAL))
 FROM EMP
 GROUP BY DEPTNO;

ORACLE은 1회에 한하여 그룹함수 속에 그룹함수를 포함 할 수 있다. 하지만 MS-SQL SERVER는 불가능하다. 위의 결과를 서브쿼리로 이용하여 다음과 같이 처리할 수 있다.

 SELECT DEPTNO, SUM(SAL)
 FROM EMP
 GROUP BY DEPTNO
 HAVING SUM(SAL) > (SELECT AVG(SUM(SAL))
                             FROM EMP
                             WHERE DEPTNO IS NOT NULL
                             GROUP BY DEPTNO);

위와 같이 나온다. 그룹 함수의 조건은 HAVING 절에서 사용해야 함을 잊지 말자.
MS-SQL에서는 불가능하므로 MS-SQL에서 사용이 가능하도록 바꾼다면 다음과 같다.

SELECT DEPTNO,SUM(SAL)
 FROM EMP
 WHERE DEPTNO IS NOT NULL
 GROUP BY DEPTNO
 HAVING SUM(SAL) >
   ( SELECT  AVG(T.AA)"각부서별 급여합의 평균"
   FROM
     ( SELECT  SUM(SAL) AA
       FROM EMP
       WHERE DEPTNO IS NOT NULL
   GROUP BY  DEPTNO )T
 ) ;

  간편하게 처리하기 위해서 VIEW를 생성해도 된다. 

  CREATE OR REPLACE VIEW VIEW_TOTSAL
  AS
  SELECT DEPTNO, SUM(SAL) TOTSAL
  FROM EMP
  WHERE DEPTNO IS NOT NULL
  GROUP BY DEPTNO;

  SELECT DEPTNO "부서번호", TOTSAL"급여의합"
  FROM VIEW_TOTSAL
  WHERE TOTSAL>(SELECT AVG(TOTSAL) FROM VIEW_TOTSAL);

그렇다면 부서번호가 NULL이 아닌 부서중 급여평균이 최저인 부서번호를 구해보자.

SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO
HAVING SUM(SAL) = (SELECT MIN(SUM(SAL))
                                      FROM EMP
                                      WHERE DEPTNO IS NOT NULL
                                      GROUP BY DEPTNO);

SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE DEPTNO IS NOT NULL
GROUP BY DEPTNO
HAVING SUM(SAL) <= ALL(SELECT SUM(SAL)
                                      FROM EMP
                                      WHERE DEPTNO IS NOT NULL
                                      GROUP BY DEPTNO);

EXISTS : 식의 연산하여 행이 존재하면 참, 행이 존재 하지 않으면 거짓을 반환한다. 

 SELECT *  FROM EMP
 WHERE 1 = 2;                 -- 거짓

 SELECT *  FROM EMP
 WHERE 2 = 2;                 -- 참

 SELECT *  FROM EMP
 WHERE EXISTS( SELECT * FROM DEPT
                           WHERE DEPTNO = 10);    -- 참

  SELECT *   FROM EMP
  WHERE NOT EXISTS( SELECT * FROM DEPT
                           WHERE DEPTNO = 50);     -- 거짓

위의 결과는 참인 경우에는 EMP테이블이 보이게 되고 아닐 경우에는 WHERE 조건에 따라 정리되어 보이지 않는다.
EMP 테이블에서 근무하고 있는 부서의 위치가 "NEW YORK"과 "DALLAS"인 사원의 사원번호, 사원명, 급여를 추출 하여보자.

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN
(  SELECT DEPTNO
   FROM DEPT
   WHERE LOC = 'NEW YORK' OR
               LOC = 'DALLAS');

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO IN
   (SELECT DEPTNO
   FROM DEPT
   WHERE LOC IN('NEW YORK', 'DALLAS')  );

위의 두 쿼리문는 같은 결과를 가져오는 쿼리문이다. 사용하기엔 IN이 직관적이고 쉽지만 속도는 OR가 빠르다. IN을 쓰면 내부적으로 OR로 변환시켜서 사용하기 때문에 실제상 한번 더 연산을 하게 된다.
서브쿼리에 사용되어지는 IN 연산자 대신에 EXISTS 연산자를 사용하여 나타낼 수 있다. 서브쿼리에서는 IN 연산자 보다 EXISTS 연산자를 사용하는 것이 속도가 조금 떠 빠르다고 하기 때문이다.

SELECT EMPNO, ENAME,SAL
FROM EMP E
WHERE EXISTS(SELECT *  FROM DEPT
                            WHERE LOC IN ('NEW YORK', 'DALLAS' )
                            AND DEPTNO = E.DEPTNO);                             );

서브쿼리에서의 WHERE문에 외부와 비교할 조건을 넣어야 한다. 이를 안 넣을 시에는 그냥 TRUE값을 받아버려 전체 테이블을 보여주게 된다.