SQL_08(서브쿼리(상관 쿼리, EXISTS, NOT EXISTS 연산자)

상관(연관) 쿼리

/* *************************************************************************************************
상관(연관) 쿼리
- 메인쿼리문의 조회값을 서브쿼리의 조건에서 사용하는 쿼리.
- 메인쿼리를 실행하고 그 결과를 바탕으로 서브쿼리의 조건절을 비교한다.
	- 메인 쿼리의 where을 실행하면서 subquery가 같이 실행된다. 이때 메인쿼리 where 절에서 비교하는 그 행의 컬럼값들을 가지고 subquery가 실행된다.
* *************************************************************************************************/
-- 부서별(DEPT)에서 급여(emp.salary)를 가장 많이 받는
-- 직원들의 id(emp.emp_id), 이름(emp.emp_name), 연봉(emp.salary), 소속부서ID(dept.dept_id) 조회
 select e.emp_id, e.emp_name, e.salary, ifnull(e.dept_id, '미배치') 소속부서ID
 from emp e 
 where salary = (select max(salary)
			     from emp
                 where ifnull(dept_id, 0) = ifnull(e.dept_id, 0))
		-- dept_id는 null을 가질 수 있기 때문에 ifnull()로 변환해서 비교한다.
order by dept_id;

---

EXISTS, NOT EXISTS 연산자

/* **************************************************************************************
EXISTS, NOT EXISTS 연산자 (상관(연관)쿼리와 같이 사용된다)
-- 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건. 
-- 조건을 만족하는 행이 여러개라도 한행만 있으면 더이상 검색하지 않는다.

- 보통 데이터테이블의 값이 이력테이블(Transaction TB)에 있는지 여부를 조회할 때 사용된다.
	- 메인쿼리: 데이터테이블
	- 서브쿼리: 이력테이블
	- 메인쿼리에서 조회할 행이 서브쿼리의 테이블에 있는지(또는 없는지) 확인
	
고객(데이터) 주문(이력) -> 특정 고객이 주문을 한 적이 있는지 여부
장비(데이터) 대여(이력) -> 특정 장비가 대여 된 적이 있는지 여부
************************************************************************************* */


-- 직원이 한명이상 있는 부서의 부서ID(dept.dept_id)와 이름(dept.dept_name),위치(dept.loc)를 조회
select * 
from dept
where exists (select * from emp where dept_id = dept.dept_id);

select * from dept;
-- 직원이 한명도 없는 부서의 부서ID(dept.dept_id)와 이름(dept.dept_name), 위치(dept.loc)를 조회
select *
from dept
where not exists (select * from emp where dept_id = dept.dept_id);

-- 부서(dept)에서 연봉(emp.salary)이 13000이상인 한명이라도 있는 부서의 부서ID(dept.dept_id)와 이름(dept.dept_name), 위치(dept.loc)를 조회
select *
from dept
where exists (select emp_id 
			  from emp 
			  where dept_id = dept.dept_id and salary >= 13000);


select emp_id from emp where dept_id = 60 and salary >= 13000;

/* ******************************
TODO 문제 
주문 관련 테이블들 이용.
******************************* */

-- TODO: 고객(customers) 중 주문(orders)을 한번 이상 한 고객들을 조회.
select *
from customers c
where exists (select *
			  from orders
              where cust_id = c.cust_id);

-- TODO: 고객(customers) 중 주문(orders)을 한번도 하지 않은 고객들을 조회.
select *
from customers c
where not exists (select *
				  from orders
				  where cust_id = c.cust_id);

-- TODO: 제품(products) 중 한번이상 주문된 제품 정보 조회
select *
from products p
where exists (select *
			  from order_items
              where product_id = p.product_id);

-- TODO: 제품(products)중 주문이 한번도 안된 제품 정보 조회
select *
from products p
where not exists (select *
				  from order_items
				  where product_id = p.product_id);