#9 9일차_서브쿼리 : 인라인뷰, 스칼라 서브쿼리

9일차에 배운 내용을 정리하였다.

 목차는 다음과 같다. :

 

01 인라인뷰

02. 스칼라 서브쿼리

---

 

01 인라인뷰

 from 절에 사용하는 서브쿼리로, 가상의 테이블처럼 사용한다.

 

예) emp 테이블을 사용하여 각 부서별 최대급여자의 이름, 부서번호, 급여를 출력

 

 1) 다중컬럼 서브쿼리

 - 비연관 서브쿼리 형태로 작성

 - 서브쿼리를 비연관성 서브쿼리로 작성하고, 해당 서브쿼리 결과가 상수의 형태로 메인쿼리에 반영

 - 서브쿼리를 통해 미리 구해둔 상수와 일치하는 행을 조회

select deptno, max(sal)
 from emp 
group by deptno;
                     
select ename, deptno, sal
  from emp
 where (deptno, sal) in (select deptno, max(sal)
                          from emp 
                         group by deptno);

2)  연관 서브쿼리

 - 비연관 서브쿼리 형태로 작성

 - 각 행마다 원하는 정보를 비교하며 원하는 조건에 부합하는 행을 조회

select e1.ename, e1.deptno, e1.sal
  from emp e1
 where e1.sal = (select max(e2.sal)
                   from emp e2
                  where e2.deptno = e1.deptno);
                 
3)  인라인뷰

 - 비연관 서브쿼리 형태로 작성

 - 가상의 테이블

 - 원본 테이블을 1차, 2차로 계속해서 가공하여 한 테이블 안에서 확인해야 할 때 사용

select e.ename, e.deptno, e.sal
  from emp e, (select deptno, max(sal) as max_sal
                          from emp 
                         group by deptno) i
  where  e.deptno = i.deptno
    and e.sal = i.max_sal;

 

 

 결과는 모두 같으나 사용 목적에 따른 차이가 있다.

 

예) professor 테이블을 사용하여 직급(position)별 평균 급여 이상의 급여를 받는 교수의 이름, 직급, 급여를 출력하되 연관서브쿼리, 인라인뷰 2개의 형태로 쿼리를 작성

 

 1) 연관서브쿼리

 - avg(pay)의 출력은 불가능하다.

 

select p1.name, p1.position, p1.pay 
  from professor p1
 where p1.pay >= (select avg(p2.pay)
                    from professor p2
                   where p2.position = p1.position);

 

 

 2) 인라인뷰

 - avg(pay)의 출력이 가능하다.

 

select p.name, p.position, p.pay, i.avg_pay
  from professor p, (select position, avg(pay) as avg_pay
                       from professor 
                      group by position) i
 where p.position = i.position
   and p.pay >= i.avg_pay;

---

02. 스칼라 서브쿼리

 select절에 사용하는 서브쿼리로 최종 출력시 원하는 대상을 가상컬럼의 형태로 표현하기 위해 사용한다.

 반드시 각 행마다 하나의 값만 출력을 진행하는 단일행 서브쿼리 형태로만 전달이 가능하다.

 

 스칼라 서브쿼리는 행마다 필요한 것을 가져오기 위한 연관서브쿼리 형태로 작성하며, 인라인뷰는 비연관 서브쿼리로 먼저 실행 후 테이블로 고정하고 양측을 비교하여 원하는 값을 출력한다.

 

 예) emp 테이블을 사용하여 각 직원의 사번, 이름, 급여, 부서번호와 함꼐 전체급여평균을 함께 출력

 

select empno, ename, sal, deptno, (select avg(sal) 
                                     from emp) as avg_sal
  from emp;

 

 

 한 테이블에 sal 과 avg(sal)이 함께 출력되었음을 확인할 수 있다.

 

▼ 동일 결과를 인라인뷰로 확인하였을 시

select empno, ename, sal, deptno, avg_sal
  from emp, (select avg(sal) avg_sal from emp);

 

 예) emp 테이블을 사용하여 해당 직원이 소속한 부서의 평균급여와 함께 직원의 사번, 이름, 급여, 부서번호,  소속부서별 평균급여 출력

 

select empno, ename, sal, deptno, (select avg(sal) 
                                     from emp   
                                    where e1.deptno = deptno)
  from emp e1;

 

 

▼ 동일 결과를 인라인뷰로 확인하였을 시

select e1.empno, e1.ename, e1.sal, e1.deptno, i.avg_sal
  from emp e1, (select deptno, avg(sal) as avg_sal
                  from emp 
                 group by deptno) i
  where e1.deptno = i.deptno
  order by 1;

 또한 스칼라 서브쿼리는 outer join 을 신경쓰지 않아도 된다.

메인쿼리 내 where 절은 지정한 조건에 대해 부합하는 행만 필터링하기 때문에 outer join (+) 표시가 필요하나, 스칼라 서브쿼리는 처리 순서상 특정 조건에 대한 필터링을 수행하지 않으며, 메인쿼리 내에서도 특정 조건을 지정하지 않는 한 모든 데이터가 출력된다.

 

 예) student 테이블을 사용하며, 학번, 이름, 학년, 지도교수를 출력 (단, 지도교수가 존재하지 않는 학생까지 모두 출력)

 

select s.studno, s.name, s.grade, (select p.name 
                                     from professor p 
                                    where s.profno = p.PROFNO) as 교수이름
  from student s;

 

 

 

▼ 동일 결과를 outer join을 이용하여 확인하였을 시 

select s.studno, s.name, s.grade, p.name
  from student s, professor p 
 where s.profno = p.profno(+);