[ORACLE] 서브쿼리 Subquery 활용_ 오라클 서브쿼리

[ORACLE] 서브쿼리 Subquery 활용_ 오라클 서브쿼리

오라클 서브쿼리 활용  |

다양한 서브쿼리 활용하여 문제를 풀어보려고 합니다. 이전 글에서 서브쿼리가 무엇이고 어떤 종류가 있는지 살펴봤습니다. 그럼 좀 더 실제적으로 이 서브쿼리가 어떻게 사용되는지를 알아보려고 합니다. 한 가지 문제이지만 다양한 서브쿼리를 통해서 여러가지 쿼리문을 작성해보면서 어떤 차이가 있는지 또 결과는 어떠한지 보겠습니다. 

문제  |

1. 부서별로 평균 급여보다 높은 급여를 받는 사원은?

1) 서브쿼리로 풀어보기

--1) 서브쿼리로 풀어보기
  SELECT  A.ENAME, A.SAL
    FROM  EMP A
   WHERE  A.SAL > (
                    SELECT  TRUNC(AVG(SAL))AS 평균급여
                      FROM  EMP B
                     WHERE  A.DEPTNO = B.DEPTNO
                  GROUP BY  DEPTNO
                  );

2) 스칼라 서브쿼리로 풀어보기

--2) 스칼라 서브쿼리로 풀어보기             
  SELECT  A.ENAME,
          A.DEPTNO, 
          A.SAL,
          (
              SELECT  TRUNC(AVG(Z.SAL))
                FROM  EMP Z
               WHERE  A.DEPTNO = Z.DEPTNO
            GROUP BY  Z.DEPTNO
          )  "AVG2"  
    FROM  EMP A
   WHERE  A.SAL > (
                      SELECT  AVG(Z.SAL)
                        FROM  EMP Z
                       WHERE  A.DEPTNO = Z.DEPTNO
                    GROUP BY  Z.DEPTNO
                  );

3) 인라인뷰로 풀어보기

--3) 인라인뷰 서브쿼리로 풀어보기
  SELECT  *
    FROM  (
            SELECT  A.ENAME, 
                    A.DEPTNO, 
                    A.SAL,
                    (
                        SELECT  ROUND(AVG(B.SAL))
                          FROM  EMP B
                         WHERE  A.DEPTNO = B.DEPTNO
                      GROUP BY  B.DEPTNO
                    ) AS AVG2
              FROM  EMP A
            ) C
   WHERE  C.SAL > C.AVG2;

4) 조인으로 풀어보기

--4) 조인으로 풀어보기
  SELECT  A.ENAME, A.SAL, A.DEPTNO
    FROM  EMP A
    LEFT
    JOIN  (
              SELECT  TRUNC(AVG(Z.SAL)) AS SALAVG, Z.DEPTNO
                FROM  EMP Z
            GROUP BY  Z.DEPTNO
          ) B
      ON  A.DEPTNO = B.DEPTNO
   WHERE  A.SAL > B.SALAVG;

결과

다 결론은 같습니다. 정답은 다양합니다. 내가 원하는 결과를 이끌어내기 위해서 다양한 방향에서 쿼리를 작성하는 연습을 해봐야합니다. 어떻게 쿼리가 작동하는지 그림을 보며 하나씩 이해해보겠습니다.

해설  |

1. 기본 서브쿼리로 풀어보기

여기서는 WHERE절에 주목해야 한다. 메인쿼리와 서브쿼리의 공통된 부분을 찾아서 꼭 매칭을 시켜줘야합니다. 잊지말자!

2. 스칼라 서브쿼리로 풀어보기

억지로 풀어 본 것 입니다. 이는 단순 서브쿼리로만 풀 수 있지만, 이해를 위해 굳이 스칼라 서브쿼리를 적용하여보려 한 것이니 참고만 하시면 됩니다. 

SELECT 절에 서브쿼리를 사용한 것이 스칼라 서브쿼리입니다. 이때, 조건에도 동일하게 서브쿼리를 작성한 것을 볼 수 있습니다. 이는 옵티마이저의 쿼리 실행순서와 관계가 있습니다. FROM > WHERE > SELECT 순으로 수행하기 때문에 SELECT 절에 있는 컬럼을 가져다 쓸 수 없어서 조건에도 작성해 준 것 입니다. 

3. 인라인 뷰 서브쿼리로 풀어보기

서브쿼리를 2번 사용한 것을 볼 수 있습니다. 서브쿼리안에 서브쿼리가 들어가 있습니다. 이를 다중서브쿼리라고도 합니다. 먼저 평균까지 구해진 테이블 서브쿼리1(빨강박스)를 사용해서 만듭니다. 그리고 그 서브쿼리2(초록박스)를 다시 인라인 뷰로 만들어서 "C"라는 테이블로 명명했습니다. 

그럼 C 테이블(초록박스)에 있는 컬럼들이 사용가능한 것을 볼 수 있다. 위 스칼라 서브쿼리에서는 WHERE절에서 바로, 급여와 평균급여를 비교하지 못했는데, 이제 가능해졌습니다! 

4. 조인으로 풀어보자

평균급여가 포함된 테이블을 만들기 위해서 조인을 했습니다. 이때, 조인에 작성된 테이블을 "B"(빨강박스)라 명명했습니다. 조건에서 A 테이블의 컬럼과 B 테이블에서 나온 컬럼들을 서로 비교할 수 있게 됩니다.

여러테이블을 조인하여 작성하다보면 서브쿼리를 쓸 일이 참 많아집니다. 단 하나의 컬럼을 추가하기 위해서 조인을 하는 것보다 스칼라 서브쿼리를 활용하면 더 빠르고 쉽게 쿼리문을 작성할 수 있게 됩니다. 저도 아직 배워나가는 단계라서 더 많은 것을 더 상세하게 설명하지 못해 아쉽지만, 차곡차곡 쌓아가다보면 이 과정들이 분명 멋진 디딤판이 될 것을 생각하면서 꾸준하게 블로깅 해보겠습니다. 

모두 응원합니다! :0