Subquery

Subquery(서브쿼리)

• 서브쿼리란 다른 쿼리 내부에 포함되어 있는 select 문을 의미한다.
• 서브 쿼리를 포함하고 있는 다른 쿼리를 외부쿼리(outer query) 또는 메인 쿼리라고 부르며, 서브 쿼리는 내부 쿼리(inner query)라고도 부른다.
• 서브 쿼리는 비교 연산자의 오른쪽에 기술해야 하고 반드시 괄호로 감싸져 있어야 한다.

- 종류

• 중첩 서브 쿼리 (Nested Subquery) - WHERE 문에 작성하는 서브 쿼리
  • 단일 행
  • 복수(다중) 행
  • 다중 컬럼
• 인라인 뷰 (inline View) - FROM 문에 작성하는 서브 쿼리
• 스칼라 서브 쿼리 (Scalar Subquery) - SELECT 문에 작성하는 서브 쿼리

 

- 주의사항

• 서브 쿼리는 반드시 () 로 감싸야 함
• 단일 행 또는 다중 행 비교 연산자와 함께 사용
• ORDER BY 사용 불가

 

- 사용이유

join 을 사용할수도 있지만 데이터에 따라 쿼리가 복잡해지건 카테시안곱(a테이블 모든 행 수 * b테이블 모든 행 수) 으로 속도저하가 올 수 있다.

---

NESTED Subquery - 단일 행

: 서브 쿼리의 결과가 단일행을 리턴

 

ex) 부서가 'seattle' (대소문자 구분) 인 부서아이디와 이름 출력

SELECT d.department_id , d.department_name 
FROM departments d 
WHERE location_id = (
                        SELECT location_id 
                        FROM locations l
                        WHERE BINARY upper(city) = upper('seattle') 
                     );

 

NESTED Subquery - 다중 행

: 서브 쿼리의 결과가 다중행을 리턴

-- 근무 도시가 ‘seattle’(대소문자 구분X)인 사원의 사번, 이름.
-- 다중행 (in)
select employee_id, first_name
from employees
where department_id in (
            select department_id
            from departments
            where location_id = (
                                    select location_id
                                    from locations
                                    where binary upper(city) = upper('seattle')
                                 )
);

 

-- 모든 사원 중 적어도(최소급여자보다) 30번 부서에서 근무하는 사원의 급여보다 많이 받는 사원의 사번, 이름, 급여, 부서번호
-- 다중행 (any)
select employee_id, first_name, salary, department_id
from employees
where salary > any (
                      select salary
                      from employees
                      where department_id = 30
                    )
order by salary;

 

-- 30번 부서에서 근무하는 모든(최대급여자보다) 사원들보다 급여를 많이 받는 사원의 사번, 이름, 급여, 부서번호.
-- 다중행 (all)
select employee_id, first_name, salary, department_id
from employees
where salary > all (
                        select salary
                        from employees
                        where department_id = 30
                    )
order by salary;

 

 

---

인라인 뷰

• from 절에 사용되는 서브 쿼리
• 뷰처럼 결과가 동적으로 생성된 테이블로 사용 가능
• 임시적인 뷰라서 저장 안 됨
• column 자유롭게 참조 가능

-- 모든 사원의 평균 급여보다 적게 받는 사원들과 같은 부서에서 근무하는 사원의 사번, 이름, 급여, 부서번호
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;

 

TopN 질의

SELECT t.*
FROM (	SELECT @rb := @rb + 1 AS rnum, a.* 
		FROM (	SELECT employee_id ,first_name, salary 
				FROM employees
				ORDER BY salary DESC ) a CROSS JOIN (SELECT @rb := 0) b 
		) t;

 

limit

-- MySQL은 limit 사용!! 급여 순 11~15등까지 출력
select employee_id, first_name, salary
from employees
order by salary desc limit 10, 5;

---

스칼라 서브 쿼리

-- 직급 아이디가 IT_PROG인 사원의 사번, 이름, 직급아이디, 부서이름
select e.employee_id, e.first_name, job_id,
	   (select department_name from departments d where e.department_id = d.department_id) department_name
from employees e
where job_id = 'IT_PROG';