SQL 기초 : 다중 컬럼 서브쿼리 : 중첩서브쿼리와 상호연관 서브쿼리, 그리고 인라인 뷰 (Nested subquery, Correlated subquery, Inline view)

오늘은 다중 컬럼 서브쿼리 중 중첩서브쿼리(Nested subquery)와
인라인 뷰를 활용한 서브쿼리를 알아보고자한다
아래의 예제를 통해 확인해보자

예제 emp 테이블에서 부서별 최대 연봉자의 정보출력
step 1 부서별 최대연봉 출력

select deptno, max(sal)
  from EMP
 group by deptno;

step 2 위 정보 확인 후 그에 맞는 데이터 값을 조건으로 색인하기

select *
  from EMP
 where deptno = 30
   and sal = 2850;

이 과정을 하나로 단축 시켜주는 것이 다중컬럼 서브쿼리이다.
서브쿼리의 결과가 여러 컬럼의 데이터를 출력해주는 서브쿼리를 다중컬럼 서브쿼리라고 하는데
위의 경우에는 아래와 같이 두 컬럼의 결과를 모두 활용할 수 있다.

select *
  from EMP
 where (deptno, sal) in (select deptno, max(sal)
                           from EMP
                          group by deptno);

위와 같이 각 부서의 최고 연봉자의 정보를 출력하는 것이 가능하다.
아래와 같이 다중 컬럼 서브쿼리를 인라인 뷰 방식으로 활용하여 풀이할 수도 있다.

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

다중컬럼 서브쿼리를 from 절에 넣어 조인한 후 각 테이블의 컬럼을 활용해 조건을 건 후 색인.

서브쿼리 연습문제

Q1. professor 테이블에서 심슨과 입사년도가 같으면서
조인형의 pay보다 적게받는 교수의 이름, pay, 입사일을 구하여라

select name, pay, hiredate
  from PROFESSOR
 where to_char(hiredate, 'YYYY') = (select to_char(hiredate, 'YYYY')
                                      from professor
                                      where name = '심슨')
   and pay < (select pay
                from professor
               where name = '조인형');

to_char 함수를 이용해 hiredate 컬럼의 년도만 가져와서 비교.

Q2. emp 테이블에서 이름이 S로 시작하는 사원보다
연봉(sal)이 많은 사원의 이름과 직업, sal을 출력.
case 1) any

select ename, job, SAL
  from emp
 where sal > (select min(sal)
                from EMP
               where ename like 'S%');

case 2) all

select ename, job, SAL
  from emp
 where sal > (select max(sal)
                from EMP
               where ename like 'S%');

Q3. Student 테이블을 조회하여 각 학년별로 최대키를 가진
학생들의 학년과 이름과 키를 출력하세요.

sol 1) nested subquery

select grade, name, height
  from STUDENT
 where (grade, height) in (select grade, max(height)
                             from STUDENT
                            group by grade);

sol 2) 인라인뷰

select s.grade, name, height
  from STUDENT s, (select grade, max(height) as MH
                   from student
                  group by grade) g
 where s.grade = g.grade
   and s.height = MH;

각 학년 별 최대 키를 가진 학생 출력

Q4. emp2 테이블에서 각 지역별 직원의 평균연봉보다
더 많은 연봉을 받는 직원의 이름, 고용형태, 지역, pay,
직급을 구하여라.

select  name, 
        emp_type, 
        substr(tel, 1, instr(tel, ')')-1) 지역,
        pay,
        position
   from emp2 e, 
        (select substr(tel, 1, instr(tel, ')')-1) as loc, 
                avg(pay) as avp
           from EMP2
          group by substr(tel, 1, instr(tel, ')')-1)) l
  where e.pay > l.avp
    and substr(e.tel, 1, instr(e.tel, ')') -1) = l.loc;

아래는 ANSI 표준을 활용한 풀이.

select  name, 
        emp_type, 
        substr(tel, 1, instr(tel, ')')-1) 지역,
        pay,
        position
   from emp2 e join 
        (select substr(tel, 1, instr(tel, ')')-1) as loc, 
                avg(pay) as avp
           from EMP2
          group by substr(tel, 1, instr(tel, ')')-1)) l
     on e.pay > l.avp
    and substr(e.tel, 1, instr(e.tel, ')') -1) = l.loc;  

다중컬럼 서브쿼리의 상호연관 서브쿼리 이용

상호연관 서브쿼리란 where 절에 있는 서브쿼리의 결과를 부모 쿼리의 결과와 비교하여 활용하는 서브쿼리를 말한다.
서브쿼리가 where 절에 위치한 nested 서브쿼리의 개념 중 하나이다.

예제) professor 테이블에서 소속학과별 최대 연봉을 받는
교수 정보 출력(상호연관서브쿼리로)

select *
  from PROFESSOR p1
 where p1.pay = (select max(pay)
                   from PROFESSOR p2
                  where p1.DEPTNO = p2.DEPTNO)

이와 같이 where 절에서 자식 테이블이 부모 테이블의 값을 참조하는
중첩 서브쿼리를 상호연관 서브쿼리라고 이른다.
상호연관 서브쿼리의 실행 순서는 다음과 같다.

[ 상호연관 서브쿼리 실행순서 ]

1. 조인형의 pay 확인(550)
2. 서브쿼리에서 조인형의 deptno 요구(101)
3. 서브쿼리에서 101번 학과의 max(pay) 구함
4. 1.550과 3.550 조건일치로 첫번째 행 리턴
5. 박승곤의 pay 확인(380)
6. 서브쿼리에서 박승곤의 deptno 요구(101)
7. 서브쿼리에서 101번 학과의 max(pay) 구함
8. 5.380과 7.550 불일치로 노 리턴
   ... 나머지 행에 대해 반복

위의 과정을 보면 알 수 있듯 상호연관 서브쿼리는
스캔을 반복하므로 성능상 그다지 좋지는 않다.

연습문제) emp 테이블에서 각 부서별 평균연봉보다
낮은 연봉을 받는 직원의 정보 출력

select *
  from EMP e1
  where sal < (select avg(sal)
                     from emp e2
                    where e1.deptno = e2.deptno);

--연습문제) emp2 테이블에서 고용타입별 평균연봉을 구하고
--해당 연봉보다 높은 직원의 이름, 부서명, 고용타입, pay를 출력

select e.name, dname, e.emp_type, e.pay
  from emp2 e, dept2 d
 where pay > (select avg(pay) 
                from emp2 e2
               where e.emp_type = e2.emp_type
                 )
  and e.deptno = d.dcode;

다음 글에서는 스칼라 서브쿼리에 대해 다뤄보도록 하겠다.