서브쿼리
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) 서브쿼리 사용시 주의사항
- 서브 쿼리는 괄호와 함게 사용
- 서브 쿼리 안에는 order by는 사용 불가
- 서브쿼리는 연산자의 오른쪽에 사용
- 서브쿼리는 오로지 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;