서브 쿼리(Subquery)
•
서브 쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미한다
•
서브 쿼리를 포함하고 있는 쿼리를 외부 쿼리(outer query)또는 메인 쿼리라고 부르며,
서브 쿼리는 내부 쿼리(inner query)라고도 부른다.
•
서브 쿼리는 비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호(())로 감싸져 있어야만 한다
서브 쿼리의 종류
•
중첩 서브 쿼리(Nested Subquery) - WHERE 문에 작성하는 서브 쿼리
1.
단일 행 (=, >, < ,etc)
2.
복수(다중) 행 (in, any, all)
3.
다중 컬럼
•
인라인 뷰(Inline View) - FROM 문에 작성하는 서브 쿼리
•
스칼라 서브 쿼리(Scalar Subquery) - SELECT 문에 작성하는 서브 쿼리
서브 쿼리(Subquery)
•
주의사항
◦
서브 쿼리는 반드시 ( ) 로 감싸야 한다.
◦
서브 쿼리는 단일 행 또는 다중 행 비교 연산자와 함께 사용된다.
•
서브 쿼리가 사용 가능한 곳
◦
SELECT
◦
FROM
◦
WHERE
◦
HAVING
◦
ORDER BY
◦
INSERT 문의 VALUES
◦
UPDATE 문의 SET
서브 쿼리의 종류 - Nested Subquery
•
사번이 100인 사원의 부서 이름
출력해야 할 결과인 department_name은 departments table에 존재하지만, 지문상 알 수 있는 data인 employee_id는 employees table에 존재한다.
서브 쿼리를 통해 해결하거나 join을 이용해서 해결할 수 있다.
join의 경우 쿼리가 복잡해 지거나 카테시안곱으로 인한 속도 저하가 올 수 있다.
(But 일반적으로는 join이 서브쿼리보다 빠르다 → 서브쿼리는 추가적인 도구)
// 조인
select d.department_name
from employees e join departments d
on e.department_id = d.department_id
where e.employee_id = 100;
// 서브쿼리
select department_name
from departments
where department_id = (
select department_id
from employees
where employee_id = 100
);
SQL
복사
서브 쿼리 종류 - 인라인 뷰(Inline View)
•
FROM 절에 사용되는 서브 쿼리를 인라인 뷰(Inline View)라 한다.
•
서브 쿼리가 FROM절에 사용되면 뷰(View)처럼 결과가 동적으로 생성된 테이블로 사용 가능
•
임시적인 뷰이기 때문에 데이터베이스에는 저장되지 않는다.
•
동적으로 생성된 테이블이기 때문에 column을 자유롭게 참조 가능
-- 인라인뷰(Inline View) -> from 절에 붙이는 서브쿼리
-- 모든 사원의 평균 급여보다 적게 받는 사원들과
-- 같은 부서에서 근무하는 사원의 사번, 이름, 급여, 부서번호
select e.employee_id, e.first_name, e.salary, e.department_id
from (
select distinct department_id
from employees
where salary < (select avg(salary) from employees)
) d join employees e
on d.department_id = e.department_id;
SQL
복사
•
활용 - TopN 질의, limit
-- TopN 질의
-- 모든 사원의 사번, 이름, 급여를 출력.(단 아래의 조건 참조)
-- 1. 사원 정보를 급여순으로 정렬.
-- 2. 한 페이지당 5명이 출력.
-- 3. 현재페이지가 3페이지라고 가정. (급여 순 11등 ~ 15등까지 출력)
set @pageno = 3; -- 변수 설정
select b.rn, b.employee_id, b.first_name, b.salary
from (
select @rownum := @rownum + 1 as rn, a.*
from (
select employee_id, first_name, salary
from employees
order by salary desc
) a, (select @rownum := 0) tmp -- join키워드 생략, 카테시안 곱으로 tmp 테이블 생성
) b
where b.rn > (@pageno * 5 - 5) and b.rn <= (@pageno * 5);
-- MySQL은 limit로 해결.
select employee_id, first_name, salary
from employees
order by salary desc limit 10, 5;
select a.*
from (
select @rownum := @rownum + 1 as rn, employee_id, first_name, salary
from employees e, (select @rownum := 0) tmp
order by salary desc
) a limit 10, 5;
SQL
복사
서브 쿼리 - Scalar Subquery
•
메인 쿼리에서 만들어진 값을 서브 쿼리에서 활용할 수 있음
◦
하지만 성능적인 측면에서는 좋지 않아서 잘 활용 X
-- 부서번호가 50인 부서의 총급여, 60인 부서의 평균급여,
-- 90인 부서의 최고급여, 90인 부서의 최저급여
select
(select sum(salary) from employees where department_id = 50) sum50,
(select avg(salary) from employees where department_id = 60) avg60,
(select max(salary) from employees where department_id = 90) max90,
(select min(salary) from employees where department_id = 90) min90
from dual; -- 컬럼으로 뽑아내기
SQL
복사
서브 쿼리의 활용
•
서브 쿼리를 이용하여 create, insert, update, delete 작업을 수행할 수 있다.
-- 서브쿼리를 이용한 create.
-- employees table을 emp_copy라는 이름으로 복사(컬럼 이름 동일).
create table emp_copy
select * from employees;
-- employees table의 구조만 emp_blank라는 이름으로 생성(컬럼 이름 동일).
create table emp_blank
select * from employees
where 1 = 0;
-- 50번 부서의 사번(eid), 이름(name), 급여(sal), 부서번호(did)만 emp50이라는 이름으로 생성.
create table emp50
select employee_id eid, first_name name, salary sal, department_id did
from employees
where department_id = 50;
-- 서브쿼리를 이용한 insert.
-- employees table에서 부서번호가 80인 사원의 모든 정보를 emp_blank에 insert
insert into emp_blank
select * from employees
where department_id = 80;
-- 서브쿼리를 이용한 update.
-- employees table의 모든 사원의 평균 급여보다 적게 받는 emp50 table의 사원의 급여를 500 인상.
update emp50
set sal = sal + 500
where sal < (select avg(salary) from employees);
-- 서브쿼리를 이용한 delete.
-- employees table의 모든 사원의 평균 급여보다 적게 받는 emp50 table의 사원은 퇴사.
delete from emp50
where sal < (select avg(salary) from employees);
SQL
복사
