[Database/SQL]오라클 서브 쿼리(Sub Query)란??

서브 쿼리(Sub Query)

하나의 SQL 문장 내에 포함된 또 다른 SQL 문이 있는 쿼리문

전체 SQL 문장을 메인 쿼리라고 하며, 메인 쿼리(Main Query or Outer Query)에 종속된 내부 쿼리를 서브 쿼리(Sub Query or Inner Query)라고 한다.

서브 쿼리는 반드시 괄호 () 안에 넣어 표현해야 한다.

- 이름이 'KING'인 사원의 부서번호를 조회하는 서브쿼리와 그 결과로 부서명을 조회하는 메인쿼리

SELECT dname FROM dept
WHERE deptno = ( SELECT deptno FROM emp WHERE ename = 'KING' )

DNAME
-----------
ACCOUNTING

단일 행 서브 쿼리

서브 쿼리의 결과가 1개의 행만 나오는 것

서브 쿼리의 결과가 1건만 나오고 이 결과를 메인 쿼리로 전달하여 전체적인 쿼리를 수행한다.

메인 쿼리의 WHERE절에서 단일 행 연산자(=, <>, >, <=, <, <=)를 이용한다.

- 평균 급여보다 많은 급여를 받는 사원 조회

SELECT empno, ename, sal FROM emp
WHERE sal >= (SELECT AVG(sal) FROM emp);

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

다중 행 서브 쿼리

서브 쿼리의 결과가 두 건 이상 출력되는 것

단일 행 연산자는 사용할 수 없으며, 다중 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)만 사용 가능

IN

WHERE절의 IN연산자와 같다.

하나의 컬럼이 여러 개의 '=' 조건을 가지는 경우 사용

- 부서별 급여를 제일 많이 받는 사원 조회

SELECT dname, empno, ename, sal FROM emp
JOIN dept USING(deptno)
WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);

DNAME           EMPNO   ENAME   SAL
---------       -----   ------  -----
SALES           7698    BLAKE   2850
RESEARCH        7902    FORD    3000
ACCOUNTING      7839    KING    5000

ANY, SOME

메인 쿼리의 비교 조건이 서브 쿼리의 여러 검색 결과 중 하나 이상 만족되면 반환

- 'SALESMAN' 들의 급여를 조회하여 ANY 연산자로 메인 쿼리와 비교하여 출력

  'SALESMAN' 들의 급여 중 최소값보다 많은 급여를 받는 사원들이 출력된다.

SELECT empno, ename, sal FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE JOB='SALESMAN');

 EMPNO          ENAME    SAL
-----          -----    ----
7839	       KING  	5000
7902	       FORD  	3000
7788	       SCOTT	3000
7566	       JONES	2975
7698	       BLAKE	2850
7782	       CLARK	2450
7499	       ALLEN	1600
7844	       TURNER	1500
7934	       MILLER	1300

ALL

메인 쿼리의 비교 조건이 서브 쿼리의 여러 검색 결과와 모든 값이 일치하면 반환

- 'MANAGER' 사원들의 급여들 보다 높은 급여를 받는 사원 조회

   MANAGER 사원들 중 최고 급여인 2975 보다 많은 급여를 받는 사원들이 조회된다.

SELECT empno, ename, sal FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE JOB='MANAGER');

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

EXISTS

서브쿼리의 데이터가 존재하는 지 여부를 먼저 따져 존재하는 값들만 결과로 출력

- 관리자로 등록되어 있는 사원들을 조회

SELECT empno, ename, sal FROM emp e
WHERE EXISTS(SELECT empno FROM emp WHERE e.empno = mgr); 

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

다중 열 서브 쿼리

서브 쿼리의 결과가 두 개 이상의 컬럼으로 반환되어 메인 쿼리에 전달하는 쿼리

- 부서 번호가 30인 사원들의 급여와 부서번호를 묶어 메인쿼리를 전달

SELECT empno, ename, sal, deptno FROM emp
WHERE (deptno, sal) IN (
            SELECT deptno, sal FROM emp
            WHERE deptno = 30
);

EMPNO  ENAME    SAL     DEPTNO
----   -----    ----    --
7499   ALLEN	1600	30
7654   MARTIN	1250	30
7521   WARD	1250	30
7698   BLAKE	2850	30
7844   TURNER	1500	30
7900   JAMES	950	30

From 절 서브 쿼리

메인 쿼리의 FROM절을 서브 쿼리로 이용하는 방법

- 급여가 부서번호 20인 부서의 평균보다 높고 사원을 관리하는 'MANAGER'로써 20부서에 속하지 않는 사원을 조회

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                 CLAKE    MANAGER         2450        30

집합 연산자

두 개 이상의 쿼리 결과를 하나로 결합하는 연산자

여러 개의 SELECT문을 하나로 연결한다.

집합 연산자로 결합되는 결과의 컬럼은 데이터타입이 동일해야한다.

UNION

여러 개의 SQL문의 결과에 대한 합집합

중복행을 하나의 행으로 보여준다.

- 영업사원과 관리자를 따로 질의하여 UNION으로 결합하는 쿼리문

SELECT empno, ename, JOB FROM emp WHERE JOB = 'SALESMAN'
UNION
SELECT empno, ename, JOB FROM emp WHERE JOB = 'MANAGER';

UNION ALL

여러 개의 SQL문의 결과에 대한 합집합

UNION 연산자와 다르게 중복행도 모두 출련된다.

- 급여가 1000미만인 직원, 2000미만인 직원을 조회하여 UNION ALL로 결합하는 쿼리문

  중복 결과를 포함하여 출력됨

SELECT empno, ename, sal FROM emp WHERE sal < 1000
UNION ALL
SELECT empno, ename, sal FROM emp WHERE sal < 2000
ORDER BY empno;

INTERSECT

여러 개의 SQL문의 결과에 대한 교집합

중복행은 하나의 결과로 보여준다.

EXISTS또는 IN 서브쿼리를 이용한 SQL문으로 변경 가능하다.

- 급여가 1000 초과인 직원을 조회하고 2000미만인 직원을 조회하여 INTERSECT로 결합하는 쿼리문

  급여가 1000 초과 2000 미만인 직원들만 조회

SELECT empno, ename, sal FROM emp WHERE sal > 1000
INTERSECT
SELECT empno, ename, sal FROM emp WHERE sal < 2000

MINUS

여러 개의 SQL문의 결과에 대한 차집합

중복행은 하나의 결과로 보여준다. 

NOT EXISTS또는 NOT IN 서브쿼리를 이용한 SQL문으로 변경 가능하다.

- 사원 전체 결과에서 MINUS연산자를 이용해 급여가 2000 초과인 사원 제외하여 조회하는 쿼리문

SELECT empno, ename, sal FROM emp
MINUS
SELECT empno, ename, sal FROM emp WHERE sal > 2000;

이상으로 오라클 서브 쿼리(Sub Query)에 대한 포스팅을 마치도록 하겠다.

서브 쿼리는 기술 면접에서 JOIN과 빈번하게 나오는 개념으로 꼭 알고 넘어가자~~

그리고 서브 쿼리를 사용한 쿼리문을 한 두개씩 알아두면 오라클뿐만 아니라 SQL을 사용할 때, 도움이 많이 될 것이다.