오라클 서브쿼리 예제, Select, Where, From subquery

오라클에서 서브쿼리는 하위를 뜻하는 Sub와 질의를 뜻하는 Query의 합성어입니다. 오라클 문서에서는 서브쿼리 또는 하위질의라고 번역하여 안내하고 있습니다.

이름에서도 알 수 있듯이 서브쿼리는 메인 쿼리를 보조하는 수단으로 사용하며, Select, Insert, Update, Delete 문과 같은 모든 DML(Data Manipulation Language)에 적용할 수 있습니다.

서브쿼리 관련 용어

서브쿼리 중에 메인쿼리의 컬럼과 관계있는 서브쿼리의 한 형태를 상관 서브쿼리 또는 연관 서브쿼리(correlated subquery)라고 합니다. 보통 SELECT에서 가장 흔히 사용되는 형태인데 이러한 용어가 있구나 하고 넘어가시면 됩니다.

메인쿼리는 외부 쿼리(outer query)라고도 지칭합니다.

서브쿼리의 리턴 형태에 따라 다중 행 하위 질의(multi-row subquery) 또는 단일 행 하위 질의(single-row subquery)가 있으며, 단일 행 하위 질의 중에서도 1개의 열을 반환하는 것을 스칼라 서브쿼리(scalar subquery)라 지칭합니다. 즉, 스칼라 서브쿼리는 1 row, 1 column입니다. 참고로 [ ORA-01427 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다 ] 오류도 서브쿼리와 관련이 있는 오류입니다. 싱글 로우를 리턴 하리라고 예상하는 부분에 멀티 로우가 리턴되며 발생하는 현상입니다.

서브쿼리 사용하기 예제 자료

서브쿼리는 소괄호(parentheses)로 묶어서 작성합니다. 그리고 서브쿼리의 확장성은 서브쿼리 내에 다시 서브쿼리를 사용하는 것 또한 가능할 정도로 넓습니다. 이제부터 Select문에서 사용하는 서브쿼리에 집중하여 예시와 함께 안내해 드리겠습니다.

Select

EMP테이블의 DEPTNO를 통해 DEPT테이블에서 부서명을 조회하는 서브쿼리입니다. 메인쿼리의 DEPTNO 컬럼을 사용하는 correlated subquery 예시입니다.

SELECT (SELECT DNAME
          FROM DEPT
         WHERE DEPTNO = EMP.DEPTNO)
          AS DEPT_DNAME,
       EMP.*
  FROM EMP;

EMP테이블에서 각 직업별 평균을 함께 SELECT 하여 본인과 평균을 비교할 수 있도록 서브쿼리를 만들어보는 예시입니다.

SELECT (SELECT ROUND (AVG (SAL))
          FROM EMP
         WHERE JOB = EMP.JOB)
          AS AVGSALBYJOB,
       EMP.SAL,
       EMP.*
  FROM EMP;

Where

서브쿼리를 WHERE 조건과 함께 사용하는 예시입니다. 서브쿼리의 DEPT테이블을 먼저 스캔하여 얻은 결과를 외부 쿼리 조건에 이용하여 조회를 진행합니다.

SELECT *
  FROM EMP
 WHERE DEPTNO = (SELECT DEPTNO
                   FROM DEPT
                  WHERE DEPTNO = 10);

EMP테이블에서 부서명이 어카운팅, 리서치인 직원을 조회하는 서브쿼리입니다. IN 대신 EXISTS 구문을 사용해 보았습니다.

SELECT *
  FROM EMP
 WHERE EXISTS
          (SELECT NULL
             FROM DEPT
            WHERE DEPTNO = EMP.DEPTNO AND DNAME IN ('ACCOUNTING', 'RESEARCH'));

이번에는 IN을 이용하여 EMP테이블에서 최고 급여를 받는 사원을 조회해 봅니다.

SELECT *
  FROM EMP
 WHERE SAL IN (SELECT MAX (SAL) FROM EMP);

From (inline View)

FROM 절 내에서 SELECT를 하는 서브쿼리는 오라클에서 인라인 뷰(inline view)라고 지칭합니다.

SELECT *
  FROM    (SELECT *
             FROM EMP
            WHERE DEPTNO = 20) EMP_DEPTNO_20
       LEFT JOIN
          DEPT
       ON DEPT.DEPTNO = EMP_DEPTNO_20.DEPTNO;

앞서 서브쿼리의 확장성에 대해 안내해드렸듯이 FROM 내부의 인라인뷰 또한 조인 등 여러 조회 조건을 설정할 수 있습니다.

SELECT *
  FROM (SELECT *
          FROM (SELECT *
                  FROM EMP JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO));