[Oracle] 오라클 서브 쿼리 종류 및 사용법, 예시 (스칼라 서브 쿼리, 인라인 뷰, 중첩 서브 쿼리)

오라클에서 쿼리를 작성할 때 서브 쿼리를 활용해야 하는 경우가 필연적으로 발생합니다.

 

처음 서브쿼리를 사용하다보면 어디에 어떻게 사용해야 할지 빠르게 감이 안 올 수 있습니다.

 

그런 고민을 가지고 있는 입문자 분들을 위해 서브쿼리의 개념과 활용방법을 알려 드리겠습니다.

 

서브 쿼리(SubQuery) 종류

 

서브 쿼리란 메인 쿼리 내부에서 ( ) 로 구분하여 별도로 작성하는 쿼리를 말합니다.

 

서브 쿼리는 용도에 따라 3가지 서브 쿼리로 분류 할 수 있습니다. 

오라클 3가지 서브 쿼리

 

• 스칼라 서브 쿼리 - SELECT 절에 사용되며 별도의 단일 컬럼에 값을 추가할 수 있습니다.
• 인라인 뷰 - FROM절에 사용되며 사용자의 입맛에 맞게 임시로 테이블을 만들어서 사용할 수 있습니다.
• 중첩 서브 쿼리 - WHERE, HAVING 절에 사용되며 조건을 별도 테이블과 조인해서 사용할 수 있습니다.

 

스칼라 서브 쿼리(Scalar SubQuery)

SELECT A.EMPNO
     , A.ENAME
     , A.JOB
     , A.SAL
     , A.DEPTNO
     , (SELECT DNAME
             FROM DEPT D
             WHERE D.DEPTNO = A.DEPTNO) AS DEPT_NM
    FROM EMP A
    WHERE A.JOB = 'CLERK'

스칼라 서브쿼리 예제1 (일반 조인)

 

위 예시는 스칼라 서브 쿼리를 사용하는 가장 보편적인 방법입니다.

해당 쿼리는 부서코드에 해당하는 부서명을 가져오는 것을 목적으로 서브 쿼리를 사용하였습니다.

이 경우 단일 행(값)이 반환 되는 경우 아우터 조인과 동일한 결과가 나옵니다.

따라서 쿼리 성능, 잡다한 오류 발생을 감안하여 아우터 조인을 사용하시는 것을 추천드립니다. 

 

SELECT A.DEPTNO
     , A.DNAME
     , A.LOCATION
     , CASE WHEN A.DEPTNO = (SELECT MAX(B.DEPTNO)
                                FROM EMP B 
                                WHERE B.JOB = 'SALESMAN') THEN 'Y' 
            ELSE 'N' END AS SALESMAN_YN
    FROM DEPT A

스칼라 서브 쿼리 예제2 (CASE WHEN 조건 안에 사용)

예시를 위해 서브 쿼리 안에 별도의 조인 조건을 주지 않았지만, 조인을 수행하여 서브쿼리에서 별도의 값을 도출한 결과를 비교해도 됩니다.

응용한다면 CASE 마다 서브쿼리를 다르게 설정하여 조건을 변경할 수 있습니다.

 

SELECT EMPNO
     , ENAME
     , SAL
     , (SELECT TRUNC(AVG(SAL)) FROM EMP B WHERE B.DEPTNO = A.DEPTNO) AS AVG_SAL
     , SAL - (SELECT TRUNC(AVG(SAL)) FROM EMP B WHERE B.DEPTNO = A.DEPTNO) AS AVG_SAL_DIFF
    FROM EMP A
    WHERE A.DEPTNO = '30'

스칼라 서브 쿼리 예제3 (집계 함수, 연산)

 

스칼라 서브쿼리는 단일 행(값)을 반환해야 하기 때문에 값이 숫자인 경우 보통 집계함수를 같이 사용합니다.

서브쿼리가 숫자를 반환하는 경우 SELECT 안에서 별도의 연산도 수행할 수 있으니 예시를 참고 바랍니다,

 

스칼라 서브 쿼리 오류(ORA-01427)

 

스칼라 서브 쿼리에 반환되는 행(값)이 2개 이상인 경우 예시와 같이 "ORA-01427" 오류가 발생합니다.

보통은 쿼리를 처음 작성할때 구문을 실수하여 반드시 1개의 행(값)이 반환되지 않기 때문에 발생하지만, 추후 테이블에 데이터가 쌓이면서 규칙에 어긋나는 데이터가 삽입 되는 경우도 있기 때문에 아우터 조인 사용을 권장합니다.

SELECT EMPNO
     , ENAME
     , SAL
     --중복 값이 동일한 경우
     , (SELECT DISTINCT JOB FROM EMP B WHERE B.DEPTNO = A.DEPTNO AND B.JOB = 'SALESMAN') AS JOB_DISTINCT
     , (SELECT JOB FROM EMP B WHERE B.DEPTNO = A.DEPTNO AND B.JOB = 'SALESMAN' AND ROWNUM = 1) AS JOB_ROWNUM
     --서로 다른 값이 들어가 있는 경우 MAX, MIN 사용 비추천
     , (SELECT MAX(JOB) FROM EMP B WHERE B.DEPTNO = A.DEPTNO AND B.JOB = 'SALESMAN' AND ROWNUM = 1) AS JOB_MAX
     , (SELECT MAX(JOB) FROM EMP B WHERE B.DEPTNO = A.DEPTNO AND B.JOB = 'SALESMAN' AND ROWNUM = 1) AS JOB_MIN
    FROM EMP A
    WHERE A.DEPTNO = '30'

스칼라 서브 쿼리 예제4 (중복 방지)

 

스칼라 서브쿼리를 꼭 사용해야 한다면 예시와 같이 중복된 행(값)이 표시되는걸 최대한 방지해주면 됩니다.

• DISTINCT로 중복된 값을 하나만 반환하기
• ROWNUM = 1 조건으로 첫번째 행만 반환하기
• 그룹함수 사용하기(문자열이 포함된 데이터는 이상하게 나올 수 있으므로 다른 방법 추천)

 

※ 스칼라 서브쿼리를 사용할 때 반드시 조인을 해서 연관성을 줘야하는건 아닙니다.

 

중복 값으로 인해 "ORA-01427" 오류만 발생하지 않는다면 활용하는건 자유입니다.

 

인라인 뷰(Inline View)

SELECT C.EMPNO
     , C.ENAME
     , C.JOB
     , C.SAL
     , D.GRADE
    FROM (
    SELECT A.EMPNO
         , A.ENAME
         , A.JOB
         , A.SAL
        FROM EMP A
           , DEPT B
        WHERE A.DEPTNO = B.DEPTNO
          AND B.DNAME = 'RESEARCH'
    ) C, SALGRADE D
    WHERE C.SAL BETWEEN D.LOSAL AND D.HISAL

인라인 뷰 예제

 

인라인 뷰는 해당 쿼리문에서만 사용하는 임시 테이블. 마치 뷰 테이블이나 WITH 절로 만든 뷰와 비슷하게 동작합니다. 

재활용적인 측면에서는 뷰 테이블 보다는 WITH절과 비슷합니다.

 

인라인 뷰는 컬럼만 잘 지정한다면 메인 쿼리의 테이블 또는 또 다른 인라인 뷰와 조인이 가능합니다.

인라인 뷰는 별도 인덱스가 없기 때문에 데이터가 많으면 쿼리문이 느려질 수 있으니 WITH 절을 사용하여 여러번 참조하는 방식을 사용할 것인지, 깔끔하게 하나의 쿼리로 만들것인지 고민해서 사용하시길 바랍니다.

 

※ 웹 페이지를 개발할 때 서버 스크립트 언어에 따라 WITH 절이 동작하지 않는 경우도 있으니 인라인 뷰 사용법을 익혀두기실 바랍니다.

 

중첩 서브 쿼리 (Nested SubQuery)

SELECT A.EMPNO
     , A.ENAME
     , A.JOB
     , A.SAL
     , A.DEPTNO
    FROM EMP A
    WHERE A.JOB = 'CLERK'
      AND A.DEPTNO = (SELECT B.DEPTNO 
                            FROM DEPT B
                            WHERE B.DNAME = 'RESEARCH')

중첩 서브 쿼리 예제1

 

위의 예제는 부서코드에 별도의 값을 대입하지 않고 서브 쿼리로 값을 대입하는 예시입니다.

부서명(DNAME)이 'RESEARCH' 인 부서코드(DEPTNO)를 대입하게 됩니다.

조건에 따라 다중 행이 대입되는 경우 아래와 같이 IN 을 사용하면 됩니다.

 

SELECT A.EMPNO
     , A.ENAME
     , A.JOB
     , A.SAL
     , A.DEPTNO
    FROM EMP A
    WHERE A.JOB = 'CLERK'
      AND A.DEPTNO IN (SELECT B.DEPTNO 
                            FROM DEPT B
                            WHERE B.LOCATION IN ('NEW YORK','CHICAGO'))

중첩 서브 쿼리 예제2

SELECT A.EMPNO
     , A.ENAME
     , A.JOB
     , A.SAL
     , A.DEPTNO
    FROM EMP A
    WHERE A.DEPTNO IN ('20','30')
      AND 3 = (SELECT GRADE 
                    FROM SALGRADE B 
                    WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL)

중첩 서브 쿼리 예제3

 

중첩 서브쿼리를 사용할 때 무조건 컬럼 값과 매칭하지 않고, 위 예시와 같이 특정 값을 명시해서 사용할 수 있습니다.

 

SELECT A.EMPNO
     , A.ENAME
     , A.JOB
     , A.SAL
     , A.DEPTNO
    FROM EMP A
    WHERE A.JOB = 'CLERK'
      AND NOT EXISTS (SELECT * 
                            FROM DEPT B
                            WHERE B.DNAME = 'SALES'
                              AND A.DEPTNO = B.DEPTNO)

중첩 서브 쿼리 예제4

 

중첩 서브쿼리에  IN 을 사용하여 다중 조건을 주는 경우 비교할 데이터가 많다면 속도가 저하 됩니다.

이 경우 IN, NOT IN 보다는 EXISTS, NOT EXISTS 를 사용하면 빠르게 특정 조건에 대한 포함 여부를 확인할 수 있습니다.

 

※ EXISTS, NOT EXISTS 를 사용하는 경우 확실하게 조인 수행 필요, 부등호 연산자 사용 시 조건 꼼꼼하게 확인!!