07-joins.sql

-- ============================================================
-- SQL Masterclass — Chapter 07: JOINs
-- ============================================================
-- 🟡 INTERMEDIATE
--
-- In this chapter you will learn:
--   • INNER JOIN — matching rows from both tables
--   • LEFT JOIN — all rows from left + matches from right
--   • RIGHT JOIN — all rows from right + matches from left
--   • FULL OUTER JOIN — all rows from both tables
--   • CROSS JOIN — cartesian product
--   • Self-joins — joining a table to itself
--   • Multi-table joins — combining 3+ tables
--   • Table aliases for readability
-- ============================================================


-- ============================================================
-- 7.1  INNER JOIN — Only matching rows
-- ============================================================
-- Returns rows only where the join condition matches in BOTH tables.

-- Get orders with customer details
SELECT
    o.order_id,
    o.order_status,
    o.order_purchase_timestamp,
    c.customer_city,
    c.customer_state
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
LIMIT 10;


-- Get order items with product details
SELECT
    oi.order_id,
    oi.price,
    p.product_category_name,
    p.product_weight_g
FROM order_items oi
INNER JOIN products p ON oi.product_id = p.product_id
LIMIT 10;


-- ============================================================
-- 7.2  LEFT JOIN — All from left, matches from right
-- ============================================================
-- Returns ALL rows from the left table.
-- If no match in right table, NULL is returned.

-- All products, even those never sold
SELECT
    p.product_id,
    p.product_category_name,
    oi.order_id,
    oi.price
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LIMIT 20;


-- Find products that were NEVER sold
SELECT
    p.product_id,
    p.product_category_name
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL
LIMIT 10;

-- Count unsold products
SELECT COUNT(*) AS unsold_products
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;


-- ============================================================
-- 7.3  RIGHT JOIN — All from right, matches from left
-- ============================================================
-- Returns ALL rows from the right table.
-- Less commonly used — you can always rewrite as a LEFT JOIN.

-- All order items with their reviews (if any)
SELECT
    oi.order_id,
    oi.product_id,
    oi.price,
    r.review_score
FROM order_reviews r
RIGHT JOIN order_items oi ON r.order_id = oi.order_id
LIMIT 20;

-- Equivalent LEFT JOIN version (preferred):
SELECT
    oi.order_id,
    oi.product_id,
    oi.price,
    r.review_score
FROM order_items oi
LEFT JOIN order_reviews r ON oi.order_id = r.order_id
LIMIT 20;


-- ============================================================
-- 7.4  FULL OUTER JOIN — All from both
-- ============================================================
-- Returns ALL rows from BOTH tables.
-- NULLs where there's no match on either side.
-- Note: SQLite doesn't support FULL OUTER JOIN natively.

-- Find mismatches between orders and payments
-- (orders without payment OR payments without orders)
SELECT
    o.order_id  AS order_order_id,
    p.order_id  AS payment_order_id,
    o.order_status,
    p.payment_value
FROM orders o
FULL OUTER JOIN order_payments p ON o.order_id = p.order_id
WHERE o.order_id IS NULL OR p.order_id IS NULL
LIMIT 20;


-- ============================================================
-- 7.5  CROSS JOIN — Cartesian product
-- ============================================================
-- Every row from left × every row from right.
-- Rarely used but powerful for generating combinations.

-- All possible state pairs (e.g., for a distance matrix)
SELECT DISTINCT
    c.customer_state AS buyer_state,
    s.seller_state   AS seller_state
FROM (SELECT DISTINCT customer_state FROM customers) c
CROSS JOIN (SELECT DISTINCT seller_state FROM sellers) s
ORDER BY buyer_state, seller_state
LIMIT 20;


-- ============================================================
-- 7.6  SELF JOIN — Joining a table to itself
-- ============================================================
-- Useful for comparing rows within the same table.

-- Find pairs of sellers in the same city
SELECT
    s1.seller_id   AS seller_1,
    s2.seller_id   AS seller_2,
    s1.seller_city AS city,
    s1.seller_state AS state
FROM sellers s1
INNER JOIN sellers s2
    ON s1.seller_city = s2.seller_city
    AND s1.seller_state = s2.seller_state
    AND s1.seller_id < s2.seller_id   -- avoid duplicates and self-pairs
LIMIT 15;


-- ============================================================
-- 7.7  MULTI-TABLE JOINS — Combining 3+ tables
-- ============================================================

-- Full order details: customer + order + items + product
SELECT
    c.customer_state,
    c.customer_city,
    o.order_id,
    o.order_status,
    oi.price,
    oi.freight_value,
    p.product_category_name
FROM orders o
JOIN customers c   ON o.customer_id  = c.customer_id
JOIN order_items oi ON o.order_id    = oi.order_id
JOIN products p    ON oi.product_id  = p.product_id
LIMIT 10;


-- Complete order view: customer → order → item → seller → product → review
SELECT
    c.customer_state    AS buyer_state,
    s.seller_state      AS seller_state,
    p.product_category_name,
    oi.price,
    r.review_score,
    o.order_purchase_timestamp
FROM orders o
JOIN customers c    ON o.customer_id   = c.customer_id
JOIN order_items oi ON o.order_id      = oi.order_id
JOIN products p     ON oi.product_id   = p.product_id
JOIN sellers s      ON oi.seller_id    = s.seller_id
LEFT JOIN order_reviews r ON o.order_id = r.order_id
WHERE o.order_status = 'delivered'
LIMIT 10;


-- Product categories with English names
SELECT
    p.product_category_name,
    t.product_category_name_english AS category_english,
    COUNT(*) AS items_sold,
    SUM(oi.price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
WHERE p.product_category_name IS NOT NULL
GROUP BY p.product_category_name, t.product_category_name_english
ORDER BY total_revenue DESC
LIMIT 15;


-- ============================================================
-- 7.8  JOIN FOR ANALYTICS — Real-world patterns
-- ============================================================

-- Cross-state commerce: Where do sellers ship to?
SELECT
    s.seller_state   AS from_state,
    c.customer_state AS to_state,
    COUNT(*)         AS num_orders,
    SUM(oi.price)    AS total_revenue
FROM order_items oi
JOIN orders o    ON oi.order_id    = o.order_id
JOIN customers c ON o.customer_id  = c.customer_id
JOIN sellers s   ON oi.seller_id   = s.seller_id
GROUP BY s.seller_state, c.customer_state
ORDER BY num_orders DESC
LIMIT 15;


-- Average review by product category (English names)
SELECT
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    COUNT(r.review_id)  AS num_reviews,
    AVG(r.review_score) AS avg_score
FROM order_items oi
JOIN products p  ON oi.product_id = p.product_id
JOIN orders o    ON oi.order_id   = o.order_id
LEFT JOIN order_reviews r ON o.order_id = r.order_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
WHERE p.product_category_name IS NOT NULL
GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
HAVING COUNT(r.review_id) > 100
ORDER BY avg_score DESC
LIMIT 10;


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

-- Exercise 1: Join orders with order_payments. Show order_id,
--             order_status, payment_type, and payment_value.
--             Only show delivered orders. Limit to 15 rows.

-- Exercise 2: Find all sellers who have never sold anything.
--             (Hint: LEFT JOIN + IS NULL)

-- Exercise 3: For each customer state, show the total number of
--             orders and total payment value. Order by value DESC.

-- Exercise 4: Join order_items with products and translations.
--             Show the English category name, count of items sold,
--             and average price. Show top 10 by items sold.

-- Exercise 5: Find the top 5 seller-customer state pairs with
--             the highest total revenue.


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

-- Exercise 1
SELECT
    o.order_id,
    o.order_status,
    p.payment_type,
    p.payment_value
FROM orders o
JOIN order_payments p ON o.order_id = p.order_id
WHERE o.order_status = 'delivered'
LIMIT 15;

-- Exercise 2
SELECT
    s.seller_id,
    s.seller_city,
    s.seller_state
FROM sellers s
LEFT JOIN order_items oi ON s.seller_id = oi.seller_id
WHERE oi.order_id IS NULL;

-- Exercise 3
SELECT
    c.customer_state,
    COUNT(DISTINCT o.order_id) AS num_orders,
    SUM(p.payment_value)       AS total_payment
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_payments p ON o.order_id = p.order_id
GROUP BY c.customer_state
ORDER BY total_payment DESC;

-- Exercise 4
SELECT
    COALESCE(t.product_category_name_english, p.product_category_name) AS category,
    COUNT(*)     AS items_sold,
    AVG(oi.price) AS avg_price
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
LEFT JOIN product_category_name_translation t
    ON p.product_category_name = t.product_category_name
WHERE p.product_category_name IS NOT NULL
GROUP BY COALESCE(t.product_category_name_english, p.product_category_name)
ORDER BY items_sold DESC
LIMIT 10;

-- Exercise 5
SELECT
    s.seller_state  AS seller_state,
    c.customer_state AS customer_state,
    SUM(oi.price)    AS total_revenue
FROM order_items oi
JOIN orders o    ON oi.order_id   = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
JOIN sellers s   ON oi.seller_id  = s.seller_id
GROUP BY s.seller_state, c.customer_state
ORDER BY total_revenue DESC
LIMIT 5;