[8i] 서브쿼리 & 다중 열 서브쿼리 예

/********************************************************************************************
-- Title : [8i] 서브쿼리 & 다중 열 서브쿼리 예
-- Reference : OLN
-- Key word : pairwise non-pairwise sub query subquery sub-query
********************************************************************************************/

/****************************************************************************************
-- 서브쿼리 사용 예
****************************************************************************************/
SQL> SELECT ename
  2  FROm emp
  3  WHERE sal > (SELECT sal    
  4               FROM emp
  5               WHERE empno = 7566);

ENAME
----------
SCOTT
KING
FORD

/****************************************************************************************
-- 서브쿼리를 가진 HAVING 절
****************************************************************************************/
SQL> SELECT deptno, MIN(sal)
  2  FROM emp
  3  GROUP BY deptno
  4  HAVING MIN(sal) > (SELECT MIN(sal)
  5                     FROM emp
  6                     WHERE deptno = 20);

    DEPTNO   MIN(SAL)
---------- ----------
        10       1300
        30        950
       
       
/****************************************************************************************
-- 다중행 서브 쿼리의 예
****************************************************************************************/
SQL> SELECT ename, sal, deptno
  2  FROm emp
  3  WHERE sal IN (800, 950, 1300);

ENAME             SAL     DEPTNO
---------- ---------- ----------
SMITH             800         20
JAMES             950         30
MILLER           1300         10

/****************************************************************************************
-- 다중 행 서브쿼리에서 ANY 연산자 사용
****************************************************************************************/
SQL> SELECT empno, ename, job
  2  FROM emp
  3  WHERE sal < ANY (SELECT sal
  4                   FROM emp
  5                   WHERE job = 'CLERK')
  6  AND job <> 'CLERK';

     EMPNO ENAME      JOB
---------- ---------- ---------
      7521 WARD       SALESMAN
      7654 MARTIN     SALESMAN
     
     
/****************************************************************************************
-- 다중 행 서브쿼리에서 ALL 연산자 사용
****************************************************************************************/
SQL> SELECT empno, ename, job
  2  FROm emp
  3  WHERE sal > ALL (SELECT avg(sal)
  4                  FROM emp
  5                  GROUP BY deptno)

     EMPNO ENAME      JOB
---------- ---------- ---------
      7566 JONES      MANAGER
      7788 SCOTT      ANALYST
      7839 KING       PRESIDENT
      7902 FORD       ANALYST

/****************************************************************************************
-- 다중 열 서브쿼리 사용 예
****************************************************************************************/
SQL> SELECT ename, deptno, sal, comm
  2  FROM emp
  3  WHERE (sal, NVL(comm, -1)) IN (SELECT sal, NVL(comm, -1)
  4                                 FROM emp
  5                                 WHERE deptno = 30);

ENAME          DEPTNO        SAL       COMM
---------- ---------- ---------- ----------
JAMES              30        950
WARD               30       1250        500
MARTIN             30       1250       1400
TURNER             30       1500          0
ALLEN              30       1600        300
BLAKE              30       2850

6 rows selected.

/****************************************************************************************
-- Nonpairwise 서브쿼리 예
****************************************************************************************/
SQL> SELECT ename, deptno, sal, comm
  2  FROM emp
  3  WHERE sal IN (SELECT sal
  4                FROM emp
  5                WHERE deptno = 30)
  6  AND NVL(comm, -1) IN (SELECT NVL(comm, -1)
  7                        FROM emp
  8                        WHERE deptno = 30);

ENAME          DEPTNO        SAL       COMM
---------- ---------- ---------- ----------
JAMES              30        950
BLAKE              30       2850
TURNER             30       1500          0
ALLEN              30       1600        300
WARD               30       1250        500
MARTIN             30       1250       1400

6 rows selected.

/****************************************************************************************
-- Pairiwise 서브쿼리 예
****************************************************************************************/
SQL> SELECT ename, deptno, sal, comm
  2  FROM emp
  3  WHERE (sal, NVL(comm, -1)) IN (SELECT sal, NVL(comm, -1)
  4                                 FROM emp
  5                                 WHERE deptno = 30);

ENAME          DEPTNO        SAL       COMM
---------- ---------- ---------- ----------
JAMES              30        950
WARD               30       1250        500
MARTIN             30       1250       1400
TURNER             30       1500          0
ALLEN              30       1600        300
BLAKE              30       2850

6 rows selected.