서브쿼리

1. 서브쿼리

1) 서브쿼리란?

• sub-query 하나의 쿼리 안에 포함된 또 하나의 쿼리

2) 서브쿼리의 특징

• 알려지지 않은 기준을 이용한 검색에 유용
• 메인 쿼리가 실행되기 이전에 한번만 실행 : select -> sub-query -> main-query
• 한 문장에서 여러번 사용 가능 : sub-query의 sub-query의 sub-query의 sub-query....
• Ex)

select*
from employees
where salary >
(select salary from employees where first_name='정우');	// sub-query

3) 서브쿼리 사용시 주의사항

1. 서브 쿼리는 괄호와 함게 사용
2. 서브 쿼리 안에는 order by는 사용 불가
3. 서브쿼리는 연산자의 오른쪽에 사용
4. 서브쿼리는 오로지 select문으로만 작성

4) 문법

select column
from table_name
where column 연산자
(select column from table_name where column2='value');

예제

• 부서번호가 40인 부서의 급여 평균보다 높게 받는 사원들의 모든 컬럼과 나이를 조회하는 쿼리를 작성해주세요.

SELECT * ,FLOOR( (CAST(REPLACE(CURRENT_DATE,'-','') AS UNSIGNED) - 
       CAST(REPLACE(birthdate,'-','') AS UNSIGNED)) / 10000 ) as age
FROM emp
where sal >
(SELECT avg(sal)
FROM emp
where deptno = 40);

• 서브쿼리 메인쿼리를 따로 작성 후 합치는 것도 좋음

2. 반환에 따른 분류

1) 단일 행 서브쿼리

• 결과가 한 행만 나오는 서브쿼리 그리고 그 1개의 결과값을 메인쿼리로 전달하는 쿼리
• 단일 행 서브쿼리 연산자 : =, <> ,> ,>= ,< ,<=

2) 다중 행 서브쿼리

• 서브쿼리의 결과가 2개 이상을 반환하고 이를 메인쿼리로 전달하는 쿼리
• 다중 행 연산자 :
  in(하나라도 만족하면 반환),
  any(하나라도 만족하면 반환, 비교연산 가능),
  all(모두 만족하면 반환 비교연산 가능)
• < any : 최댓값 , > any : 최솟값
  > all : 최댓값 , < all : 최솟값
• Ex)
  1 in (1,2,3,4) => true
  10 < any(1,2,3,4) => 10<4 => false
  99 >= all(99,100,101) => 99 >= 101 => false

예제

• 각 부서별 급여를 제일 많이 받는 사원의 월급을 받는 사원들을 조회하는 쿼리를 작성해라.

select *
from emp
where sal = any
(select max(sal)
from emp
group by deptno);

• 각 부서별 나이가 제일 많은 사원을 조회하는 쿼리를 작성해주세요.

select *
from emp
where birthdate in
(select min(birthdate)
from emp
group by deptno);

• salaries 테이블에서 from_date가 2000-12-31 이전인 사람들의 급여 중 하나의 급여 보다 더 적은 급여를 받은 직원의 급여 정보를 모두 출력해보세요.

select *
from salaries
where salary < any
(select salary
from salaries
where from_date < '2000-12-31');

• salaries 테이블에서 from_date가 2000-12-31 이전인 사람들의 급여 중 모든 급여보다 적은 급여를 받은 직원의 급여 정보를 모두 출력해보세요.

select *
from salaries
where salary < all
(select salary
from salaries
where from_date < '2000-12-31');

3. 위치에 따른 분류

1) 스칼라 서브쿼리

• select 절에서 사용하는 서브쿼리 : select 절에 붙으면 스칼라 서브쿼리, where절에 붙으면 서브쿼리
  스칼라 서브쿼리는 오로지 한 행만 반환
  마치 join을 사용한 것과 같은 결과를 나타낸다.
• 데이터가 더 많을 때는 단순 join보단 스칼라 서브쿼리가 계산이 더 빠를 때가 있다.
• EX)

select table_name.column, (
	select column
    from table_name2
    where table_name.column = table_name2.column)
    as alist
    from table_name

문제

• salaries 테이블에서 직원 번호와 한 직원의 평균 급여를 중복없이 출력해보세요.

select distinct emp_no,
(
select avg(salary)
from salaries as a
where a.emp_no = b.emp_no
) as avg_salary

from salaries as b;