[Oracle] 오라클 서브쿼리 종류 및 사용법 (SubQuery)

오라클에서 쿼리문을 작성하다 보면 서브 쿼리(subquery)를 자주 접하게 된다. 서브 쿼리를 처음 접하면 아주 복잡하게 느껴지는데, 기능을 조금만 익히면 쿼리문을 작성할 때 아주 유용하게 사용할 수 있다. 서브 쿼리는 메인 쿼리 내부에 작성하는 쿼리를 뜻하며, 주로 아래의 3가지 서브 쿼리로 분류한다.

 

오라클 서브쿼리 종류

서브 쿼리	사용 위치	설명
스칼라 서브 쿼리	SELECT 절	단일 칼럼, 단일 행을 반환 (1개의 값)
인라인 뷰	FROM 절	View와 사용적인 측면에서 동일함 (임시 뷰, 임시 테이블)
중첩 서브 쿼리	WHERE 절, HAVING 절	다중 칼럼 또는 다중 행을 반환

 

위의 3가지 분류 외에도 연관성 없는 서브 쿼리, 연관성 있는 서브 쿼리로 분류하기도 한다.  연관성 없는 서브 쿼리는 메인 쿼리의 테이블과 무관하게 실행되는 서브 쿼리이며, 연관성 있는 서브 쿼리는 메인 쿼리의 테이블과 조인하여 실행하는 서브 쿼리이다.

 

스칼라 서브 쿼리 (scalar subquery)

SELECT a.empno
     , a.ename
     , a.deptno
     , (SELECT dd.dname
          FROM dept dd
         WHERE dd.deptno = a.deptno) AS dept_name
  FROM emp a
 WHERE a.sal >= 3000

 

 

위의 예제는 서브 쿼리를 사용하여 부서명(dname)을 가져오는 쿼리이다.

코드 값을 가져오는 서브 쿼리는 주로 아우터 조인을 대신하여 사용하며 위의 쿼리는 아우터 조인으로 변경해도 된다.

 

SELECT 절에 사용하는 서브 쿼리는 하나의 값만 조회되어야 한다. (하나의 칼럼, 하나의 행)

다중 행의 값이 조회되면 "ORA-01427 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다."라는 오류가 발생한다.

(만약을 대비해서 서브 쿼리 마지막에 AND ROWNUM = 1을 붙이기도 함)

 

SELECT a.empno
     , a.ename
     , a.job
     , a.sal
     , a.sal - (SELECT ROUND(AVG(aa.sal))
                  FROM emp aa
                 WHERE aa.job = a.job) AS avg_sal_diff
  FROM emp a
 WHERE a.deptno = 20
 ORDER BY a.job, a.empno

 

 

위의 예제는 자신의 연봉에서 해당 직군의 평균 연봉과 차이를 계산하는 쿼리이다.

서버 쿼리의 결과와 칼럼을 값을 연산할 수도 있다.

 

SELECT a.deptno
     , a.dname
     , CASE WHEN a.deptno IN (SELECT DISTINCT aa.deptno
                                FROM emp aa
                               WHERE aa.job = 'MANAGER')
            THEN 'Y' END AS manager_yn
  FROM dept a

 

 

SELECT 절에서 CASE 표현식 등의 조건으로 서브 쿼리를 사용하면  다중 칼럼, 다중 행의 서브 쿼리를 사용할 수 있다.

 

인라인 뷰 (inline view)

SELECT a.empno
     , a.ename
     , a.job
     , b.mgr_name
     , b.mgr_dept
  FROM emp a
 INNER JOIN (SELECT a.empno AS mgr_no
                  , a.ename AS mgr_name
                  , b.dname AS mgr_dept
               FROM emp a
                  , dept b
              WHERE a.deptno = b.deptno) b
    ON (a.mgr = b.mgr_no)
 WHERE a.deptno = 10

 

 

인라인 뷰는 뷰(View)와 동일하며 다른 점은 뷰(View)는 오브젝트이기 때문에 재활용이 가능하지만 인라인 뷰는 해당 쿼리문에서만 사용하는 임시 뷰이다. 인라인 뷰는 WITH 절로 생성된 뷰와도 비슷하다.

 

인라인 뷰는 테이블(또는 뷰)처럼 메인 쿼리의 테이블과 조인을 하여 사용할 수 있다. 인라인 뷰는 테이블과 같이 인덱스가 없기 때문에 데이터가 많으면 쿼리문이 느려질 수 있어서 사용 시 주의해야 한다.

 

중첩 서브 쿼리 (nested subquery)

SELECT a.empno
     , a.ename
     , a.deptno
     , b.dname
  FROM emp a
 INNER JOIN dept b
    ON (a.deptno = b.deptno)
 WHERE a.job = 'CLERK'
   AND a.deptno IN (SELECT DISTINCT aa.deptno
                      FROM emp aa
                     WHERE aa.job = 'MANAGER')
 ORDER BY a.deptno

 

 

위의 예제는 IN 연산자에 값을 직접 입력하지 않고 서브 쿼리로 값을 가져와서 입력하는 쿼리이다.

조회된 다중행의 부서 코드(deptno) 값이 IN 연산자에 입력된다.

 

SELECT a.empno
     , a.ename
     , a.deptno
     , b.dname
  FROM emp a
 INNER JOIN dept b
    ON (a.deptno = b.deptno)
 WHERE a.job = 'CLERK'
   AND EXISTS (SELECT 1
                 FROM emp aa
                WHERE aa.job = 'MANAGER'
                  AND aa.deptno = a.deptno)
 ORDER BY a.deptno

 

 

IN 연산자의 예제와 동일한 결과의 쿼리이며, EXISTS 연산자의 서브 쿼리에 만족하는 값이 하나라도 조회되면 메인 쿼리에도 조회된다.

 

SELECT a.empno
     , a.ename
     , a.job
     , a.sal
  FROM emp a
 WHERE a.job IN ('MANAGER', 'SALESMAN')
   AND a.sal >= (SELECT ROUND(AVG(aa.sal))
                   FROM emp aa
                  WHERE aa.job = a.job)
 ORDER BY a.job, a.empno

 

 

서브 쿼리의 결과와 부등호 연산자(>, <, >=, <=, =)를 사용하여 칼럼의 값을 비교할 수 있다. 부등호 연산자를 사용할 때는 스칼라 서브 쿼리를 사용해야 한다.

 

연관성 없는 서브 쿼리, 연관성 있는 서브 쿼리

 

연관성 없는 서브 쿼리는 메인 쿼리의 테이블과 연관 없이 서브 쿼리 내부의 테이블로만 결과를 반환하는 쿼리이다.

연관성 있는 서브 쿼리는 메인 쿼리의 테이블과 조인하여 메인테이블의 칼럼 값에 맞는 결과를 반환하는 쿼리이다.

예제쿼리(테이블포함).txt

0.00MB

[Oracle] 오라클 WITH 절 사용법 및 동작방식 (임시 테이블)

[Oracle] 오라클 EXISTS, NOT EXISTS 사용법 정리 (IN, JOIN 비교)

[Oracle] 오라클 뷰(VIEW) 생성 및 사용법 완벽 정리 (insert, update)