[23.01.02] 50일차 [데이터베이스]

서브쿼리

group by절 제외하고 서브쿼리 다 사용가능.

select last_name, job_id, salary
from employees
where salary =
(select  min(salary) from employees);

select department_id, min(salary)
from employees
group by department_id
having min(salary) >
(select min(salary)
from employees
where department_id = 50);

Inline View - From절에 Subquery가 작성된 경우

select a.last_name, a.salary, a.department_id, b.salavg
from employees a join 
(select department_id, avg(salary) salavg
from employees
group by department_id) b
on a.department_id = b.department_id
and a.salary > b.salavg;

select a, last_name, a.salary, a.department_id, b.salavg
from employees a join 
(select department_id, avg(salary) salavg
from employees
group by department_id) b
on a.department_id = b.department_id
and a.salary > b.salavg;

고급 Subquery 유형

서브쿼리 유형

• 단일행서브쿼리 + 단일컬럼서브쿼리
  단일행서브쿼리 + 다중컬럼서브쿼리
  다중행서브쿼리 + 단일컬럼서브쿼리
  다중행서브쿼리 + 다중컬럼서브쿼리

단일행 서브쿼리

• 서브쿼리로부터 단일 행이 반환되는 유형
• 메인 쿼리에 단일행 비교연산자 사용하기
• 단일행 비교연산자 : =, >, >=, <, <=, <>, !=
• 단일행 서브쿼리로부터 null값이 반환되는 경우 메인쿼리 결과도 null이다.

select last_name, job_id, salary
from employees
where job_id =
(select job_id
from employees
where last_name = 'Bell')
and salary>
(select salary
from employees
where last_name = 'Bell');

select employee_id, last_name
from employees
where salary =
(select min(salary)
from employees
group by department_id);

- 서브쿼리에 다중행 반환되어 오류남
- 단일행 비교연산자 =을 다중행 비교연산자 in으로 변경 하면 됨

select employee_id, last_name
from employees
where salary in
(select min(salary)
from employees
group by department_id);

select last_name , job_id
from employees
where job_id =
(select job_id
from employees
where last_name ='Haas');

Haas라는 직원 없음. null값 넘어감

다중행 서브쿼리

• 서브쿼리로부터 다중 행이 반환되는 유형
• 메인 쿼리에 다중행 비교 연산자 사용하기
• 다중행 비교연산자 : in, not in, any, all

다중행 비교연산자	의미	(==)
=any	(=, OR)	in (=, OR)
>any	(>, OR) 최소값보다 큰지
>=any	(>=, OR) 최소값보다 크거나 같은지
<any	(<, OR) 최대값보다 작은지
<=any	(<=, OR) 최대값보다 작거나 같은지
<>any	(<>, OR)	잘 사용 안함
=all	(=, AND)	잘 사용 안함
>all	(>, AND) 최대값보다 큰지
>=all	(>=, AND) 최대값보다 크거나 같은지
<all	(<, AND) 최소값보다 작은지
<=all	(<=, AND) 최소값보다 작거나 같은지
<>all	(<>, AND)	not in(<>, AND)

select employee_id, last_name, job_id, salary
from employees
where salary < any
(select salary
from employees
where job_id = 'IT_PROG')
and job_id<>'IT_PROG';

select employee_id, last_name, job_id, salary
from employees
where salary < all
(select salary
from employees
where job_id = 'IT_PROG')
and job_id<>'IT_PROG';

• 다중행 서브쿼리로부터 null값이 포함된 값리스트가 반환되는 경우
  [case1] 메인쿼리에 OR의 성격을 가지는 비교연산자 사용시에는 정상 결과 출력된다.
  [case2] 메인쿼리에 AND의 성격을 가지는 비교연산자 사용시에는 메인쿼리 결과도 null이다.
  정상 결과 출력을 위해서는 서브쿼리구문에 null값 제외 조건문 작성해야함.

select emp.last_name
from employees emp
where emp.employee_id not in
(select mgr.manager_id
from employees mgr
where manager_id is not null);

단일컬럼 서브쿼리

• 서브쿼리로부터 하나의 컬럼을 기준으로 값이 반환되는 유형
• 비쌍 비교 방식 사용하기

select employee_id, manager_id, department_id
from employees
where manager_id in
(select manager_id
from employees
where employee_id in(174,141))
and department_id in
(select department_id
from employees
where employee_id in(174,141))
and employee_id not in(174, 141);

다중컬럼 서브쿼리

• 서브쿼리로부터 여러 컬럼을 기준으로 값이 반환되는 유형
• 쌍 비교 방식 사용하기

select employee_id, manager_id, department_id
from employees
where (manager_id, department_id) in
(select manager_id, department_id
from employees
where employee_id in(174,199))
and employee_id not in (174,199);

select first_name, department_id, salary
from employees
where (salary, department_id) in
(select min(salary), department_id
from employees
group by department_id)
order by department_id;

연습문제

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

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