07-joins.md

Chapter 07: JOINs

In a relational database, data is spread across multiple tables to reduce redundancy. JOINs are the mechanism for stitching that data back together. Understanding the different types of joins is critical for effective data analysis.

7.1 INNER JOIN — Only Matching Rows

The INNER JOIN returns records that have matching values in both tables. It's the most common join type.

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

7.2 LEFT JOIN — All from Left, Matches from Right

The LEFT JOIN returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL for the right side.

-- 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;

7.3 FULL OUTER JOIN — All from Both

The FULL OUTER JOIN returns all records when there is a match in either left or right table records. ⚠️ Note: Not all databases (like SQLite) support this natively, but it's essential for identifying mismatches between tables.

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;

7.4 SELF JOIN — Joining a Table to Itself

A self-join is a regular join, but the table is joined with itself. This is useful for comparing rows within the same table or for hierarchical data.

-- 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
FROM sellers s1
INNER JOIN sellers s2
    ON s1.seller_city = s2.seller_city
    AND s1.seller_id < s2.seller_id; -- Avoid duplicates and self-pairs

7.5 Multi-Table Joins — Combining 3+ Tables

You can chain multiple JOIN clauses together to gather data from many tables at once.

-- Complete order view
SELECT
    c.customer_state    AS buyer_state,
    s.seller_state      AS seller_state,
    p.product_category_name,
    oi.price,
    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
WHERE o.order_status = 'delivered'
LIMIT 10;

---

Exercises

1. Join orders with order_payments to show order_id, order_status, payment_type, and payment_value for delivered orders.
2. Find all sellers who have never sold anything.
3. Show the total number of orders and total payment value for each customer state.
4. Join order_items with products and translations to show the English category name, items sold, and average price for the top 10 categories.
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 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), SUM(p.payment_value) 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 1;

-- Exercise 4
SELECT COALESCE(t.product_category_name_english, p.product_category_name) AS category, COUNT(*), AVG(oi.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 GROUP BY 1 ORDER BY 2 DESC LIMIT 10;

-- Exercise 5
SELECT s.seller_state, c.customer_state, SUM(oi.price) 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 1, 2 ORDER BY 3 DESC LIMIT 5;