[Oracle] 서브쿼리(SubQuery)

1. 부질의

SQL에서 SELECT, INSERT, UPDATE, DELETE 문에 중첩된 내부 SELECT문을 포함할 수 있으며, 이렇게 포함된 SELECT 문장을 부질의라고 합니다. 

 

2. 조건절의 부질의

일반적으로 부질의는 조건절(WHERE, HAVING)에 비교 연산자와 함께 사용됩니다.

 

'BLAKE' 사원보다 많은 급여를 받는 사원정보를 검색하기 위한 질의문을 작성하기 위해 먼저 'BLAKE' 사원의 급여를 알아야 합니다. 그리고 이 값을 이용하여 'BLAKE'보다 급여를 많이 받는 사원 번호와 이름, 급여를 검색하면 됩니다.

SELECT deptno, sal

FROM emp

WHERE sal > 

(SELECT sal

FROM emp

WHERE ename = 'BLAKE');

 

다음은 위의 질의를 SELF 조인을 이용하여 재 작성한 예입니다.

SELECT a.empno, a.ename, a.sal

FROM emp a, emp b

WHERE a.sal > b.sal and b.ename = 'BLAKE';

결국 부질의도 하나의 조건으로 처리되며, 다른 조건과 관계 연산자를 이용하여 결합할 수 있습니다. 그러나 부질의의 결과가 열의 여러 개 값을 반환하는 경우, 하나의 값을 반환할 때처럼 비교 연산자를 사용할 수 없으며, 이때에는 IN,ALL,ANY 등을 사용하여야 합니다.

 

10번 부서 사원의 직무와 같은 직무를 가지고 있는 다른 부서의 사원의 정보를 검색하는 질의문입니다.

SELECT *

FROM emp

WHERE job in

(SELECT job 

FROM emp

WHERE deptno = 10);

 

결과에서 10번 부서의 사원정보가 나오는 것이 부적절하다면 다음과 같이 질의를 고쳐 실행합니다.

SELECT *

FROM emp

WHERE job in

(SELECT job 

FROM emp

WHERE deptno = 10)

And deptno !=10;

 

ANY를 사용할 경우 3개 값 중 하나 이상의 결과가 조건을 만족하면 되고, ALL을 사용할 경우 3개 값 모두에 대해조건이 만족하여야 합니다.

SELECT *

FROM emp

WHERE sal > any

    (SELECT avg(sal)

     FROM emp

     GROUP BY deptno);

위의 결과는 부질의 중 최소값 보다 큼으로 처리 됩니다.

 

SELECT *

FROM emp

WHERE sal > all

(SELECT avg(sal)

FROM emp

GROUP BY deptno);

위의 결과는 중 최대값 보다 큼으로 처리 됩니다.

 

부질의는 WHERE절 뿐만 아니라 HAVING절에서도 사용 가능합니다. 30번 부서의 최소 급여보다 최소 급여가 큰 부서의 번호와 최소 급여를 구하는 질의문입니다. 

SELECT deptno, min(sal)

FROM emp

GROUP BY deptno having min(sal) >

  (SELECT min(sal)

   FROM emp

   WHERE deptno = 30);

 

일반적으로 부질의의 질의문을 조인 질의로 표현 가능합니다. 그러나 모든 부질의를 갖는 질의문을 조인 질의로 바꿀 수는 없습니다. 다음은 NEW YORK에 위치하는 부서에근무하는 사원 번호, 이름, 부서 이름을 검색하는 조인을 사용한 질의문입니다.

SELECT emp.empno, emp.ename, dept.dname

FROM emp, dept

WHERE emp.deptno = dept.deptno and loc = 'NEW YORK';

 

그러나 이 문장은 부질의 문장으로 변경할 수 없습니다. 왜냐하면 SELECT 열의 이름 중 DNAME는 EMP테이블이 아닌 DEPT 테이블의 열이므로 사용할 수 없습니다. 

SELECT emp.empno, emp.ename, dept.dname, emp.deptno

FROM emp, dept

WHERE emp.deptno = (

                    SELECT deptno

                    FROM dept

                    WHERE loc = 'NEW YORK'

                    );       

 

지금까지는 부질의 결과값이 하나의 열에 대한 것을 공부하였습니다. 그러나 경우에 따라서는 부질의 결과가 여러 개 열에 대한 값일 경우가 있습니다. 복수열값을 반환하는 부질의는 하나의 행을 반환하는 복수열 단일행 부질의와 여러 개의 행들을 반환하는 복수열 복수행 부질의로 구분됩니다. 

 

부질의가 복수열로 하나의 행을 반환하는 경우 부질의의 결과값들의 비교는 비교 연산자 "="가 사용됩니다. 

SELECT *

FROM test

WHERE (sal, nvl(comm, -1)) = (

    SELECT sal, nvl(comm,-1)

    FROM test

    WHERE empno = 11);

 

다음으로 부질의 결과가 복수열로 된 여러 행 값을 반환하는 경우 부질의 앞에는 부질의 결과인 여러 행의 복수열을 처리하기 위해 "IN"이 사용됩니다.

SELECT *

FROM test

WHERE (sal, nvl(comm,-1)) in (SELECT sal, nvl(comm, -1)

                           FROM  test

                           WHERE deptno = 30);

 

3. FROM절의 부질의

부질의는 FROM절에서도 사용할 수 있으며, 인라인뷰라고 부릅니다. 다음은 사원의 급여가 자신이 속하는 부서의 평균 급여보다 많은 사원 정보를 검색하는 예입니다. 먼저 각 부서의 평균 급여를 검색하는 질의문입니다.

SELECT deptno, round(avg(sal),2)

FROM emp

GROUP BY deptno;

 

이 결과를 테이블처럼 생각하여 FROM 절에서 사용하려면, 이 결과를 반환하는 SELECT 문장을 FROM절에 기술하면 됩니다. 인라인 뷰를 사용할 경우 테이블의 이름이 없으므로 별칭을 붙여 사용합니다.

SELECT empno, ename, emp.deptno, emp.sal, round(emp.sal,2) as 부서평균급여

FROM emp, (SELECT deptno, avg(sal) sal 

           FROM emp

           GROUP BY deptno) a

WHERE emp.deptno = a.deptno and emp.sal > a.sal;

4. SELECT절 서브쿼리

SELECT절 안에 서브쿼리가 들어있는 형태입니다. 스칼라서브쿼리라고도 부릅니다. SELECT 절에 오는 서브 쿼리는 반드시 단일 값을 리턴해야합니다. SUM, COUNT, MIN, MAX 등과 같은 집계 함수가 많이 쓰이는 이유입니다.

SELECT product_name, list_price, 

round(

(SELECT avg(list_price)

FROM products p1

WHERE p1.category_id = p2.category_id

), 2) avg_list_price

FROM products p2

ORDER BY product_name;

 

테이블에 존재하지 않는 데이터를 조회하고 싶을 때 사용하는 쿼리입니다.