SQL-14 ) 서브쿼리(Sub Query) - 2 : SQL, SQLD, SQLD자격증, 서브 쿼리, 오라클

안녕하세요 이승혁 입니다.

 

이번 포스팅은 서브쿼리를 이어서 실습해보도록 하겠습니다.

 

※ select 문의 6가지 절에서의 서브쿼리

 

select     : scalar subquery

from      : in line view

where    : subquery

group by  ---> 서브쿼리 불가능

having    : subquery

order by  : scalar subquery

 

ㆍ select 절의 서브쿼리 - scalar subquery

 

    하나의 결과를 반환하는 서브쿼리 입니다.

 

select ename, sal, (select max(sal)
		     from emp) 최대월급,
		   (select min(sal)
		     from emp) 최소월급,
	           (select round(avg(sal))
		     from emp) 평균월급
from emp;

   ==> 악성 SQL입니다.

   emp 테이블을 4번 스캔해야 합니다.

   튜닝 후 다음과 같은 코드로 변경할 수 있습니다.

   select enmae, sal, max(sal) over() 최대월급, min(sal) over() 최소월급, round(avg(sal) over()) 평균월급  

   하지만 튜닝 후 where절을 사용할 수 없습니다.

 

   악성 SQL임에도 위와 같은 코드를 작성하는 이유는 서브쿼리 내 where절을 사용할 수 있기 때문입니다.

 

실습 1. scalar subquery 사용 , 자기가 속한 부서의 부서 평균보다 높은 월급을 가진 사람만 출력해보기

select *
from( select deptno,ename,sal,round(avg(sal) over(partition by deptno)) 부서평균
       from emp
    )
where sal>부서평균;

---

ㆍ having 절의 서브쿼리 

 

    group by 사용시 조건절인 having 절에 사용하는 subquery 입니다.

 

실습 2. having절 sub query, 직업별 토탈 월급 중 SALESMAN의 토탈 월급보다 큰 직업만 출력해보기

select job,sum(sal)
from emp
group by job
having sum(sal)>(select sum(sal)
                  from emp
                  where job='SALESMAN');
            

having절의 서브쿼리의 반환값은 SALESMAN의 토탈월급값 5600입니다.

즉 위 쿼리는 다시 보면 아래와 같습니다.

 

select job,sum(sal)
from emp
group by job
having sum(sal)>5600;
            

---

ㆍ상호 관련 서브쿼리

 

     메인 쿼리의 컬럼이 서브 쿼리 안에서 수행되는 SQL

     서브쿼리 캐싱 : select 절의 서브쿼리에서 한 번 수행한 결과를 메모리에 적재한다.

                         이후부터는 메모리에 올려놓은 데이터를 가져온다.

 

실습 2. 상호 관련 서브쿼리 사용해보기, 이름과 직업, 자기 직업의 인원수 출력해보기

select ename, job, (select count(*)
                     from emp e2
                     where e1.job = e2.job) 인원수
from emp e1;

메인쿼리의 칼럼 ( e1.job )이 서브쿼리에서 사용되고 있습니다.

 

※ where 절의 상호관련 서브쿼리

 

실습 3. 자기 직업의 인원수가 4명 이상인 것만 출력하시오

select ename,job,(select count(*)
                   from emp e2
                   where e1.job=e2.job) 인원수
from emp e1
where 4 <= (select count(*)
             from emp e2
             where e1.job=e2.job);

메인 쿼리의 칼럼이 서브쿼리 안으로 들어가게 되면 메인쿼리부터 실행합니다.

서브쿼리를 실행하는데 메인쿼리의 칼럼이 필요 -> 메인쿼리로 이동해 from 절의 테이블을 먼저 가져와야 수행 가능

 

이번 포스팅은 여기서 마쳐보도록 하겠습니다 감사합니다.