오라클 SQL(서브쿼리1)

서브쿼리

연관성 없는 서브쿼리

--연관성 없는 서브 쿼리
--단일행
select count(*) 
  from emp
 where salary >= (select avg(salary) from emp);

--다중행
select count(*) 
  from employees
 where department_id in(select department_id
                         from departments
                        where parent_id is null)

select employee_id, emp_name, job_id
  from employees
 where (employee_id, job_id) 
    in (select employee_id, job_id
          from job_history);

연관성 있는 서브 쿼리

--연관성 있는 서브 쿼리
select a.department_id, a.department_name
  from departments a
 where exists(select 1 from job_history b
                      where a.department_id = b.department_id);

select a.employee_id,
       (select b.emp_name
          from employees b
         where a.employee_id = b.employee_id) as emp_name,
       a.department_id,
       (select b.department_name
          from departments b
         where a.department_id = b.department_id)as dep_name
  from job_history a;

select a.department_id, a.department_name
  from departments a
 where exists (select 1 
                 from employees b
                where a.department_id = b.department_id
                  and b.salary >(select avg(salary)
                                   from employees));

select department_id , avg(salary)
  from employees a
 where department_id in (select department_id
                           from departments
                          where parent_id = 90)
group by department_id;

인라인 뷰

-from절에 사용하는 서브쿼리를 인라인뷰라고 한다.

-서브쿼리를 from절에 사용해 하나의 테이블이나 뷰처럼 사용

select a.emp_id, a.emp_name, b.department_id, b.department_name
  from emp a, departments b,
  (select avg(c.salary) as avg_salary
   from departments b,
        employees c 
  where b.parent_id =90
    and b.department_id = c.department_id)d
where a.dept_id = b.department_id 
  and a.salary > d.avg_salary

복잡한 쿼리 작성 방법

1. 최종적으로 조회되는 결과 항목을 정의한다.

2. 필요한 테이블과 컬럼을 파악한다.

3. 작은 단위로 분할해서 쿼리를 작성한다.

4. 분할한 단위의 쿼리를 하나로 합쳐 최종 결과를 산출한다.

5. 결과를 검증한다.