1. 서브쿼리

데이터필터링, 집계및 계산, 비교&검증, 중첩 데이터 추출 등에 사용되며,
JOIN절 대비 간단작업에 더 자주 사용되는 경향이 있다.

# 최고 마일리지를 보유한 고객의 정보 조회
SELECT 고객번호, 고객회사명, 담당자명, 마일리지
FROM 고객
WHERE 마일리지 = (
	SELECT MAX(마일리지)
    FROM 고객
    );

통상적으로 서브쿼리문이 먼저 실행된 후 메인쿼리문이 실행된다.

# 주문번호 `H0250`을 주문한 고객에 대한
# 고객 회사명과 담당자명 조회
SELECT 고객회사명, 담당자명
FROM 고객
WHERE 고객번호 = (
	SELECT 고객번호
    FROM 주문
    WHERE 주문번호 = 'H0250'
    );
    
# 동일한 내용을 JOIN 으로
SELECT 고객.고객회사명, 고객.담당자명
FROM 고객
INNER JOIN 주문
ON 고객.고객번호 = 주문.고객번호
WHERE 주문번호 = 'H0250';

#'부산광역시'고객의 최소 마일리지보다
# 더 큰 마일리지를 가진 고객 정보
SELECT 담당자명, 고객회사명, 마일리지
FROM 고객
WHERE 마일리지 > (
	SELECT MIN(마일리지)
    FROM 고객
    WHERE 도시 = '부산광역시')
ORDER BY 담당자명, 고객회사명, 마일리지;
    
#같은 내용을 JOIN으로
SELECT DISTINCT A.담당자명, A.고객회사명, A.마일리지
FROM 고객 A
INNER JOIN 고객 B
ON A.마일리지 > B.마일리지
WHERE B.도시 = '부산광역시'
ORDER BY 담당자명, 고객회사명, 마일리지;

복수 행 서브쿼리

IN, ALL, SOME, EXISTS와 같은 복수 행 비교 연산자를 사용해 메인쿼리 - 서브쿼리 연결

IN : 메인 쿼리 비교 조건이 서브 쿼리 결과 중 하나라도 일치
ANY, SOME : 서브쿼리의 결과값을 비교연산자로 비교하여 하나 이상 일치
ALL : 서브 쿼리 결과값을 비교연산자로 비교하여 모든 경우 일치
EXISTS : 서브쿼리에 비교조건을 만족하는 결과가 존재하는 경우 필터

# '부산광역시'고객이 주문한 주문건수
SELECT COUNT(*) AS 주문건수
FROM 주문
WHERE 고객번호 IN (
	SELECT 고객번호 
    FROM 고객 
    WHERE 도시 = '부산광역시'
    );

#'부산광역시' 전체 고객의 마일리지보다
# 더 많은 마일리지 보유 고객의 정보 조회
SELECT 담당자명, 고객회사명, 마일리지
FROM 고객
WHERE 마일리지 > ANY (
	SELECT 마일리지
    FROM 고객
    WHERE 도시 = '부산광역시'
    );

# 각 지역의 어느 평균 마일리지보다도
# 더 많은 마일리지를 보유한 고객정보 조회
SELECT 담당자명, 고객회사명, 마일리지
FROM 고객
WHERE 마일리지 > ALL (
	SELECT AVG(마일리지)
    FROM 고객
    GROUP BY 지역
    );

# 한번이라도 주문한 적이 있는 고객 정보 조회
# 힌트 : EXISTS 나 IN
SELECT 고객번호, 고객회사명
FROM 고객
WHERE EXISTS (
	SELECT *
    FROM 주문
    WHERE 고객번호 = 고객.고객번호
    );
# 같은 결과를 IN으로
SELECT 고객번호, 고객회사명
FROM 고객
WHERE 고객번호 IN (
	SELECT DISTINCT 고객번호
    FROM 주문
    );

사용 위치에 따른 서브쿼리

1) 조건절(GROUP BY의 조건인 HAVING)에 사용하는 서브쿼리

# 고객 전체의 평균 마일리지보다
# 평균 마일리지가 큰 도시에 대하여
# 도시명, 도시 평균 마일리지 조회
SELECT 도시, 
	AVG(마일리지) AS 평균마일리지
FROM 고객
GROUP BY 도시
HAVING AVG(마일리지) > (
	SELECT AVG(마일리지)
    FROM 고객
    );

2) FROM절에 사용하는 서브쿼리 -> 인라인 뷰 라고 부름

SELECT 담당자명, 고객회사명, 마일리지,
	고객.도시, 도시_평균마일리지, 
    도시_평균마일리지 - 마일리지 AS 마일리지차이
FROM 고객
	, (SELECT 도시,
		AVG(마일리지) AS 도시_평균마일리지
        FROM 고객
        GROUP BY 도시
        ) AS 도시요약 #이게 인라인뷰의 별명
WHERE 고객.도시 = 도시요약.도시;

3) 스칼라 서브쿼리 : SELECT절 내에서 사용하는 서브쿼리

# 고객번호, 담당자명과 고객의 최종 주문일 조회
SELECT 고객번호, 담당자명, (
	SELECT MAX(주문일)
    FROM 주문
    WHERE 주문.고객번호 = 고객.고객번호
    ) AS 최종주문일
FROM 고객;

---

2. CTE(Common Table Expression)

쿼리로 만든 임시 데이터셋을 말하며 WITH절에서 정의
인라인 뷰와 마찬가지로 파생 테이블처럼 활용 가능

# 담당자명, 고객회사명, 마일리지, 도시,
# 해당 도시의 평균 마일리지 조회
# 이때 고객이 위치하는 도시의 평균마일리지와
# 각 고객의 마일리지간 차이도 조회
# 위 문제를 CTE로 풀이
WITH 도시별요약 AS (
	SELECT 도시, AVG(마일리지) AS 도시_평균마일리지 
    FROM 고객 
    GROUP BY 도시
    )
SELECT 담당자명, 고객회사명, 마일리지, 
	고객.도시, 도시_평균마일리지, 
    도시_평균마일리지 - 마일리지 AS 마일리지_차이
FROM 고객, 도시별요약
WHERE 고객.도시 = 도시별요약.도시;

3. 상관 서브쿼리

메인 쿼리와 서브 쿼리 간의 상관관계를 포함하는 형태의 쿼리
상퀀 쿼리는 메인 쿼리를 한 행씩 처리함

# 사원 테이블에서 사원번호, 사원이름, 
# 상사 사원번호, 상사이름 조회
SELECT 사원번호, 이름,
	상사번호, (
		SELECT 이름
        FROM 사원 AS 상사
        WHERE 상사.사원번호 = 사원.상사번호
        # 여기가 상관 서브쿼리 구문
        ) AS 상사이름
FROM 사원;

4. 다중 컬럼 서브쿼리

서브쿼리에서 여러 개의 컬럼을 사용하여
여러 비교를 수행하는 쿼리

# 각 도시마다 최고 마일리지를 보유한 고객 정보를 조회
SELECT 도시, 담당자명, 고객회사명, 마일리지
FROM 고객
WHERE (도시, 마일리지) IN (
	SELECT 도시, MAX(마일리지)
	FROM 고객
    GROUP BY 도시);

---

5. 점검문제

# '배재용' 사원의 부서명을 보이시오
SELECT 부서명
FROM 부서
WHERE 부서번호 = (
	SELECT 부서번호
    FROM 사원
    WHERE 이름 = '배재용'
    );

# 한번도 주문한 적이 없는 제품의 정보를 보이시오
SELECT 제품번호, 제품명, 포장단위, 단가, 재고, 
	단가*재고 AS 재고금액
FROM 제품
WHERE NOT EXISTS (
	SELECT 제품번호
    FROM 주문세부
    WHERE 주문세부.제품번호 = 제품.제품번호
    );

# 담당자명, 고객회사명, 주문건수
# 최초 및 최종 주문일 조회
SELECT 담당자명, 고객회사명, 주문건수,
	최초주문일, 최종주문일
FROM 고객, (
	SELECT 고객번호, 
		COUNT(주문번호) AS 주문건수,
        MIN(주문일) AS 최초주문일,
        MAX(주문일) AS 최종주문일
    FROM 주문
    GROUP BY 고객번호) AS 주문요약
WHERE 고객.고객번호 = 주문요약.고객번호;