MySQL(6) - SELECT 서브쿼리

<목차>

1) SELECT 서브쿼리

   1-1) 개념

   1-2) 예제

 

 

 

 

 

1) SELECT 서브쿼리

1-1) 개념

 

 

 

 

 

 

 

 

1-2) 예제

/*
	** 서브쿼리
	서브쿼리(SubQuery)란 다른 쿼리 내부에 포함되어 있는 SELECT 문
	서브쿼리는 쿼리를 구조화시키므로, 쿼리의 각 부분을 명확히 구분
	
	** 서브쿼리 실행 조건
		- 서브쿼리는 SELECT 문으로만 작성
		- 반드시 "괄호()" 안에 작성
		- 괄호가 끝나고 끝에 ";(세미콜론)"을 쓰지 않음
		- ORDER BY 절을 사용할 수 없음
	
	** 서브쿼리 사용 가능한 곳
		SELECT, INSERT, UPDATE, DELETE
		SELECT, FROM, WHERE, HAVING, ORDER BY
*/

-- SELECT 문에 나타나는 서브쿼리 = 스칼라 서브쿼리(Scalar Subquery)
-- 다른 테이블에서 어떤 값을 가져올 때 사용
-- 일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다.

 

 

select
	employee_id,
	first_name,
	(select salary
	from employees
	where employee_id = 100) as salary
from employees;

 

 

 

-- 사원의 부서명 출력
select
	employee_id,
	first_name,
	(select department_name
	from departments d
	where e.department_id = d.department_id)
from employees e;

 

 

 

-- 부서별 평균연봉
select
	department_id,
	avg(salary)
from employees e
group by department_id;

 

 

 

-- 사원번호, 이름, 부서번호, 연봉, 부서별 평균연봉
select
	employee_id,
	first_name,
	department_id,
	salary,
	(select avg(salary)
	from employees e2
	where
		e1.department_id = e2.department_id
	group by
		department_id
	) as 'avg'
from employees e1;

 

 

 

-- FROM 문에 나타나는 서브쿼리 = 인라인 뷰(Inline View)
-- FROM 절에 사용되는 경우, 무조건 AS 별칭을 지정해주어야 한다.
-- SELECT 절의 결과를 FROM 절에서 하나의 테이블처럼 사용하고 싶을 때 사용한다.

 

-- 20번 부서에 해당하는 사원의 사원번호, 이름, 부서번호 조회
-- sub
select employee_id, first_name, department_id
from employees
where department_id = 20;

 

 

 

-- main
select employee_id, first_name, department_id
from (select employee_id, first_name, department_id
	  from employees
	  where department_id = 20) as test
;

 

 

 

-- 직업별로 급여합계 구하기
select 
	a.employee_id, a.first_name, a.job_id,
	b.job_id, b.`급여합계`, b.`인원수`
from
	employees a,
	(select
		    job_id,
		    sum(salary) as '급여합계',
		    count(*) as '인원수'
	from employees
	group by job_id
	) as b
where a.job_id = b.job_id;

 

 

 

-- 각 부서별 최대연봉
select 
	department_id, max(salary)
from employees
group by department_id;

 

 

 

-- 각 부서별 최대 연봉자 조회
select 
	e.employee_id, e.last_name, e.department_id, e.salary
from employees e,
	(select
		    department_id, max(salary) as max_sal
	from employees
	group by department_id) as d
where
	e.department_id = d.department_id
	and
	e.salary = d.max_sal
;

 

 

 

-- 중첩 서브쿼리 : WHERE 문에 나타나는 서브쿼리  -->  가장 많이 사용되는 서브쿼리이다!
select first_name, salary
from employees
where 
	salary > (select avg(salary) from employees)
;

 

 

 

-- 각 직책별 최고 급여 구하기
select job_id, max(salary)
from employees
group by job_id;

 

 

 

-- 각 직책별 최고 급여를 받는 직원들에 대해서
-- 해당 직원들의 사번, 이름, 직책, 급여를 조회하고,
-- 직책의 오름차순으로 정렬
select 
	employee_id as '사번',
	first_name as '이름',
	job_id as '직책',
	salary as '급여'
from employees
where (job_id, salary)
	   in (select job_id, max(salary)
	   	   from employees
	   	   group by job_id)
order by `직책`;

 

 

 

/*
	* 다중행 연산자
	모르는 자료를 가지고 비교하거나, 확정되지 않은 값을 가지고 비교할 때
	서브쿼리를 사용
	
	* any
	여러 개의 비교값 중 하나라도 만족하면 true 반환
	or 연산자 느낌
	
	* all
	전체 값을 비교해서 모두 만족해야 true 반환
	and 연산자 느낌
*/

 

-- any 예제
-- any는 주어진 데이터 중 어느 하나와 일치하면 전체 결과가 참으로 반환
-- 201, 202, 203, 204, 205, 206 중 최소값 이상의 값을 가지는 전체 행을 반환
-- employee_id >= 201 or employee_id >= 202 ...
select * from employees
where 
	salary >= any(select max(salary) from employees);

select * from employees
where employee_id >= any(select employee_id
						 from employees
						 where employee_id > 200);

 

 

 

-- all 예제
-- all은 주어진 데이터 중 모두를 일치해야 결과를 참으로 반환
-- 201, 202, 203, 204, 205, 206 중 최대값 이상의 값을 가지는 전체 행을 반환
-- employee_id >= 201 and employee_id >= 202 ...
select * from employees
where employee_id >= all(select employee_id
						 from employees
						 where employee_id > 200);

 

 

 

-- EXISTS
-- 서브쿼리가 반환하는 결과값이 있는지를 조사
-- 단지 반환된 행이 있는지 없는지만 보고 값이 있으면 참, 없으면 거짓
-- 한 테이블이 다른 테이블과 외래키(FK)와 같은 관계가 있을 때 유용
select * from employees e1
where exists (
				select * from job_history jh
				where e1.employee_id = jh.employee_id
			 );

 

 

 

-- 서브쿼리 예제
/*
	1. 회사 전체 평균 연봉보다 더 받는 사원들의
	   사번 및 last_name을 조회한다.
	   
	2. last_name에 u가 포함되는 사원들과 동일 부서에 근무하는
	   사원들의 사번 및 last_name을 조회한다.
	
	3. employees 테이블에서
	   직책ID(job_id)가 'IT_PROG'인 사람이 받는 최저월급보다
	   더 많은 월급을 받는 직원들의
	   직원번호, 성명(풀네임), 직책ID, 월급을 출력하기
*/

 

-- 1. 회사 전체 평균 연봉보다 더 받는 사원들의
--	  사번 및 last_name을 조회한다.
select
	employee_id,
	last_name,
	salary
from employees
where
	salary > (select
					 avg(salary)
			  from employees)
;

-- 교수님 version
select avg(salary) from employees;   -- 회사 전체 평균 연봉 조회

select
	employee_id, last_name
from
	employees
where
	salary > (select avg(salary)
			  from employees)
;

 

 

 

-- 2. last_name에 u가 포함되는 사원들과 동일 부서에 근무하는
--	  사원들의 사번 및 last_name을 조회한다.
select 
	employee_id,
	last_name
from employees
where
	department_id = any(select
					   		   department_id
			  		    from employees
			  		    where last_name like '%u%')
;

-- 교수님 version
select department_id from employees
where last_name like '%u%';

select 
	employee_id, last_name, department_id
from
	employees
where
	department_id in (select department_id
					  from employees
					  where last_name like '%u%')
;

 

 

 

-- 3. employees 테이블에서
--	  직책ID(job_id)가 'IT_PROG'인 사람이 받는 최저월급보다
--	  더 많은 월급을 받는 직원들의
--    직원번호, 성명(풀네임), 직책ID, 월급을 출력하기
select 
	employee_id,
	concat(first_name, ' ', last_name) as '성명',
	job_id,
	salary
from employees
where
	salary > (select
			         min(salary)
			  from employees
			  where job_id = 'IT_PROG')
;

-- 교수님 version
select
	min(salary)
from 
	employees
where job_id = 'IT_PROG';

select
	employee_id as '직원번호',
	concat(first_name, ' ', last_name) as '성명',
	job_id as '직책',
	salary as '월급'
from employees
where 
	salary > (select
					min(salary)
			  from
			  	    employees
			  where job_id = 'IT_PROG')
;

-- 다른 방법
select 
	salary
from employees
where job_id = 'IT_PROG';

select
	employee_id as '직원번호',
	concat(first_name, ' ', last_name) as '성명',
	job_id as '직책ID',
	salary as '월급'
from employees
where 
	salary > any(select 
						salary
                 from employees
				 where job_id = 'IT_PROG')
;