[sql/oracle] 서브쿼리 Subquery 활용_ 오라클 서브쿼리 [펌]

[sql/oracle] 서브쿼리 Subquery 활용_ 오라클 서브쿼리 [펌]

오라클 서브쿼리 활용  |

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

 

 

 

문제  |

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

1) 서브쿼리로 풀어보기

?

1 2 3 4 5 6 7 8 9

--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) 스칼라 서브쿼리로 풀어보기

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

--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) 인라인뷰로 풀어보기

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

--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) 조인으로 풀어보기

?

1 2 3 4 5 6 7 8 9 10 11

--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

출처: https://viewa.tistory.com/47 [뷰에이]