[Oracle] 서브쿼리(subquery) 정리 및 유의사항

■ 작성일 : 2005년 5월
■ 작성자 : http://blog.naver.com/thescream
■ 저작권 : 고생해서 정리한다. 퍼가더라도 링크하나 남겨주면 고맙겠다.
■ 참조링크
■ 첨부파일 

 

Sub Query

 

1. 정의

 - select 문장(메인쿼리)의 절(clause)에 포함된 select 문장이다.

 - where 절의 sub query는 비교 대상의 값이 미지정일 경우 사용된다.

 - from 절의 sub query는 sub query의 결과 집합(Result Set)을 하나의 View로 간주한다.

 

  주의사항

 - WHERE 절, HAVING절, FROM 절에 서브쿼리를 사용한다.

 - 서브쿼리는 ()로 둘러싸여져야 한다.

 - 서브쿼리는 연산자의 오른쪽에 있어야 한다.

 - 단일행 서브쿼리는 단일행 연산자를, 다중행 서브쿼리는 다중행 연산자를 사용해야 한다.

 

 

2. 유형

A 단일행(single-row) 서브쿼리 :

 서브쿼리의 결과로 하나의 행이 리턴된다. 

 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용 할 수 있다.

 

B 다중행(multiple-row) 서브쿼리 :

 서브쿼리의 결과로 하나 이상의 행이 리턴된다.

 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용 할 수 있다.

 

IN 연산자의 사용예제

예) 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제 입니다.

 

SQL>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
      7839 KING             5000         10

 

ANY 연산자의 사용예제

 

SQL> SELECT ename, sal from emp
  2  where deptno !=20
  3  and sal > ANY(SELECT sal from emp where job = 'SALESMAN');

ENAME             SAL
---------- ----------
ALLEN            1600
BLAKE            2850
CLARK            2450
KING             5000
TURNER           1500
MILLER           1300

6 개의 행이 선택되었습니다.

 

ALL 연산자의 사용예제

 

SQL> SELECT ename, sal from emp
  2  where deptno != 20
  3  and sal > ALL(select sal from emp where job='SALESMAN');

ENAME             SAL
---------- ----------
BLAKE            2850
CLARK            2450
KING             5000

 

EXITS 연산자의 사용예제

EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만을 결과로 반환해 줍니다.

SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓 입니다.

 

예제)사원을 관리할 수 있는 사원의 정보를 보여 줍니다.

SQL> select empno, ename, sal from emp e
  2  WHERE EXISTS (select empno from emp where e.empno = mgr);

EMPNO ENAME             SAL
----- ---------- ----------
 7566 JONES            2975
 7698 BLAKE            2850
 7782 CLARK            2450
 7788 SCOTT            3000
 7839 KING             5000
 7902 FORD             3000

6 개의 행이 선택되었습니다.

 

 

C 다중열(multiple-column) 서브쿼리 :

다중열 서브쿼리란 서브쿼리의 결과값이 두개 이상의 컬럼을 반환하는 서브쿼리 입니다.
페어와이즈(Pairwise), Non페워와이즈로 구분

 

가. Pairwise(쌍비교) 서브쿼리

서브쿼리가 한번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 줍니다.

SQL>SELECT empno, sal, deptno FROM emp
        WHERE (sal, deptno) IN ( SELECT sal, deptno FROM emp
        WHERE deptno = 30 AND comm is NOT NULL );

 

EMPNO     SAL         DEPTNO
--------  -------- ----------
7521         1250         30
7654         1250         30
7844         1500         30
7499         1600         30

 

나. Nonpairwise(비쌍비교) 서브쿼리

서브쿼리가 여러 조건별로 사용 되어서 결과값을 주 쿼리로 넘겨 줍니다.

 

SQL>SELECT empno, sal, deptno FROM emp
        WHERE sal IN ( SELECT sal FROM emp WHERE deptno = 30
        AND comm is NOT NULL ) AND deptno IN ( SELECT deptno FROM emp
        WHERE deptno = 30 AND comm is NOT NULL );

 

EMPNO    SAL      DEPTNO
--------  ------ -------
7521         1250     30
7654         1250     30
7844         1500     30
7499         1600     30

 

다. NULL Value IN a Subquery

서브쿼리에서 null값이 반환되면 주 쿼리 에서는 어떠한 행도 반환되지 않습니다.

 

 

D. FROM절 상의 서브쿼리(INLINE VIEW)와 상관관계 서브쿼리

 

FROM 절상의 서브쿼리(INLINE VIEW) 란?

◈ SUBQUERY는 FROM절에서도 사용이 가능 합니다.

◈ INLINE VIEW란 FROM절상에 오는 서브쿼리로 VIEW처럼 작용 합니다.

 

예제)급여가 20부서의 평균 급여보다 크고 사원을 관리하는 사원으로서 20부서에 속하지 않은 사원의 정보를 보여주는 SQL문 입니다.

 

SELECT b.empno,b.ename,b.job,b.sal, b.deptno

FROM (SELECT empno FROM emp

         WHERE sal >(SELECT AVG(sal) FROM emp

         WHERE deptno = 20)) a, emp b WHERE a.empno = b.empno
         AND b.mgr is NOT NULL
         AND b.deptno != 20

 

EMPNO    ENAME    JOB             SAL      DEPTNO
-------- -------- ----------   -----   ----------
7698         BLAKE     MANAGER    2850     30
7782         CLARK     MANAGER    2450     10

 

예제) 급여를 많이 받는 순으로 랭킹을 매겨서 결과를 보여주라.

 

      순위 EMPNO ENAME      DNAME                 SAL                    // 결과문
---------- ----- ---------- -------------- ----------
         1  7839 KING       ACCOUNTING           5000
         2  7788 SCOTT      RESEARCH             3000
         3  7902 FORD       RESEARCH             3000
         4  7566 JONES      RESEARCH             2975
         5  7698 BLAKE      SALES                2850
         6  7782 CLARK      ACCOUNTING           2450
         7  7499 ALLEN      SALES                1600
         8  7844 TURNER     SALES                1500
         9  7934 MILLER     ACCOUNTING           1300
        10  7521 WARD       SALES                1250
        11  7654 MARTIN     SALES                1250

        12  7876 ADAMS      RESEARCH             1100
        13  7900 JAMES      SALES                 950
        14  7369 SMITH      RESEARCH              800

14 개의 행이 선택되었습니다.

 

SQL>select rownum 순위, empno, ename, dname, sal from
  2  (select e.empno, e.ename, e.job, d.dname, e.sal
  3  from emp e, dept d
  4  where e.deptno = d.deptno
  5  order by e.sal desc);

 

위에서 rownum은 결과값에 대한 순번을 매긴것이다.

 

 

상관관계 서브쿼리

◈ 상관관계 서브쿼리란 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 입니다.

◈ 이는 주 쿼리에서 서브 쿼리를 참조하고 이 값을 다시 주 쿼리로 반환한다는 것입니다.

 

예제) 사원을 관리할 수 있는 사원의 평균급여보다 급여를 많이 받는 사원의 정보를 출력

 

SELECT empno, ename, sal
FROM emp e
WHERE sal > (SELECT AVG(sal) sal
                      FROM emp
                      WHERE e.empno = mgr)

 

EMPNO-- ENAME-- SAL
--------- -------  ----------
7698------ BLAKE--- 2850
7782------ CLARK--- 2450
7788------ SCOTT--- 3000
7839------ KING----- 5000
7902------ FORD-----3000

　

 

E. 집합 쿼리(UNION, INTERSECT, MINUS)

 

☞ 집합 쿼리(UNION, INTERSECT, MINUS)

◈ 집합 연산자를 사용시 집합을 구성할 컬러의 데이터 타입이 동일해야 합니다.

◈ UNION :합집합

◈ UNION ALL:공통원소 두번씩 다 포함한 합집합

◈ INTERSECT:교집합

◈ MINUS:차집합

☞ UNION

◈ UNION은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환 합니다.　

SQL>SELECT deptno FROM emp
       UNION
        SELECT deptno FROM dept;

DEPTNO
----------
10
20
30
40

☞ UNION ALL

◈ UNION과 같으나 두 테이블의 중복되는 값까지 반환 합니다.

SQL>SELECT deptno FROM emp
       UNION ALL
        SELECT deptno FROM dept;

DEPTNO
---------
20
30
30
20
10
20
10
30
....

☞ INTERSECT

◈ INTERSECT는 두 행의 집합중 공통된 행을 반환 합니다.

SQL>SELECT deptno FROM emp
       INTERSECT
         SELECT deptno FROM dept;

DEPTNO
----------
10
20
30

☞ MINUS

◈ MINUS는 첫번째 SELECT문에 의해 반환되는 행중에서 두번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 보여 줍니다.

SQL>SELECT deptno FROM dept
       MINUS
         SELECT deptno FROM emp;

DEPTNO
----------
40

 

샘플

다음 문장은 단일행에서의 서브쿼리 방법이다.

존스보다 많이 받는 사람을 알아내기 위해 두가지 쿼리를 생각할 수 있다.

 

SQL> select sal from emp where ename = 'JONES';

       SAL
----------
      2975

SQL> select empno, ename, sal from emp where sal > 2975;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7839 KING             5000
      7902 FORD             3000

 

만일 존스의 월급이 얼마인지는 상관이 없고(혹은 모를경우) 위의 형식을 합친것이 아래와 같이 된다. 즉 비교대상의 값이 미지정일 경우에 서브쿼리를 사용한다.

SQL> select empno, ename, sal from emp
  2  where sal > (select sal from emp where ename = 'JONES');

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7839 KING             5000
      7902 FORD             3000

 

 

급여가 가장작은 사람의 행의 값을 가져와라.

SQL> select empno, ename, sal from emp
  2  where sal = (select MIN(SAL) FROM emp);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800

 

SQL> select empno, ename, sal from emp
  2  where sal = (select MIN(SAL) FROM emp GROUP BY deptno);
where sal = (select MIN(SAL) FROM emp GROUP BY deptno)
             *
2행에 오류:
ORA-01427: 단일 행 부속 질의에 2개 이상의 행이 리턴되었습니다

 

단일행의 값을 가져와야 하는 상황에서 그룹에 대한 다중행의 값을 가져오도록 하면 에러가 난다.

즉, 위의 ()안에 있는 문장의 결과를 가져와 보면 아래와 같이 3개의 다중행의 값을 가져온다.

따라서 단일행의 값을 가져올때는 '='만 써야한다.

 

SQL> select MIN(SAL) FROM emp group by deptno;

  MIN(SAL)
----------
      1300
       800
       950

 

다음은 HAVING 절에서의 서브쿼리를 사용한 방법이다.

 

SQL> SELECT job, MAX(SAL) FROM emp
  2  GROUP BY JOB
  3  HAVING MAX(SAL) > (select MAX(SAL) FROM emp where job = 'SALESMAN');

JOB         MAX(SAL)
--------- ----------
ANALYST         3000
MANAGER         2975
PRESIDENT       5000

 

HAVING절은 그룹의 결과를 제한할때 쓰인다고 했다. 위에서 서브쿼리만 실행한 것이 아래것이다.

즉 SAL이 1600보다 큰 값에 대해서 JOB 그룹별로 결과를 추출한것이다.

 

SQL> select MAX(SAL) FROM emp where job = 'SALESMAN';

  MAX(SAL)
----------
      1600

 

SQL> SELECT job, MAX(SAL) FROM emp
  2  GROUP BY JOB;

JOB         MAX(SAL)
--------- ----------
ANALYST         3000
CLERK           1300
MANAGER         2975
PRESIDENT       5000
SALESMAN        1600

 

HAVING 조건을 주지 않았을 경우 JOB에 대한 그룹결과는 5개행이 나온다.

 

 [출처] https://m.blog.naver.com/thescream/169759429