#Step 10 — 집합 연산 (UNION / INTERSECT / EXCEPT)

SELECT VERSION() AS version;

SELECT city FROM customers WHERE grade = 'VIP'
UNION
SELECT shipping_city FROM orders WHERE total_amount > 6000000;

SELECT city FROM customers WHERE grade = 'VIP'
UNION ALL
SELECT shipping_city FROM orders WHERE total_amount > 6000000;

SELECT 'UNION' AS op, COUNT(*) AS cnt FROM (
    SELECT city FROM customers WHERE grade = 'VIP'
    UNION
    SELECT shipping_city FROM orders WHERE total_amount > 6000000
) x
UNION ALL
SELECT 'UNION ALL', COUNT(*) FROM (
    SELECT city FROM customers WHERE grade = 'VIP'
    UNION ALL
    SELECT shipping_city FROM orders WHERE total_amount > 6000000
) y;

EXPLAIN FORMAT=TREE
SELECT city FROM customers UNION SELECT shipping_city FROM orders;

EXPLAIN FORMAT=TREE
SELECT city FROM customers UNION ALL SELECT shipping_city FROM orders;

SELECT customer_id, name FROM customers
UNION
SELECT product_id FROM products;
-- ERROR 1222 (21000): The used SELECT statements have a different number of columns

SELECT customer_id AS id, name AS label FROM customers WHERE customer_id <= 2
UNION ALL
SELECT product_id, name FROM products WHERE product_id <= 2;

SELECT customer_id AS v FROM customers WHERE customer_id = 1
UNION ALL
SELECT email FROM customers WHERE customer_id = 1;

CREATE TEMPORARY TABLE s10_tmp_type AS
SELECT customer_id AS v FROM customers WHERE customer_id = 1
UNION ALL
SELECT email FROM customers WHERE customer_id = 1;
DESC s10_tmp_type;
DROP TEMPORARY TABLE s10_tmp_type;

SELECT name, price FROM products WHERE category_id = 21
UNION ALL
SELECT name, price FROM products WHERE category_id = 22
ORDER BY price DESC;

(SELECT name, price FROM products WHERE category_id = 21 ORDER BY price DESC LIMIT 2)
UNION ALL
(SELECT name, price FROM products WHERE category_id = 22 ORDER BY price DESC LIMIT 2);

(SELECT name, price, '노트북' AS kind FROM products WHERE category_id = 21)
UNION ALL
(SELECT name, price, '스마트폰' FROM products WHERE category_id = 22)
ORDER BY price DESC
LIMIT 3;

SELECT product_id FROM products WHERE price >= 100000
INTERSECT
SELECT product_id FROM reviews
ORDER BY product_id;

SELECT DISTINCT p.product_id
FROM products p
WHERE p.price >= 100000
  AND p.product_id IN (SELECT product_id FROM reviews)
ORDER BY p.product_id;

SELECT product_id FROM products
EXCEPT
SELECT product_id FROM reviews
ORDER BY product_id
LIMIT 5;

SELECT COUNT(*) AS never_reviewed FROM (
    SELECT product_id FROM products
    EXCEPT
    SELECT product_id FROM reviews
) x;

SELECT COUNT(*) AS never_reviewed
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.product_id);

SELECT n FROM (SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2) a
INTERSECT ALL
SELECT n FROM (SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 3) b;

SELECT n FROM (SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2) a
EXCEPT ALL
SELECT n FROM (SELECT 1 AS n) b;

(SELECT 1 AS n UNION ALL SELECT 2)
UNION
(SELECT 2 INTERSECT SELECT 3);      -- 안쪽은 빈 결과

SELECT c.category_id, c.name AS cat_name, p.product_id, p.name AS product_name
FROM categories c
LEFT JOIN products p ON p.category_id = c.category_id
UNION                                        -- ← UNION ALL 이면 안 된다!
SELECT c.category_id, c.name, p.product_id, p.name
FROM categories c
RIGHT JOIN products p ON p.category_id = c.category_id
ORDER BY category_id, product_id
LIMIT 8;

SELECT c.category_id, c.name AS cat_name, NULL AS product_id, NULL AS product_name
FROM categories c
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.category_id)
UNION ALL
SELECT NULL, NULL, p.product_id, p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM categories c WHERE c.category_id = p.category_id)
ORDER BY category_id;

SELECT 'ORDER'   AS src, order_id   AS id, order_date AS ts, total_amount AS amount
FROM orders WHERE order_date >= '2025-12-01'
UNION ALL
SELECT 'PAYMENT', payment_id, paid_at, amount
FROM payments WHERE paid_at >= '2025-12-01'
ORDER BY ts
LIMIT 8;

SELECT status, COUNT(*) AS cnt, SUM(total_amount) AS amount
FROM orders
GROUP BY status
UNION ALL
SELECT '__TOTAL__', COUNT(*), SUM(total_amount)
FROM orders
ORDER BY cnt DESC;

-- =====================================================================
-- Step 10 — 집합 연산 : practice.sql
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < practice.sql
-- * 이 파일은 SELECT 만 합니다. 원본 데이터를 변경하지 않습니다.
-- * INTERSECT / EXCEPT 는 MySQL 8.0.31 이상에서만 동작합니다.
-- =====================================================================
USE shop;

-- [10-0] 내 서버 버전 확인 (8.0.31 미만이면 INTERSECT/EXCEPT 는 문법 에러)
SELECT VERSION() AS version;

-- [10-1] UNION : 두 결과를 세로로 붙이고 중복 제거
SELECT city FROM customers WHERE grade = 'VIP'
UNION
SELECT shipping_city FROM orders WHERE total_amount > 6000000;

-- [10-2] UNION ALL : 중복을 제거하지 않는다 (그래서 빠르다)
SELECT city FROM customers WHERE grade = 'VIP'
UNION ALL
SELECT shipping_city FROM orders WHERE total_amount > 6000000;

-- [10-3] 중복 제거 여부를 개수로 확인
SELECT 'UNION' AS op, COUNT(*) AS cnt FROM (
    SELECT city FROM customers WHERE grade = 'VIP'
    UNION
    SELECT shipping_city FROM orders WHERE total_amount > 6000000
) x
UNION ALL
SELECT 'UNION ALL', COUNT(*) FROM (
    SELECT city FROM customers WHERE grade = 'VIP'
    UNION ALL
    SELECT shipping_city FROM orders WHERE total_amount > 6000000
) y;

-- [10-4] 컬럼 개수가 다르면 에러
-- SELECT customer_id, name FROM customers
-- UNION
-- SELECT product_id FROM products;
-- ERROR 1222 (21000): The used SELECT statements have a different number of columns

-- [10-5] 컬럼 이름은 첫 번째 SELECT 것을 따른다
SELECT customer_id AS id, name AS label FROM customers WHERE customer_id <= 2
UNION ALL
SELECT product_id, name FROM products WHERE product_id <= 2;

-- [10-6] 타입이 달라도 에러가 아니다 — 조용히 합쳐진다 (위험!)
SELECT customer_id AS v FROM customers WHERE customer_id = 1
UNION ALL
SELECT email FROM customers WHERE customer_id = 1;

-- [10-7] 결과 컬럼의 타입 확인
CREATE TEMPORARY TABLE s10_tmp_type AS
SELECT customer_id AS v FROM customers WHERE customer_id = 1
UNION ALL
SELECT email FROM customers WHERE customer_id = 1;
DESC s10_tmp_type;
DROP TEMPORARY TABLE s10_tmp_type;

-- [10-8] ORDER BY 는 맨 끝에 딱 한 번, 전체 결과에 적용된다
SELECT name, price FROM products WHERE category_id = 21
UNION ALL
SELECT name, price FROM products WHERE category_id = 22
ORDER BY price DESC;

-- [10-9] 개별 SELECT 에 ORDER BY/LIMIT 를 걸려면 괄호로 감싼다
(SELECT name, price FROM products WHERE category_id = 21 ORDER BY price DESC LIMIT 2)
UNION ALL
(SELECT name, price FROM products WHERE category_id = 22 ORDER BY price DESC LIMIT 2);

-- [10-10] 전체에 ORDER BY + LIMIT
(SELECT name, price, '노트북' AS kind FROM products WHERE category_id = 21)
UNION ALL
(SELECT name, price, '스마트폰' FROM products WHERE category_id = 22)
ORDER BY price DESC
LIMIT 3;

-- [10-11] ORDER BY 에는 첫 SELECT 의 "결과 컬럼명"만 쓸 수 있다
SELECT p.name AS pname, p.price AS pprice FROM products p WHERE p.category_id = 21
UNION ALL
SELECT p.name, p.price FROM products p WHERE p.category_id = 22
ORDER BY pprice DESC
LIMIT 3;

-- [10-12] INTERSECT (8.0.31+) : 양쪽에 모두 있는 것
--         "10만원 이상 상품" ∩ "후기가 달린 상품"
SELECT product_id FROM products WHERE price >= 100000
INTERSECT
SELECT product_id FROM reviews
ORDER BY product_id;

-- [10-13] INTERSECT 를 쓰기 전 방식 (IN / EXISTS) — 결과는 같다
SELECT DISTINCT p.product_id
FROM products p
WHERE p.price >= 100000
  AND p.product_id IN (SELECT product_id FROM reviews)
ORDER BY p.product_id;

-- [10-14] EXCEPT (8.0.31+) : 왼쪽에는 있고 오른쪽에는 없는 것
SELECT product_id FROM products
EXCEPT
SELECT product_id FROM reviews
ORDER BY product_id
LIMIT 5;

-- [10-15] EXCEPT 개수 확인
SELECT COUNT(*) AS never_reviewed FROM (
    SELECT product_id FROM products
    EXCEPT
    SELECT product_id FROM reviews
) x;

-- [10-16] EXCEPT 대신 쓰던 방식 (NOT EXISTS)
SELECT COUNT(*) AS never_reviewed
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.product_id);

-- [10-17] INTERSECT ALL / EXCEPT ALL (8.0.31+) : 중복을 남긴다
SELECT n FROM (SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2) a
INTERSECT ALL
SELECT n FROM (SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 3) b;

SELECT n FROM (SELECT 1 AS n UNION ALL SELECT 1 UNION ALL SELECT 2) a
EXCEPT ALL
SELECT n FROM (SELECT 1 AS n) b;

-- [10-18] 연산 우선순위 : INTERSECT 가 UNION/EXCEPT 보다 먼저 평가된다
--         괄호로 명시하는 습관을 들이자
(SELECT 1 AS n UNION ALL SELECT 2)
UNION
(SELECT 2 INTERSECT SELECT 3);

-- [10-19] UNION 이 느린 이유 : 중복 제거 = 임시 테이블 + 정렬/해시
EXPLAIN FORMAT=TREE
SELECT city FROM customers
UNION
SELECT shipping_city FROM orders;

-- [10-20] UNION ALL 은 그냥 이어붙이기만 한다
EXPLAIN FORMAT=TREE
SELECT city FROM customers
UNION ALL
SELECT shipping_city FROM orders;

-- [10-21] FULL OUTER JOIN 흉내내기 (MySQL 에는 FULL OUTER JOIN 이 없다)
--         LEFT JOIN UNION RIGHT JOIN
SELECT c.category_id, c.name AS cat_name, p.product_id, p.name AS product_name
FROM categories c
LEFT JOIN products p ON p.category_id = c.category_id
UNION
SELECT c.category_id, c.name, p.product_id, p.name
FROM categories c
RIGHT JOIN products p ON p.category_id = c.category_id
ORDER BY category_id, product_id
LIMIT 8;

-- [10-22] FULL OUTER JOIN 의 "짝 없는 행"만 보기 (양쪽 안티조인)
SELECT c.category_id, c.name AS cat_name, NULL AS product_id, NULL AS product_name
FROM categories c
WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.category_id)
UNION ALL
SELECT NULL, NULL, p.product_id, p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM categories c WHERE c.category_id = p.category_id)
ORDER BY category_id;

-- [10-23] 실무 패턴 : 서로 다른 소스를 하나의 리포트로 (UNION ALL + 구분 컬럼)
SELECT 'ORDER'   AS src, order_id   AS id, order_date AS ts, total_amount AS amount
FROM orders WHERE order_date >= '2025-12-01'
UNION ALL
SELECT 'PAYMENT', payment_id, paid_at, amount
FROM payments WHERE paid_at >= '2025-12-01'
ORDER BY ts
LIMIT 8;

-- [10-24] UNION ALL + 소계/합계 행 만들기
SELECT status, COUNT(*) AS cnt, SUM(total_amount) AS amount
FROM orders
GROUP BY status
UNION ALL
SELECT '__TOTAL__', COUNT(*), SUM(total_amount)
FROM orders
ORDER BY cnt DESC;

-- =====================================================================
-- Step 10 — 집합 연산 : 연습문제
-- 실행: mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < exercise.sql
-- 주의: INTERSECT / EXCEPT 는 MySQL 8.0.31 이상 필요
-- 정답은 solution.sql
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- Q1. VIP 고객이 사는 도시와 GOLD 고객이 사는 도시를
--     중복 없이 하나의 목록으로 출력하시오. (컬럼: city / 정렬: city)
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q2. Q1 을 UNION ALL 로 바꾸어 행 수를 세시오.
--     UNION 결과 행 수와 왜 다른지 주석으로 설명할 것.
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q3. 카테고리 21(노트북)과 23(주변기기)에서
--     각각 "최고가 상품 1개씩"만 뽑아 하나의 결과로 출력하시오.
--     컬럼: category_id, name, price  (총 2행)
--     힌트: 괄호 + ORDER BY + LIMIT
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q4. INTERSECT 로
--     "후기가 달린 상품" ∩ "재고(stock)가 100개 이상인 상품" 을 구하시오.
--     컬럼: product_id / 정렬: product_id
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q5. EXCEPT 로
--     "주문된 적 있는 상품(order_items)" - "후기가 달린 상품(reviews)" 을 구하시오.
--     컬럼: product_id / 정렬: product_id
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q6. Q5 와 같은 결과를 NOT EXISTS 로 구하고,
--     두 방법의 결과 건수가 같은지 확인하시오.
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q7. orders 와 payments 를 FULL OUTER JOIN 처럼 합치시오.
--     (결제 없는 주문 + 주문 없는 결제 + 매칭된 것 모두)
--     컬럼: order_id, order_status, payment_id, pay_status
--     정렬: order_id / 상위 5행
-- ---------------------------------------------------------------------


-- ---------------------------------------------------------------------
-- Q8. 등급(grade)별 고객 수를 구하고, 맨 아래에 '__TOTAL__' 합계 행을 붙이시오.
--     컬럼: grade, cnt
-- ---------------------------------------------------------------------

-- =====================================================================
-- Step 10 — 집합 연산 : 정답 + 해설
-- =====================================================================
USE shop;

-- ---------------------------------------------------------------------
-- A1. UNION 은 중복을 제거한다.
--     맨 끝 ORDER BY 는 전체 결과에 적용되며, 첫 SELECT 의 컬럼명(city)을 쓴다.
-- ---------------------------------------------------------------------
SELECT city FROM customers WHERE grade = 'VIP'
UNION
SELECT city FROM customers WHERE grade = 'GOLD'
ORDER BY city;
-- → 대구, 부산, 서울, 인천 (4행)

-- ---------------------------------------------------------------------
-- A2. UNION ALL 은 중복을 제거하지 않는다.
--     VIP 5명 + GOLD 8명 = 13행이 그대로 나온다.
--     같은 도시(서울 등)가 여러 번 등장하기 때문에 UNION(4행)과 차이가 난다.
--     → 중복 제거가 필요 없다면 UNION ALL 이 항상 빠르다
--       (임시테이블 없이 Append 로 스트리밍하기 때문).
-- ---------------------------------------------------------------------
SELECT COUNT(*) AS union_all_cnt FROM (
    SELECT city FROM customers WHERE grade = 'VIP'
    UNION ALL
    SELECT city FROM customers WHERE grade = 'GOLD'
) x;
-- → 13

-- ---------------------------------------------------------------------
-- A3. 개별 SELECT 에 ORDER BY / LIMIT 를 걸려면 반드시 괄호로 감싼다.
--     괄호가 없으면 ORDER BY 가 전체 결과에 적용되어 의도와 달라진다.
--     (그룹 수가 적을 때 쓸 수 있는 "그룹별 Top-N" 기법)
-- ---------------------------------------------------------------------
(SELECT category_id, name, price FROM products WHERE category_id = 21 ORDER BY price DESC LIMIT 1)
UNION ALL
(SELECT category_id, name, price FROM products WHERE category_id = 23 ORDER BY price DESC LIMIT 1);
-- → 게이밍 노트북 RTX4060(2,190,000) / 27인치 4K 모니터(459,000)

-- ---------------------------------------------------------------------
-- A4. INTERSECT (8.0.31+). 양쪽 모두에 있는 product_id.
-- ---------------------------------------------------------------------
SELECT product_id FROM reviews
INTERSECT
SELECT product_id FROM products WHERE stock >= 100
ORDER BY product_id;
-- → 1, 18, 24, 28, 38 (5행)

-- ---------------------------------------------------------------------
-- A5. EXCEPT (8.0.31+). 왼쪽에는 있고 오른쪽에는 없는 것.
--     EXCEPT 는 왼쪽의 중복을 자동으로 제거하므로 DISTINCT 를 따로 쓸 필요가 없다.
-- ---------------------------------------------------------------------
SELECT product_id FROM order_items
EXCEPT
SELECT product_id FROM reviews
ORDER BY product_id;
-- → 24행 (모든 상품이 한 번은 주문됐으므로, 후기 없는 상품 24개와 같다)

-- ---------------------------------------------------------------------
-- A6. NOT EXISTS 버전. 결과 건수가 A5 와 같아야 한다.
--     차이점:
--       - EXCEPT     : 집합 연산. NULL 을 "같은 값"으로 취급 → NOT IN 함정 없음
--       - NOT EXISTS : 상관 서브쿼리. 역시 NULL 에 안전
--       - NOT IN     : NULL 이 섞이면 0행 → 쓰지 말 것
-- ---------------------------------------------------------------------
SELECT COUNT(*) AS cnt_except FROM (
    SELECT product_id FROM order_items
    EXCEPT
    SELECT product_id FROM reviews
) x;
-- → 24

SELECT COUNT(DISTINCT oi.product_id) AS cnt_not_exists
FROM order_items oi
WHERE NOT EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = oi.product_id);
-- → 24  (동일)

-- ---------------------------------------------------------------------
-- A7. FULL OUTER JOIN 흉내내기.
--     LEFT JOIN UNION RIGHT JOIN.
--     반드시 UNION(중복 제거)이어야 한다. UNION ALL 이면 매칭된 행이 두 번 나온다.
--     * 이 스키마는 payments.order_id 에 FK 가 있어 "주문 없는 결제"는 존재할 수 없다.
--       따라서 실질적으로는 LEFT JOIN 결과와 같지만, 패턴 자체를 익히는 것이 목적이다.
-- ---------------------------------------------------------------------
SELECT o.order_id, o.status AS order_status, p.payment_id, p.status AS pay_status
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
UNION
SELECT o.order_id, o.status, p.payment_id, p.status
FROM orders o
RIGHT JOIN payments p ON p.order_id = o.order_id
ORDER BY order_id
LIMIT 5;

-- ---------------------------------------------------------------------
-- A8. UNION ALL 로 합계 행 붙이기.
--     합계 행의 grade 자리에 문자열 리터럴을 넣는다.
--     grade 는 ENUM 이지만 UNION 결과 타입은 문자열로 승격되므로 문제없다.
--     정렬 트릭: 합계를 맨 아래로 보내려면 정렬용 컬럼을 하나 더 만든다.
-- ---------------------------------------------------------------------
SELECT grade, cnt FROM (
    SELECT grade AS grade, COUNT(*) AS cnt, 0 AS sort_key
    FROM customers
    GROUP BY grade
    UNION ALL
    SELECT '__TOTAL__', COUNT(*), 1
    FROM customers
) x
ORDER BY sort_key, cnt DESC;
-- → BRONZE(10) GOLD(8) SILVER(7) VIP(5), 그리고 마지막 줄에 __TOTAL__(30)