[ JOIN과 Subquery ]
2. Subquery : Subquery는 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장. Main query보다 먼저 실행됨
* select 절, from 절, where 절, order by 절, insert....... 모두에 사용 가능함
(다만, 실무상 order by 절에는 거의 사용하지 않음)
1) where 절의 subquery
(1) 기본 문법
▷ Subquery의 필요성
select e.employee_id, e.first_name, e.salary
from departments d, employees e
where d.department_id = e.department_id -- catesian product에서 조건을 검색하므로, 효율이 떨어짐
and lower(d.department_name) like lower('it');
--> departments 테이블에서 컬럼 하나만 조회하면 되는데, catesian product에서 조건을 검색하므로, 효율이 떨어짐!
▷ Subquery 기본문법 : Subquery는 괄호로 묶어야 함
- 위 문제에서 join을 쓰지 않고 employees 테이블에서만 검색하려면?
select department_id
from departments
where department_name = 'IT';
select employee_id, first_name, salary
from employees
where department_id = 60; -- IT 부서의 부서번호
- 위의 쿼리를 하나로 합치려면?
> 연산자를 기준으로 왼쪽에는 컬럼, 오른쪽에는 값이 와야하기에 에러 발생!
- 괄호를 입혀서 먼저 실행시키기 >>>>subquery 완성!
select employee_id, first_name, salary
from employees
where department_id = (select department_id
from departments
where department_name = 'IT');
select employee_id, first_name, salary
from employees
where department_id = (select department_id
from departments
where department_name = 'IT');
▷ 중첩사용
-- 'Seattle'에 근무하는 사원의 사번, 이름, 급여
select employee_id, first_name, salary
from employees
where department_id in (select department_id
from departments
where location_id = (select location_id
from locations
where lower(city) = lower('Seattle')));
> * 'Seattle'에 위치한 부서가 여러곳이기에, in 함수가 아닌 등호를 써버리면 오류 발생함!!
> * 한번에 작성하다보면 이런 부분을 놓칠 수 있으므로, 순차적으로 select 해보고 합치는 방법으로 실습할 것!!
- * 근데 이 문제 join으로도 가능한가?
select employee_id, first_name, salary
from employees
where department_id = (select department_id
from departments
where location_id = (select location_id
from locations
where lower(city) = lower('Seattle')));
>> 가능! 단, where절에 다른 테이블을 조회할 필요가 없을 경우에ㅋㅋ
(실행순서가 from > where > select 순이기에, where 절에서도 subquery를 써야해서 더 불편함)
▷ 응용문제
--1. 'Kevin'보다 급여를 많이 받는 사원의 사번, 이름, 급여
select employee_id, first_name, salary
from employees
where salary > (select salary
from employees
where lower(first_name) = lower('Kevin'));
--2. 50번 부서에 있는 사원들보다 급여를 많이 받는 사원의 사번, 이름, 급여
select employee_id, first_name, salary
from employees
where salary > all (select salary
from employees
where department_id = 50);
--3. 부서에 근무하는 모든 사원들의 평균 급여보다 많이 받는 사원의 사번, 이름, 급여
select employee_id, first_name, salary
from employees
where salary > (select round(avg(salary))
from employees
where department_id is not null);
--4. 부서번호가 20번의 평균 급여보다 크고,
-- 매니저인 사원으로 부서 번호가 20이 아닌 사원의
-- 사번, 이름, 급여, 부서번호
select distinct m.employee_id, m.first_name, m.salary, m.department_id
from employees e, (select *
from employees
where salary > (select avg(salary)
from employees
where department_id = 20
)
) m
where e.manager_id = m.employee_id
and m.department_id <> 20
order by m.employee_id;
--5. 부서번호가 20번의 평균 급여보다 크고,
-- 부서장인 사원으로 부서 번호가 20이 아닌 사원의
-- 사번, 이름, 급여, 부서번호
select e.employee_id, e.first_name, e.salary, e.department_id
from employees e, (select employee_id
from employees
where salary > (select avg(salary)
from employees
where department_id = 20)) s
where e.employee_id = s.employee_id
and e.employee_id in (select manager_id
from departments)
and e.department_id <> 20;
>>> 위 2번 문제에서 논리연산자 all 대신 max함수를 써도 값은 나옴.
단, 사용자에게 값을 받아오는 경우에는 max 함수를 쓰면 값이 다르게 나올 수도 있기에 권장되지 않음
* ?? 근데 이게 어떤 경우인지는 잘 모르겠음..
2) From 절의 subquery (Inline View)
- from절에서 원하는 데이터를 조회하여 가상의 테이블을 만들어 조인을 수행하거나 가상의 집합을 다시 조회할 때 사용함
* creat view (임시로 만드는 테이블)과는 다름
▷ 필요성
-- 지역번호가 1700인 부서에서 일하는 사원의
-- 사번, 이름, 부서번호, 부서이름
select e.employee_id, e.first_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id
and d.location_id = 1700;
> 실행순서 확인 : catesian product 160건에서 join 조건 19건 도출 거기서 일반조건 6건 도출. 효율이 떨어짐
▷ Inline View 기본문법 : from 절 안에 괄호로 묶은 select 절 사용!
- 위 쿼리의 효율성 높여보기
select e.employee_id, e.first_name, e.department_id, d.department_name
from employees e, (select department_id, department_name
from departments
where location_id = 1700) d
where e.department_id = d.department_id;
> 실행순서 : 가상의 테이블 (location_id 1700인 테이블)과 employees 테이블의 catessian product 80건에서,
join 조건을 충족하는 6건 도출
* 근데, 지금 얘기하는 효율성이 속도를 보장하는 건 아님. 속도를 보려면 실행계획(?)을 돌려봐야함
3) select 절의 subquery (Scala Subquery)
▷ 필요성
-- 20번 부서의 평균급여
-- 50번 부서의 급여총합
-- 80번 부서의 인원수
select avg(salary) from employees where department_id = 20;
select sum(salary) from employees where department_id = 50;
select count(employee_id) from employees where department_id = 80;
> 이 쿼리를 하나로 합치려면?!
▷ 기본문법
- select 절의 subquery 기본문법 : select 절에 괄호로 묶은 select 절 사용!
- 단일행, 단일컬럼만 사용가능!
select
(select avg(salary) from employees where department_id = 20) avg20,
(select sum(salary) from employees where department_id = 50) sum50,
(select count(employee_id) from employees where department_id = 80) count80
from dual;
▷ 응용문제
--1. 모든 사원의 사번, 이름, 급여, 등급, 부서이름
-- 단, A는 1등급, B는 2등급... F는 6등급으로 표현
-- 단, job_grades는 join하지 않는다
select e.employee_id 사번, e.first_name 이름, e.salary 급여,
decode( (select grade_level from job_grades where e.salary between lowest_sal and highest_sal),
'A','1등급',
'B','2등급',
'C','3등급',
'D','4등급',
'E','5등급',
'6등급') 등급, --case 대신 decode 사용 가능!
d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);
* 연관 서브쿼리
- 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태의 서브쿼리
- 메인쿼리가 먼저 수행되어 읽어온 데이터를 서브쿼리에서 조건에 맞는지 확인하고자 할 때 주로 사용 됨
- end.
.png)
.png)
