[DB] 서브쿼리

하위 질의문으로써 전형적으로 where절에서 사용한다.
예를 들어보자.
예) 평균급여보다 많은 급여를 받는 직원의 이름과 급여를 출력하시오.
select avg(salary) from employees
를 하여 평균급여를 구한 뒤
select last_name, salary from employees where salary > 평균급여

이렇듯 하나의 질의문의 결과를 얻기 위해 두번의 질이과정을 거치고 있다. 이는 번거로운 작업니다. 이를 해결하기 위해 서브쿼리를 사용한다.

select last_name, salary from employees
where salary > (select avg(salary) from employees)
어떤 조건을 위해 where 절에 또다시 select하는 것이 서브쿼리이다.

문제) 직무별(job_id) 최대급여자의 사원내역을 출력하라.
select * from employees where (salary, job_id) in(select max(salary), job_id from employees group by job_id)
중요한 문제!
서브쿼리로 받는 값이 두개 이상이 될때는 in을 사용하여 값을 받아야한다. (=연산자로는 두개 이상의 값을 받을 수 없다.) 그리고 where절에서 salary 컬럼만으로 직무별 최대값을 구하면 같은 직무가 중복 출력되는 것을 볼 수 있다. 그 이유는 job_id를 salary의 최대값과 같이 처리해 주지 않았기 때문에 최대급여와 같은 다른 직무도 출력되는 것이다.

글만으로는 알아보기 어렵겠지만 명령문을 유심히 살펴보고 나름의 이해를 하도록 노력하자.

- update 문에서 서브쿼리 사용
문제) 20번 부서 이름을 30번 부서 이름으로 변경하여라.
update dept02 set department_name = (select department_name from dept02 where department_id = 30) where department_id = 20


문제1) student 테이블과 department 테이블을 이용하여 이윤나 학생과 1전공(deptno1)이 동일한 학생들의 이름과 전공이름을 출력하라.
select name, dname from student s, department d where s.deptno1 = d.deptno and
s.deptno1 = (select deptno1 from student where name = '이윤나')

문제2) professor 테이블에서 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하라.
select p.name, p.hiredate, d.dname from professor p, department d where p.deptno = d.deptno and p.hiredate > (select hiredate from professor where name = '송도권')

* 다중 행 sub query
in => 같은 값을 가진 다중 행 출력
all => 해당하는 서브쿼리의 범위 최대, 최소 값을 반환
any => 해당하는 서브쿼리의 값들을 모두 반환

위의 설명만으로 이해하기 어렵다. 예제를 통해서 이해해보자
문제1) emp02 테이블을 사용하여 전체 직원중 과장 직급의 최소 연봉보다 연봉이 높은 이름과 직급, 연봉을 출력하시오.
select name, position, pay from emp02 where pay > any(select pay from emp02 where position = '과장')

문제2) student 테이블을 조회하여 각 학년별로 최대 키를 가진 학생들의 학년과 이름, 키를 출력하시오.
select grade, name, height from student where height, grade in (select max(height), grade from student group by grade)