7.3. Multiple-Row Subquery

Multiple-Row Subquery란?

- 하나 이상의 행을 반환하는 Subquery이다

- 단일 행 연산자를 사용하지 못하며, 다중 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)만 사용이 가능하다.

IN 연산자

• - IN 연산자는 하나의 컬럼이 여러개의 '=' 조건을 가지는 경우에 사용
• - OR는 IN을 포함한다. IN을 사용해 표현할 수 있는 것은 당연히 OR로 표현할 수 있다.
• - 하지만 OR로 표한한 것은 IN으로 표현하지 못할때가 있다. (OR에서 LIKE같은 연산자를 사용한 경우)
• - IN은 반드시 하나의 컬럼이 비교되어야 하므로 나중에 인덱스 구성에 대한 전략을 수립할 때 유리하다.
• - 그러므로 가능한 OR보다는 IN 을 사용하는 것이 좋다.

-- 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제

SELECT empno,ename,sal,deptno 

  FROM emp

 WHERE sal IN (SELECT MAX(sal)

                 FROM emp

                GROUP BY deptno);

 

EMPNO ENAME         SAL     DEPTNO

----- --------  --------- ---------

 7698 BLAKE        2850        30

 7788 SCOTT        3000        20

 7902 FORD         3000        20

ANY 연산자

- ANY 연산자는 Subquery의 여러 결과값 중 어느 하나의 값만 만족이 되면 행을 반환 한다.

-- SALESMAN 직업의 급여보다 많이 받는 사원의 사원명과 급여 정보를 출력하는 예제

SELECT ename, sal

  FROM emp

 WHERE deptno != 20

   AND sal > ANY (SELECT sal

                    FROM emp

                   WHERE job='SALESMAN');

 

ENAME             SAL

---------- ----------

ALLEN            1600

BLAKE            2850

CLARK            2450

...

ALL 연산자

- ALL 연산자는 Subquery의 여러 결과값 중 모든 결과 값을 만족해야 행을 반환 한다.

-- 모든 SALESMAN직업의 급여보다 많이받는 사원의 사원명과 급여정보를 출력하는예제

SELECT ename, sal

  FROM emp

 WHERE deptno != 20

   AND sal > ALL (SELECT sal

                    FROM emp

                   WHERE job='SALESMAN');

 

ENAME             SAL

---------- --------

CLARK            2450

BLAKE            2850

KING             5000

EXISTS 연산자

• - EXISTS 연산자는 Subquery 데이터가 존재하는가를 체크해 존재 여부(TRUE,FALSE)를 결과로 반환한다.
• - EXISTS절에는 반드시 메인 쿼리와 연결이 되는 조인 조건을 가지고 있어야 한다.
• - subquery에서 결과 행을 찾으면, inner query 수행을 중단하고 TRUE를 반환한다.

-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우

-- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를

-- 체크해야 한다.

-- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고

-- DISTINCT로 중복 제거를 한다.

SELECT DISTINCT d.deptno, d.dname

  FROM dept d, emp e

 WHERE d.deptno = e.deptno;

 

 

 

-- EXISTS를 사용하는 Subquery로 변경

-- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해

-- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다.

SELECT d.deptno, d.dname

  FROM dept d

 WHERE EXISTS

      (SELECT 1

         FROM emp e

        WHERE e.deptno = d.deptno);

출처 : http://www.gurubee.net/lecture/1503