09-set-operations.sql

-- ============================================================
-- SQL Masterclass — Chapter 09: Set Operations
-- ============================================================
-- 🟡 INTERMEDIATE
--
-- In this chapter you will learn:
--   • UNION — combine results, remove duplicates
--   • UNION ALL — combine results, keep duplicates
--   • INTERSECT — rows in both queries
--   • EXCEPT — rows in first but not second
--   • Rules for set operations
--   • Practical use cases
-- ============================================================


-- ============================================================
-- 9.1  UNION — Combine and deduplicate
-- ============================================================
-- Combines results of two queries and removes duplicates.
-- Both queries must have the same number of columns and
-- compatible data types.

-- All unique cities from both customers and sellers
SELECT customer_city AS city, customer_state AS state
FROM customers

UNION

SELECT seller_city AS city, seller_state AS state
FROM sellers

ORDER BY state, city
LIMIT 20;


-- ============================================================
-- 9.2  UNION ALL — Combine and keep duplicates
-- ============================================================
-- Faster than UNION because no deduplication step.
-- Use when you know there are no duplicates or want to keep them.

-- Count of records in each table
SELECT 'customers'       AS table_name, COUNT(*) AS row_count FROM customers
UNION ALL
SELECT 'orders',          COUNT(*) FROM orders
UNION ALL
SELECT 'order_items',     COUNT(*) FROM order_items
UNION ALL
SELECT 'order_payments',  COUNT(*) FROM order_payments
UNION ALL
SELECT 'order_reviews',   COUNT(*) FROM order_reviews
UNION ALL
SELECT 'products',        COUNT(*) FROM products
UNION ALL
SELECT 'sellers',         COUNT(*) FROM sellers
UNION ALL
SELECT 'geolocation',     COUNT(*) FROM geolocation;


-- ============================================================
-- 9.3  UNION ALL FOR TIMELINE EVENTS
-- ============================================================
-- A common pattern: creating a unified event timeline.

-- Order lifecycle events for a specific order
SELECT
    order_id,
    'purchased' AS event,
    order_purchase_timestamp AS event_timestamp
FROM orders
WHERE order_id = (SELECT order_id FROM orders WHERE order_status = 'delivered' LIMIT 1)

UNION ALL

SELECT
    order_id,
    'approved' AS event,
    order_approved_at
FROM orders
WHERE order_id = (SELECT order_id FROM orders WHERE order_status = 'delivered' LIMIT 1)
  AND order_approved_at IS NOT NULL

UNION ALL

SELECT
    order_id,
    'shipped' AS event,
    order_delivered_carrier_date
FROM orders
WHERE order_id = (SELECT order_id FROM orders WHERE order_status = 'delivered' LIMIT 1)
  AND order_delivered_carrier_date IS NOT NULL

UNION ALL

SELECT
    order_id,
    'delivered' AS event,
    order_delivered_customer_date
FROM orders
WHERE order_id = (SELECT order_id FROM orders WHERE order_status = 'delivered' LIMIT 1)
  AND order_delivered_customer_date IS NOT NULL

ORDER BY event_timestamp;


-- ============================================================
-- 9.4  INTERSECT — Rows in BOTH queries
-- ============================================================
-- Returns only rows that appear in BOTH result sets.

-- States that have both customers AND sellers
SELECT customer_state AS state FROM customers
INTERSECT
SELECT seller_state FROM sellers
ORDER BY state;


-- Cities that have both customers and sellers
SELECT customer_city AS city FROM customers
INTERSECT
SELECT seller_city FROM sellers
ORDER BY city
LIMIT 20;


-- ============================================================
-- 9.5  EXCEPT — Rows in first but NOT second
-- ============================================================
-- Returns rows from the first query that don't exist in the second.

-- States with customers but NO sellers
SELECT DISTINCT customer_state AS state FROM customers
EXCEPT
SELECT DISTINCT seller_state FROM sellers
ORDER BY state;


-- States with sellers but NO customers
SELECT DISTINCT seller_state AS state FROM sellers
EXCEPT
SELECT DISTINCT customer_state FROM customers
ORDER BY state;


-- Product categories that exist but have never been sold
SELECT DISTINCT product_category_name FROM products
WHERE product_category_name IS NOT NULL
EXCEPT
SELECT DISTINCT p.product_category_name
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name IS NOT NULL
ORDER BY product_category_name;


-- ============================================================
-- 9.6  COMBINING SET OPERATIONS WITH CTEs
-- ============================================================

-- Compare customer and seller geographic coverage
WITH customer_states AS (
    SELECT DISTINCT customer_state AS state FROM customers
),
seller_states AS (
    SELECT DISTINCT seller_state AS state FROM sellers
),
both_states AS (
    SELECT state FROM customer_states
    INTERSECT
    SELECT state FROM seller_states
),
customer_only AS (
    SELECT state FROM customer_states
    EXCEPT
    SELECT state FROM seller_states
),
seller_only AS (
    SELECT state FROM seller_states
    EXCEPT
    SELECT state FROM customer_states
)
SELECT state, 'Both Customers & Sellers' AS coverage FROM both_states
UNION ALL
SELECT state, 'Customers Only' FROM customer_only
UNION ALL
SELECT state, 'Sellers Only' FROM seller_only
ORDER BY state;


-- ============================================================
-- 9.7  SET OPERATIONS RULES
-- ============================================================
-- 1. Same number of columns in both queries
-- 2. Matching data types (or implicitly castable)
-- 3. Column names come from the FIRST query
-- 4. ORDER BY applies to the FINAL result

-- This WORKS — matching columns
SELECT customer_city AS city, customer_state AS state FROM customers
UNION
SELECT seller_city, seller_state FROM sellers
LIMIT 5;

-- This would FAIL — different number of columns:
-- SELECT customer_city, customer_state, customer_id FROM customers
-- UNION
-- SELECT seller_city, seller_state FROM sellers;


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Use UNION ALL to create a summary showing:
--             'Buyers' + count of distinct customers,
--             'Sellers' + count of distinct sellers

-- Exercise 2: Find cities that are in the customers table but
--             NOT in the sellers table. How many are there?

-- Exercise 3: Use INTERSECT to find product categories that
--             have been both sold (in order_items) AND reviewed
--             (via orders → order_reviews → order_items → products).

-- Exercise 4: Create a unified "entity" list showing all unique
--             cities from customers, sellers, and geolocation
--             tables (using UNION).


-- ============================================================
-- SOLUTIONS
-- ============================================================

-- Exercise 1
SELECT 'Buyers'  AS entity, COUNT(DISTINCT customer_id) AS count FROM customers
UNION ALL
SELECT 'Sellers', COUNT(DISTINCT seller_id) FROM sellers;

-- Exercise 2
SELECT COUNT(*) AS customer_only_cities
FROM (
    SELECT DISTINCT customer_city FROM customers
    EXCEPT
    SELECT DISTINCT seller_city FROM sellers
);

-- Exercise 3
SELECT DISTINCT p.product_category_name
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name IS NOT NULL

INTERSECT

SELECT DISTINCT p.product_category_name
FROM order_reviews r
JOIN orders o ON r.order_id = o.order_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name IS NOT NULL

ORDER BY product_category_name
LIMIT 20;

-- Exercise 4
SELECT DISTINCT customer_city AS city FROM customers
UNION
SELECT DISTINCT seller_city FROM sellers
UNION
SELECT DISTINCT geolocation_city FROM geolocation
ORDER BY city
LIMIT 30;