Subquery2: Inlineview, Scalar Subquery, Correlated Subquery

위치에 따라 다르게 불리는 서브쿼리

• WHERE 중첩 서브쿼리
• FROM 인라인 뷰
• SELECT 스칼라 서브쿼리

inlineview

from절에 sub쿼리가 사용된 경우 쓴다.
직급이 사원인 사람들의 이름과 급여를 구하시오.

SELECT emp.last_name, emp.salary
FROM ( SELECT * 
	FROM employee AS e
	WHERE e.office_worker='사원') emp

Scalar Subquery 스칼라서브쿼리

• SELECT 문에 나타나는 서브쿼리
• 규모가 제일 작은 중첩서브쿼리유형
• 한 행에서 정확히 한 행에서 정확히 열 값을 반환.
• 단일행 서브쿼리 + 단일컬럼 서브쿼리
• 사용처
  • decode및 case조건 및 표현식 부분
  • group by제외한 모든 select절
  • update문의 set절

//departments테이블에서 department_id, department_name, 각부서별 사원수를 출력하는 쿼리
select department_id, department_name, (select count(*)
			from employees e
			where e.department_id = d.department_id) as emp_c
from departments d
order by department_id;

Correlated Subquery 상호관련서브쿼리

• 내부 Subquery에서 외부테이블의 값을 참조할 때 사용.
• 일반 서브쿼리와 달리 서브쿼리만 따로 돌릴 수 없음.
• 실행순서 : 메인쿼리에서 만들어진 테이블의 행의 수만큼 후보 행을 하나씩 가져옴 -> 후보행 하나를 사용해서 inner쿼리를 실행 -> 메인 쿼리의 후보행을 다 돌때까지 반복실행 후 결과 출력.
• 단점 : 성능적으로 유리한 문법이 아님.
  • 따라서 상호관련서브쿼리말고 다른 문법으로 쓸 수 있다면 그걸 쓰면 됨.

예제1 : 자신의 부서의 평균 급여보다 많은 급여를 받는 사원을 모두 찾을때.

- 인라인뷰로도 출력가능

//상호관련서브쿼리사용
select last_name, salary, department_id
from employees outer
where salary > (select avg(salary)
    from employees inner
    where inner.department_id = outer.department_id);


//내코드 : 왜 계속 에러가 뜰까했는데 메인쿼리의 select부분이 e1테이블인지 e2테이블인지 모호해서 에러가 났다. 따라서 아래 강사님코드처럼 어디서 가져올지 테이블을 확실히 정해줘야한다.
select last_name, salary, department_id
from (select avg(salary), department_id
            from employees
            group by department_id) e1
join employees e2
on(e1.department_id = e2.department_id)
where e2.salary >e1.avg;

//강사님코드 (인라인뷰사용)
select e1.last_name, e1.salary, e1.department_id
from employees e1
join (select department_id, avg(salary) avgsal
            from employees
            group by department_id) e2
on (e1.department_id = e2.department_id)
where e1.salary > e2.avgsal;

예제2 : 각 부서별 최대 연봉을 받는 사원의 employee_id를 출력하는 쿼리

스스로 궁금해서 다른 쿼리구문으로 작성가능 한지 확인해보았다
상호관련서브쿼리를 바꾸기 위해서 첫번째로 서브쿼리를 먼저 만들어야한다.

//상호관련서브쿼리사용
select department_id, employee_id, salary
from employees e
where 1 = (select count(distinct salary)
            from employees
            where e.department_id = department_id
            and e.salary <= salary)
order by department_id;


//내코드 : 인라인뷰사용
//1. 부서별 최대 연봉 출력
select department_id, max(salary)
from employees 
group by department_id
order by department_id;

//2. 부서별 최대 연봉을 받는 직원의 employee_id출력
select e1.department_id, e1.employee_id, e1.salary
from employees e1
join (select department_id, max(salary) maxsal
        from employees 
        group by department_id) e2
on e1.salary = e2.maxsal 
where e1.department_id = e2.department_id
order by department_id;