You've got to find what you love.

* SUB-QUERY는 4가지 종류가 있습니다.

• 단일 행 SUB-QUERY
• 복수 행 SUB-QUERY
• 복수 컬럼 SUB-QUERY
• 상호관련 SUB-QUERY

# 단일 행 SUB-QUERY
- 단일 행 SUB-QUERY 는 그 실행 결과가 하나의 컬럼, 하나의 행을 리턴해 주는 경우를 의미합니다. 그 결과와 비교 해야 하기 때문에 비교 연산자가 사용 됩니다.

/*
 단일 행 SUB-QUERY 가 여러 개의 결과를 리턴하는 경우 에러가 발생합니다.
*/
SQL> SELECT ENAME, SAL
  2  FROM EMP
  3    WHERE SAL = (SELECT SAL FROM EMP
  4    WHERE DEPTNO = 10);
  WHERE SAL = (SELECT SAL FROM EMP
               *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row(단일 행 부속 질의에 1개 이상의 행이 리턴 되었습니다.)

/*
 SUB-QUERY에 의해 리턴되는 결과의 컬럼 수와 MAIN-QUERY에서 비교되는 컬럼 수가 일치하지 않으면 에러가 발생합니다.
*/
SQL> SELECT ENAME, SAL
  2  FROM EMP
  3    WHERE SAL = (SELECT SAL, COMM FROM EMP
  4    WHERE DEPTNO = 10);
  WHERE SAL = (SELECT SAL, COMM FROM EMP
               *
ERROR at line 3:
ORA-00913: too many values

/*
 SUB-QUERY의 WHERE 절에 복수 행 함수를 사용할 수 없습니다.
*/
SQL> SELECT ENAME, SAL
  2  FROM EMP
  3    WHERE SAL = (SELECT SAL FROM EMP
  4    WHERE SAL > AVG(SAL));
  WHERE SAL > AVG(SAL))
              *
ERROR at line 4:
ORA-00934: group function is not allowed here

# 복수 행 SUB-QUERY
- 복수 행 SUB-QUERY는 그 실행 결과가 여러 개의 행을 리턴해 주는 경우를 의미합니다.

1) IN 연산자 : SUB-QUERY에 의해 리턴되는 값 중에서 조건에 해당하는 행이 있으면 출력해 줍니다.

SQL> SELECT ENAME, SAL
  2  FROM EMP
  3  WHERE SAL IN (SELECT MAX(SAL) FROM EMP
  4  GROUP BY DEPTNO);

ENAME             SAL
---------- ----------
BLAKE            2850
FORD             3000
SCOTT            3000
KING             5000
CLARK            2450

2) ANY 연산자

SQL> SELECT EMPNO, ENAME, JOB, SAL
  2  FROM EMP
  3    WHERE SAL = ANY (SELECT SAL FROM EMP
  4    WHERE JOB = 'MANAGER');

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450

3) ALL 연산자
- <ALL : 결과 중에 가장 작은 값보다 작은 조건의 행
- >ALL : 결과 중에 가장 큰 값보다 큰 조건의 행

SQL> SELECT EMPNO, ENAME, JOB, SAL
  2  FROM EMP
  3    WHERE SAL < ALL (SELECT SAL FROM EMP
  4    WHERE JOB = 'MANAGER');

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7368 SMITH      CLERK            800
      7369 SMITH      CLERK            800
      7499 ALLEN      SALESMAN        1600
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7844 TURNER     SALESMAN        1500
      7876 ADAMS      CLERK           1100
      7900 JAMES      CLERK           1957
      7934 MILLER     CLERK           1300

SQL> SELECT EMPNO, ENAME, JOB, SAL
  2  FROM EMP
  3    WHERE SAL > ALL (SELECT SAL FROM EMP
  4    WHERE JOB = 'MANAGER');

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

4) EXISTS 연산자

SQL> SELECT DNAME, DEPTNO
  2  FROM DEPT
  3    WHERE EXISTS (SELECT * FROM EMP
  4    WHERE DEPTNO = 10);

DNAME              DEPTNO
-------------- ----------
ACCOUNTING             10
RESEARCH               20
SALES                  30
OPERATIONS             40

# 복수 컬럼 SUB-QUERY














- 복수 컬럼 SUB-QUERY는 실행 결과가 복수 개의 컬럼, 복수 개의 행을 리턴 해줍니다.

SQL> SELECT ENAME, DEPTNO, NVL(COMM, -1)
  2    FROM EMP
  3    WHERE (SAL, NVL(COMM, -1)) IN (SELECT SAL, NVL(COMM, -1)
  4    FROM EMP
  5    WHERE DEPTNO = 30);

ENAME          DEPTNO NVL(COMM,-1)
---------- ---------- ------------
ALLEN              30          300
WARD               30          500
MARTIN             30         1400
BLAKE              30           -1
TURNER             30            0
JAMES              30           -1

# 상호 관련 SUB-QUERY
- 상호 연관 SUB-QUERY는 MAIN-QUERY절에 사용된 테이블이 SUB-QUERY절에 다시 재사용되는 경우의 SUB-QUERY를 말합니다. SUB-QUERY에 사용 될 조건 값을 MAIN-QUERY로부터 참조해야 하는 경우 사용되는 방법입니다.
이러한 SUB-QUERY의 사용은 사용자의 SQL문을 보다 쉽고 간편하게 해주는 장점을 가지고 있지만 한편으론 성능을 저하하는 단점을 가지고 있기도 합니다.

SQL> SELECT E.EMPNO, E.ENAME, E.DEPTNO
  2  FROM EMP E
  3  WHERE SAL > (SELECT AVG(SAL)
  4  FROM EMP D
  5  WHERE D.DEPTNO = E.DEPTNO);

     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7698 BLAKE              30
      7900 JAMES              30
      7566 JONES              20
      7788 SCOTT              20
      7902 FORD               20
      7782 CLARK              10

출처 : 오라클 ACE가 해설하는 NEW SQL & PL/SQL