Day28 Database-2

221130

📌 7. 서브쿼리(Subquery)

서브쿼리란? 쿼리 구문 안에 또 다시 쿼리 구문이 포함되어 있는 형태
                 Group by 절을 제외한 쿼리구문에 사용 가능

• 서브쿼리 유형: 단일행 서브쿼리, 다중행 서브쿼리

[where절에 서브쿼리가 사용되는 문법]
select 컬럼1, 컬럼2, 컬럼3
         from 테이블명
         where 컬렴명 = (select 컬럼명
                               from 테이블명
                               where 조건문);

✍ employees테이블에서 Able보다 급여를 더 많이 받는 사원들의
     employee_id, last_name, salary를 출력하시오.

select employee_id, last_name, salary
from employees
where salary > (select salary
				from employees
                where last_name = 'abel');

---

📂 단일행 서브쿼리(Single-row subquery)

: 서브쿼리로부터 오직 하나의 행이 반환되는 유형
  메인쿼리에 단일행 비교연산자 사용하면 됨.

• 단일행 비교연산자 : =, >, >=, <, <=, <>

✍ 141번 사원과 동일한 업무 담당자를 출력하시오.

select employee_id, last_name, job_id
from employees
where job_id = (select job_id
				from employees
				where employee_id =141);

✍ 141번 사원과 동일한 업무 담당자를 출력하되, 결과에 141번 사원을 제외하시오.

select employee_id, last_name, job_id
from employees
where job_id = (select job_id
				from employees
				where employee_id =141)
and employee_id <> 141;

✍ employees 테이블의 전체 직원 중 급여를 가장 적게 받는 사원의
     employee_id, last_name, salary,job_id, department_id를 출력하시오

select employee_id, last_name, salary, job_id, department_id
from employees
-- where salary = (우리회사 최소급여);
where salary = (select min(salary)
				from employees);

✍ 'Lee'와 동일업무를 담당하면서 급여는 더 많이 받는 사원의 정보 출력하시오.

select last_name, job_id, salary
from employees
where job_id = (select job_id
				from employees
				where LAST_NAME = 'Lee')
AND salary > (select salary
			  from employees
              where last_name = 'Lee');

< Having절에 서브쿼리 사용 예제 >

✍ 부서별 최소급여를 출력하되, 30번 부서의 최소급보다 큰 부서들만 출력하시오.

select department_id, min(salary)
from employees
where department_id is not null
group by department_id
having min(salary) > (select min(salary)
					  from employees
                      where department_id = 30);

✍ 부서별 최소급여와 동일한 급여를 받는 직원들을 출력하시오.

select employee_id, last_name
from employees
where salary = (select min(salary)		-- 부서별 최소급여를 출력하는 서브쿼리
				from employees
                group by department_id);

❌ [오류원인?]
: 메인쿼리에 단일행 비교연산자(=)를 사용하였으므로 서브쿼리로부터 한 행이 반환되어야 하는데
 여러 행이 반환되어 오류가 발생됨.

⭕ [수정]
: 부서별 최소급여를 출력하는 서브쿼리

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

❌ 결과가 나오지 않는 원인은?
: (원래는 hass만이라도 나와야하지만) 회사에 Haas라는 직원이 없음!!
단일행 서브쿼리인 경우, 서브쿼리 결과가 null이면 메인쿼리 결과도 null값이 반환됨.

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

---

📂 다중행 서브쿼리(Multiple-row subquery)

: 서브쿼리로부터 메인쿼리로 두 개 이상의 행을 반환되는 유형
  다중행 서브쿼리인 경우, 메인쿼리에 다중행 비교연산자를 사용해야 함

• 다중행 비교연산자: 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)


<IN 활용 예제>

SELECT employee_id, last_name, 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); 

<Any 활용 예제>

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';

✍ 'IT_PROG' 담당자들보다 급여를 작게 받는 직원들 출력하되, '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';	// 44명