상관(연관) 쿼리

• 메인쿼리문의 조회값을 서브쿼리의 조건에서 사용하는 쿼리.
• 메인쿼리를 실행하고 그 결과를 바탕으로 서브쿼리의 조건절을 비교한다.
  - 메인 쿼리의 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, e.dept_id
from emp e 
where salary = 24000.00;

select max(salary) from emp where dept_id = 90;
-- --------
select max(salary) from emp group by dept_id;


## 메인쿼리의 값을 서브쿼리의 조건에서 사용
select e.emp_id, e.emp_name, e.salary, e.dept_id
from emp e
where salary = (select max(salary)
				from emp 
                where ifnull(dept_id,0) = ifnull(e.dept_id,0));
#1) where절 서브쿼리가 실행될때  e.dept_id 를 사용하므로 메인 뭐리 각 행을 가져와 실행 => 메인/서브쿼리가 동시에 실행되는 상관쿼리)
#2) dept_id => 서브쿼리의 from emp에서 가져온 dept_id 컬럼
-- e.dept_id => 바깥쪽테이블의 from emp e에서 가져온 dept_id
-- => 메인쿼리에 alias를 설정해 서브쿼리와 메인쿼리의 테이블을 물리적으로 분리한다.
#3) dept_id는 null을 가질 수 있기 때문에 ifnull()로 변환해서 비교한다.
-- null != null;

EXISTS, NOT EXISTS 연산자 (상관(연관)쿼리와 같이 사용된다)

-- 서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건.
-- 조건을 만족하는 행이 여러개라도 한행만 있으면 더이상 검색하지 않는다.

• 보통 데이터테이블의 값이 이력테이블(Transaction TB)에 있는지 여부를 조회할 때 사용된다.
  - 메인쿼리: 데이터테이블
  - 서브쿼리: 이력테이블
  - 메인쿼리에서 조회할 행이 서브쿼리의 테이블에 있는지(또는 없는지) 확인
  

고객(데이터) 주문(이력) -> 특정 고객이 주문을 한 적이 있는지 여부
장비(데이터) 대여(이력) -> 특정 장비가 대여 된 적이 있는지 여부

-- 직원이 한명이상 있는 부서의 부서ID(dept.dept_id)와 이름(dept.dept_name),위치(dept.loc)를 조회
-- 직원(자식)이 부서(부모)를 참조. 부서(부모)의 값이 직원(자식)

개념 )
select * from dept
where (select * from emp where dept_id = 120); -- 값이 안나오면 없는 것 => 이 쿼리를 시행했을 때 한 행이라도 나오는 쿼리 필요 
-- dept table의 행을 하나씩 체크하면서 dept에 emp table의 detp_id가 120일때와 같을 때가 있는지를 확인한다. => 상관쿼리면서 exists로 값이 존재하는 행만 출력

Exists)
select * from dept
where exists (select salary from emp where dept_id = dept.dept_id);
-- 상관쿼리 : 서브쿼리에 있는 값이 메인쿼리의 있는 값을 포함하고 있어야함.

--확인
select * from dept;
select * from emp;

예)

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

예)

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

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

-- TODO: 고객(customers) 중 주문(orders)을 한번 이상 한 고객들을 조회.

select * from orders where cust_id = 150;
-- cust_Id를 하나씩 쳐가며 값이 나오면 존재,값이 나오지 않으면 존재하지 않는다 => 이를 where절에서 하나의 쿼리로 시행하고 싶음 => exists 사용


select *
from customers c
where exists (select * from orders where cust_id = c.cust_id);
-- not exists = 존재하는 행이 출력


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

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