#Step 08 — 서브쿼리

SELECT
    o.order_id,
    o.total_amount,
    (SELECT ROUND(AVG(total_amount), 0) FROM orders) AS avg_all,
    o.total_amount - (SELECT AVG(total_amount) FROM orders) AS diff
FROM orders o
ORDER BY o.total_amount DESC
LIMIT 5;

SELECT product_id, name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 5;

SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = '서울')
ORDER BY order_id
LIMIT 5;

SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE (customer_id, total_amount) = (
    SELECT customer_id, MAX(total_amount)
    FROM orders
    WHERE customer_id = 1
    GROUP BY customer_id
);

SELECT s.customer_id, c.name, s.order_cnt, s.sum_amount
FROM (
    SELECT customer_id, COUNT(*) AS order_cnt, SUM(total_amount) AS sum_amount
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY customer_id
) AS s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.sum_amount >= 3000000
ORDER BY s.sum_amount DESC
LIMIT 5;

SELECT
    c.customer_id,
    c.name,
    (SELECT COUNT(*)          FROM orders o WHERE o.customer_id = c.customer_id) AS order_cnt,
    (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c
ORDER BY order_cnt DESC
LIMIT 5;

SELECT c.customer_id, c.name, c.grade
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM reviews r WHERE r.customer_id = c.customer_id
)
ORDER BY c.customer_id;

SELECT o.order_id, o.status, o.total_amount
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM payments p WHERE p.order_id = o.order_id
)
ORDER BY o.order_id
LIMIT 5;

-- (1) IN
SELECT COUNT(*) AS cnt FROM products p
WHERE p.product_id IN (SELECT oi.product_id FROM order_items oi);

-- (2) EXISTS
SELECT COUNT(*) AS cnt FROM products p
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

-- (3) JOIN — DISTINCT 를 잊으면?
SELECT COUNT(*) AS cnt_wrong
FROM products p JOIN order_items oi ON oi.product_id = p.product_id;

EXPLAIN FORMAT=TREE
SELECT p.product_id FROM products p
WHERE p.product_id IN (SELECT oi.product_id FROM order_items oi);

-- 노트북 중, 어떤 스마트폰보다든 비싼 것 (= 최저가 스마트폰보다 비싼 것)
SELECT product_id, name, price FROM products
WHERE category_id = 21 AND price > ANY (SELECT price FROM products WHERE category_id = 22)
ORDER BY price;

-- 노트북 중, 모든 스마트폰보다 비싼 것 (= 최고가 스마트폰보다 비싼 것)
SELECT product_id, name, price FROM products
WHERE category_id = 21 AND price > ALL (SELECT price FROM products WHERE category_id = 22)
ORDER BY price;

SELECT COUNT(*) AS cnt
FROM employees e
WHERE e.employee_id NOT IN (SELECT manager_id FROM employees);

SELECT manager_id, COUNT(*) AS cnt FROM employees GROUP BY manager_id ORDER BY manager_id LIMIT 3;

SELECT 5 NOT IN (1, 2, NULL) AS r1,
       5 IN     (1, 2, NULL) AS r2,
       5 <> NULL             AS r3;

-- 해결 1: 서브쿼리에서 NULL 제거
SELECT COUNT(*) FROM employees e
WHERE e.employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL);

-- 해결 2: NOT EXISTS  ← 가장 권장. NULL 에 구조적으로 안전
SELECT COUNT(*) FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.employee_id);

-- 해결 3: LEFT JOIN ... IS NULL (안티 조인)
SELECT COUNT(*) FROM employees e
LEFT JOIN employees m ON m.manager_id = e.employee_id
WHERE m.employee_id IS NULL;

EXPLAIN FORMAT=TREE
SELECT d.product_id, d.name
FROM (SELECT product_id, name, price, category_id FROM products) AS d
WHERE d.category_id = 21;

SET SESSION optimizer_switch = 'derived_merge=off';
EXPLAIN FORMAT=TREE
SELECT d.product_id, d.name
FROM (SELECT product_id, name, price, category_id FROM products) AS d
WHERE d.category_id = 21;
SET SESSION optimizer_switch = 'derived_merge=on';

EXPLAIN FORMAT=TREE
SELECT d.category_id, d.cnt
FROM (SELECT category_id, COUNT(*) AS cnt FROM products GROUP BY category_id) AS d
WHERE d.cnt >= 4;

SELECT c.customer_id, c.name, t.order_id, t.total_amount
FROM customers c
JOIN LATERAL (
    SELECT o.order_id, o.total_amount
    FROM orders o
    WHERE o.customer_id = c.customer_id   -- ← 바깥의 c 를 참조!
    ORDER BY o.total_amount DESC
    LIMIT 1
) AS t ON TRUE
ORDER BY t.total_amount DESC
LIMIT 5;

SELECT p.product_id, p.name, p.category_id, p.price,
       (SELECT ROUND(AVG(p2.price)) FROM products p2 WHERE p2.category_id = p.category_id) AS cat_avg
FROM products p
WHERE p.price > (SELECT AVG(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id, p.price DESC
LIMIT 8;

-- =====================================================================
-- Step 08 — 서브쿼리 : practice.sql
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < practice.sql
-- * 이 파일은 SELECT 만 합니다. 원본 데이터를 변경하지 않습니다.
-- =====================================================================
USE shop;

-- [8-1] 스칼라 서브쿼리 : 전체 평균 주문금액을 한 컬럼으로 붙이기
SELECT
    o.order_id,
    o.total_amount,
    (SELECT ROUND(AVG(total_amount), 0) FROM orders) AS avg_all,
    o.total_amount - (SELECT AVG(total_amount) FROM orders) AS diff
FROM orders o
ORDER BY o.total_amount DESC
LIMIT 5;

-- [8-2] WHERE 절 스칼라 서브쿼리 : 평균보다 비싼 상품
SELECT product_id, name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC
LIMIT 5;

-- [8-3] 스칼라 서브쿼리가 2행 이상 반환하면 에러 (일부러 실패시키는 예제)
-- SELECT * FROM products WHERE price = (SELECT price FROM products WHERE category_id = 21);
-- ERROR 1242 (21000): Subquery returns more than 1 row

-- [8-4] IN : 다중행 서브쿼리 — 서울에 사는 고객의 주문
SELECT order_id, customer_id, total_amount
FROM orders
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = '서울')
ORDER BY order_id
LIMIT 5;

-- [8-5] 행(ROW) 서브쿼리 : 여러 컬럼을 한 번에 비교
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE (customer_id, total_amount) = (
    SELECT customer_id, MAX(total_amount)
    FROM orders
    WHERE customer_id = 1
    GROUP BY customer_id
);

-- [8-6] FROM 절 서브쿼리(파생 테이블) : 고객별 합계를 먼저 만들고 다시 필터
SELECT s.customer_id, c.name, s.order_cnt, s.sum_amount
FROM (
    SELECT customer_id, COUNT(*) AS order_cnt, SUM(total_amount) AS sum_amount
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY customer_id
) AS s
JOIN customers c ON c.customer_id = s.customer_id
WHERE s.sum_amount >= 3000000
ORDER BY s.sum_amount DESC
LIMIT 5;

-- [8-7] 파생 테이블에는 반드시 별칭(alias)이 필요하다
-- SELECT * FROM (SELECT 1) ;   -- ERROR 1248 (42000): Every derived table must have its own alias

-- [8-8] 상관 서브쿼리 : 바깥 행마다 안쪽 쿼리가 다시 평가된다
SELECT
    c.customer_id,
    c.name,
    (SELECT COUNT(*)      FROM orders o WHERE o.customer_id = c.customer_id) AS order_cnt,
    (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c
ORDER BY order_cnt DESC
LIMIT 5;

-- [8-9] EXISTS : "있기만 하면 참" — 후기를 한 번이라도 쓴 고객
SELECT c.customer_id, c.name, c.grade
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM reviews r WHERE r.customer_id = c.customer_id
)
ORDER BY c.customer_id
LIMIT 5;

-- [8-10] NOT EXISTS : 결제 기록이 하나도 없는 주문(PENDING)
SELECT o.order_id, o.status, o.total_amount
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM payments p WHERE p.order_id = o.order_id
)
ORDER BY o.order_id
LIMIT 5;

-- [8-11] NOT EXISTS 로 세는 전체 건수
SELECT COUNT(*) AS no_payment_orders
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.order_id);

-- [8-12] 같은 질문 3가지 방법 (1) IN
SELECT COUNT(*) AS cnt
FROM products p
WHERE p.product_id IN (SELECT oi.product_id FROM order_items oi);

-- [8-13] 같은 질문 3가지 방법 (2) EXISTS
SELECT COUNT(*) AS cnt
FROM products p
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);

-- [8-14] 같은 질문 3가지 방법 (3) JOIN — DISTINCT 를 잊으면 결과가 부풀어 오른다
SELECT COUNT(*) AS cnt_wrong
FROM products p
JOIN order_items oi ON oi.product_id = p.product_id;

-- [8-15] JOIN 으로 올바르게 세기
SELECT COUNT(DISTINCT p.product_id) AS cnt_right
FROM products p
JOIN order_items oi ON oi.product_id = p.product_id;

-- [8-16] IN 서브쿼리의 실행계획 (8.0 은 semijoin 으로 변환한다)
EXPLAIN FORMAT=TREE
SELECT p.product_id FROM products p
WHERE p.product_id IN (SELECT oi.product_id FROM order_items oi);

-- [8-17] ANY : 서브쿼리 결과 중 "하나라도" 만족
SELECT product_id, name, price
FROM products
WHERE category_id = 21
  AND price > ANY (SELECT price FROM products WHERE category_id = 22)
ORDER BY price;

-- [8-18] ALL : 서브쿼리 결과 "전부"보다 커야 함
SELECT product_id, name, price
FROM products
WHERE category_id = 21
  AND price > ALL (SELECT price FROM products WHERE category_id = 22)
ORDER BY price;

-- [8-19] 비교 대상 확인 (스마트폰 카테고리 가격)
SELECT product_id, name, price FROM products WHERE category_id = 22 ORDER BY price;

-- [8-20] NOT IN + NULL 함정 — 관리자가 아닌 사원을 찾으려 했지만 0행
SELECT COUNT(*) AS cnt
FROM employees e
WHERE e.employee_id NOT IN (SELECT manager_id FROM employees);

-- [8-21] 원인 : 서브쿼리에 NULL 이 섞여 있다
SELECT manager_id, COUNT(*) AS cnt
FROM employees
GROUP BY manager_id
ORDER BY manager_id
LIMIT 3;

-- [8-22] NULL 과의 비교는 UNKNOWN
SELECT 5 NOT IN (1, 2, NULL)  AS r1,
       5 IN     (1, 2, NULL)  AS r2,
       5 <> NULL              AS r3;

-- [8-23] 해결 1 : 서브쿼리에서 NULL 제거
SELECT COUNT(*) AS cnt
FROM employees e
WHERE e.employee_id NOT IN (
    SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);

-- [8-24] 해결 2 : NOT EXISTS (NULL 에 안전. 가장 권장)
SELECT COUNT(*) AS cnt
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM employees m WHERE m.manager_id = e.employee_id
);

-- [8-25] 해결 3 : LEFT JOIN ... IS NULL (안티 조인)
SELECT COUNT(*) AS cnt
FROM employees e
LEFT JOIN employees m ON m.manager_id = e.employee_id
WHERE m.employee_id IS NULL;

-- [8-26] 파생 테이블 머지(derived merge) — 8.0 은 파생 테이블을 바깥 쿼리에 합친다
EXPLAIN FORMAT=TREE
SELECT d.product_id, d.name
FROM (SELECT product_id, name, price, category_id FROM products) AS d
WHERE d.category_id = 21;

-- [8-27] 머지를 막는 요소(GROUP BY 등)가 있으면 임시테이블로 구체화(materialize)된다
EXPLAIN FORMAT=TREE
SELECT d.category_id, d.cnt
FROM (SELECT category_id, COUNT(*) AS cnt FROM products GROUP BY category_id) AS d
WHERE d.cnt >= 4;

-- [8-28] optimizer_switch 로 머지를 강제로 끄고 비교 (세션 한정)
SET SESSION optimizer_switch = 'derived_merge=off';
EXPLAIN FORMAT=TREE
SELECT d.product_id, d.name
FROM (SELECT product_id, name, price, category_id FROM products) AS d
WHERE d.category_id = 21;
SET SESSION optimizer_switch = 'derived_merge=on';

-- [8-29] LATERAL (8.0.14+) : 파생 테이블이 바깥 행을 참조할 수 있다
SELECT c.customer_id, c.name, t.order_id, t.total_amount
FROM customers c
JOIN LATERAL (
    SELECT o.order_id, o.total_amount
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.total_amount DESC
    LIMIT 1
) AS t ON TRUE
ORDER BY t.total_amount DESC
LIMIT 5;

-- [8-30] 종합 : 카테고리 평균가보다 비싼 상품 (상관 서브쿼리)
SELECT p.product_id, p.name, p.category_id, p.price,
       (SELECT ROUND(AVG(p2.price)) FROM products p2 WHERE p2.category_id = p.category_id) AS cat_avg
FROM products p
WHERE p.price > (SELECT AVG(p2.price) FROM products p2 WHERE p2.category_id = p.category_id)
ORDER BY p.category_id, p.price DESC
LIMIT 8;

-- =====================================================================
-- Step 08 — 서브쿼리 : 연습문제
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < exercise.sql
-- 정답은 solution.sql
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- Q1. 전체 평균 주문금액(total_amount)보다 큰 주문은 몇 건인가?
--     컬럼: over_avg_cnt
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q2. 후기가 한 건도 달리지 않은 상품을 NOT EXISTS 로 찾으시오.
--     컬럼: product_id, name, price  / 정렬: product_id
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q3. Q2 와 같은 결과를 NOT IN 으로 작성하시오.
--     단, reviews.product_id 에 NULL 이 없더라도
--     "NULL 함정을 피하는 안전한 형태"로 작성할 것.
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q4. 취소(CANCELLED)를 제외한 주문 기준으로,
--     고객별 주문건수와 주문금액 합계를 파생 테이블로 만들고
--     합계 상위 5명을 고객 이름과 함께 출력하시오.
--     컬럼: customer_id, name, grade, order_cnt, sum_amount
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q5. 자기가 속한 부서(dept)의 평균 급여보다 많이 받는 사원을 찾으시오.
--     컬럼: employee_id, name, dept, salary, dept_avg(반올림)
--     정렬: dept, salary DESC
--     (상관 서브쿼리를 사용할 것)
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q6. '주변기기'(category_id=23) 의 모든 상품보다 비싼
--     '노트북'(category_id=21) 상품을 ALL 을 써서 찾으시오.
--     컬럼: product_id, name, price / 정렬: price
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q7. LATERAL 을 사용해 카테고리별 최고가 상품을 1개씩 뽑으시오.
--     대상: products 가 실제로 존재하는 카테고리만
--     컬럼: category_id, cat_name, product_id, product_name, price
--     정렬: price DESC
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q8. 결제(payments)가 한 건도 없는 주문의 건수를
--     (a) NOT EXISTS  (b) LEFT JOIN ... IS NULL
--     두 가지 방법으로 각각 구하고, 결과가 같은지 확인하시오.
-- ---------------------------------------------------------------------

-- =====================================================================
-- Step 08 — 서브쿼리 : 정답 + 해설
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- A1. 스칼라 서브쿼리를 WHERE 에 그대로 쓴다.
--     AVG 는 한 번만 계산된다(상관 서브쿼리가 아니므로).
-- ---------------------------------------------------------------------
SELECT COUNT(*) AS over_avg_cnt
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);
-- → 210

-- ---------------------------------------------------------------------
-- A2. NOT EXISTS 안티 조인.
--     상관 조건(r.product_id = p.product_id)이 반드시 필요하다.
-- ---------------------------------------------------------------------
SELECT p.product_id, p.name, p.price
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM reviews r WHERE r.product_id = p.product_id
)
ORDER BY p.product_id;
-- → 24행

-- ---------------------------------------------------------------------
-- A3. NOT IN 버전.
--     핵심은 서브쿼리에 WHERE product_id IS NOT NULL 을 붙여
--     "NULL 이 섞여 들어올 가능성"을 원천 차단하는 것.
--     지금 스키마에선 reviews.product_id 가 NOT NULL 이라 없어도 되지만,
--     스키마는 바뀔 수 있으므로 방어적으로 쓰는 습관이 안전하다.
--     (근본적으로는 A2 처럼 NOT EXISTS 를 쓰는 게 낫다.)
-- ---------------------------------------------------------------------
SELECT p.product_id, p.name, p.price
FROM products p
WHERE p.product_id NOT IN (
    SELECT r.product_id FROM reviews r WHERE r.product_id IS NOT NULL
)
ORDER BY p.product_id;
-- → A2 와 동일하게 24행

-- ---------------------------------------------------------------------
-- A4. 파생 테이블로 먼저 집계 → 그다음 customers 와 조인.
--     조인부터 하고 GROUP BY 해도 되지만, 집계를 먼저 하면
--     조인 대상 행 수가 30건으로 줄어 계획이 단순해진다.
-- ---------------------------------------------------------------------
SELECT s.customer_id, c.name, c.grade, s.order_cnt, s.sum_amount
FROM (
    SELECT customer_id,
           COUNT(*)           AS order_cnt,
           SUM(total_amount)  AS sum_amount
    FROM orders
    WHERE status <> 'CANCELLED'
    GROUP BY customer_id
) AS s
JOIN customers c ON c.customer_id = s.customer_id
ORDER BY s.sum_amount DESC
LIMIT 5;
-- → 임수진(58,449,000) / 정 훈(51,568,000) / 황도윤(50,248,500) / 남규리 / 백승호

-- ---------------------------------------------------------------------
-- A5. 상관 서브쿼리. e.dept 를 안쪽에서 참조하므로
--     사원 18명 각각에 대해 부서 평균이 다시 계산된다.
-- ---------------------------------------------------------------------
SELECT e.employee_id, e.name, e.dept, e.salary,
       (SELECT ROUND(AVG(e2.salary)) FROM employees e2 WHERE e2.dept = e.dept) AS dept_avg
FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept = e.dept)
ORDER BY e.dept, e.salary DESC;
-- → 7행 (개발본부 김코드/박서버/최화면, 경영지원 오지원/윤사람, 영업본부 이세일/강매출)

-- ---------------------------------------------------------------------
-- A6. > ALL : 서브쿼리의 "모든" 값보다 커야 한다 = 최댓값보다 커야 한다.
--     MAX 로 바꿔 써도 같다:  price > (SELECT MAX(price) ... )
-- ---------------------------------------------------------------------
SELECT product_id, name, price
FROM products
WHERE category_id = 21
  AND price > ALL (SELECT price FROM products WHERE category_id = 23)
ORDER BY price;
-- → 주변기기 최고가는 459,000(27인치 4K 모니터). 노트북 4종이 모두 그보다 비싸다.

-- ---------------------------------------------------------------------
-- A7. LATERAL (8.0.14+).
--     파생 테이블 안에서 바깥의 c.category_id 를 참조할 수 있다.
--     그래서 "카테고리마다 ORDER BY price DESC LIMIT 1" 이 가능해진다.
--     LATERAL 이 없다면 윈도우 함수(ROW_NUMBER) 를 써야 한다.
-- ---------------------------------------------------------------------
SELECT c.category_id, c.name AS cat_name,
       t.product_id, t.name AS product_name, t.price
FROM categories c
JOIN LATERAL (
    SELECT p.product_id, p.name, p.price
    FROM products p
    WHERE p.category_id = c.category_id
    ORDER BY p.price DESC, p.product_id
    LIMIT 1
) AS t ON TRUE
ORDER BY t.price DESC;
-- → 12행 (상품이 있는 카테고리만. JOIN 이므로 상품 없는 카테고리는 자동 제외)

-- ---------------------------------------------------------------------
-- A8-a. NOT EXISTS
-- ---------------------------------------------------------------------
SELECT COUNT(*) AS cnt_not_exists
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.order_id);
-- → 60

-- ---------------------------------------------------------------------
-- A8-b. LEFT JOIN ... IS NULL
--     주의: IS NULL 검사는 조인 대상 테이블의 "NOT NULL 컬럼"(보통 PK)에 걸어야 한다.
--     NULL 을 허용하는 컬럼에 걸면 "조인은 됐지만 그 컬럼이 NULL 인 행"까지 섞인다.
-- ---------------------------------------------------------------------
SELECT COUNT(*) AS cnt_left_join
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL;
-- → 60  (NOT EXISTS 와 동일)

-- ---------------------------------------------------------------------
-- 보너스: 셋 중 무엇을 쓸까?
--   - 안티 조인은 NOT EXISTS 가 의도가 가장 명확하고 NULL 에 안전하다.
--   - LEFT JOIN IS NULL 은 예전 MySQL 에서 더 빨랐지만,
--     8.0 은 NOT EXISTS 도 안티조인으로 최적화하므로 차이가 거의 없다.
--   - NOT IN 은 NULL 함정 때문에 마지막 선택지.
-- ---------------------------------------------------------------------