Chapter 4: Advanced Queries and Joins

Understanding Joins

Joins combine rows from multiple tables based on related columns. They're the key to leveraging relational database power.

Inner Join

Returns only rows with matches in both tables:

-- Explicit syntax (preferred)
SELECT c.name, o.order_date, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- Implicit syntax (older style)
SELECT c.name, o.order_date, o.total
FROM customers c, orders o
WHERE c.id = o.customer_id;

Multiple Joins

SELECT
    c.name AS customer,
    o.order_date,
    p.name AS product,
    oi.quantity,
    oi.price
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2024-01-01';

Left Join (Left Outer Join)

Returns all rows from the left table, with NULL for non-matching right rows:

-- Find all customers, including those without orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;

-- Find customers without orders
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

Right Join

Returns all rows from the right table:

-- All orders, including those with deleted customers
SELECT c.name, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

Full Outer Join

MySQL doesn't support FULL OUTER JOIN directly. Simulate with UNION:

-- All customers and all orders
SELECT c.name, o.order_date
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
UNION
SELECT c.name, o.order_date
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

Cross Join

Cartesian product of two tables:

-- Generate all possible product-color combinations
SELECT p.name, c.color
FROM products p
CROSS JOIN colors c;

Self Joins

Join a table to itself:

-- Find employees and their managers
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find duplicate emails
SELECT a.*, b.id AS duplicate_id
FROM customers a
JOIN customers b ON a.email = b.email
WHERE a.id < b.id;

Subqueries

Scalar Subqueries

Return single value:

-- Orders above average
SELECT * FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- Latest order for each customer
SELECT c.name,
    (SELECT MAX(order_date)
     FROM orders o
     WHERE o.customer_id = c.id) AS last_order
FROM customers c;

Column Subqueries

Return single column:

-- Customers who ordered specific product
SELECT * FROM customers
WHERE id IN (
    SELECT DISTINCT customer_id
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    WHERE oi.product_id = 5
);

Row Subqueries

Return multiple columns:

-- Find most recent order per customer
SELECT * FROM orders
WHERE (customer_id, order_date) IN (
    SELECT customer_id, MAX(order_date)
    FROM orders
    GROUP BY customer_id
);

Table Subqueries

Use in FROM clause:

-- Top customers by total spending
SELECT * FROM (
    SELECT
        c.id,
        c.name,
        SUM(o.total) AS total_spent
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    GROUP BY c.id
) AS customer_totals
WHERE total_spent > 1000
ORDER BY total_spent DESC;

Correlated Subqueries

Reference outer query columns:

-- Products priced above category average
SELECT p1.* FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);

EXISTS and NOT EXISTS

Check for row existence:

-- Customers with orders
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

-- Products never ordered
SELECT * FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.id
);

Common Table Expressions (CTEs)

Available in MySQL 8.0+, CTEs make complex queries readable:

-- Basic CTE
WITH high_value_orders AS (
    SELECT * FROM orders
    WHERE total > 100
)
SELECT * FROM high_value_orders
WHERE order_date >= '2024-01-01';

-- Multiple CTEs
WITH
customer_totals AS (
    SELECT
        customer_id,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
),
vip_customers AS (
    SELECT customer_id
    FROM customer_totals
    WHERE total_spent > 1000
)
SELECT c.*
FROM customers c
JOIN vip_customers v ON c.id = v.customer_id;

-- Recursive CTE for hierarchical data
WITH RECURSIVE category_tree AS (
    -- Anchor member
    SELECT id, name, parent_id, 0 AS level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

Window Functions

MySQL 8.0+ supports window functions for advanced analytics:

Ranking Functions

-- Row number
SELECT
    name,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num
FROM products;

-- Rank with ties
SELECT
    name,
    category,
    price,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rank_in_category,
    DENSE_RANK() OVER (ORDER BY price DESC) AS overall_rank
FROM products;

-- Percentile
SELECT
    name,
    price,
    PERCENT_RANK() OVER (ORDER BY price) AS price_percentile
FROM products;

Aggregate Window Functions

-- Running totals
SELECT
    order_date,
    total,
    SUM(total) OVER (ORDER BY order_date) AS running_total,
    AVG(total) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7
FROM orders;

-- Compare to group average
SELECT
    name,
    category,
    price,
    AVG(price) OVER (PARTITION BY category) AS category_avg,
    price - AVG(price) OVER (PARTITION BY category) AS diff_from_avg
FROM products;

Lead and Lag

-- Compare with previous/next row
SELECT
    order_date,
    total,
    LAG(total) OVER (ORDER BY order_date) AS prev_order_total,
    LEAD(total) OVER (ORDER BY order_date) AS next_order_total,
    total - LAG(total) OVER (ORDER BY order_date) AS change_from_prev
FROM orders;

UNION, INTERSECT, and EXCEPT

UNION

Combine result sets:

-- All unique emails from customers and suppliers
SELECT email FROM customers
UNION
SELECT email FROM suppliers;

-- Include duplicates
SELECT email FROM customers
UNION ALL
SELECT email FROM suppliers;

INTERSECT and EXCEPT

Not directly supported in MySQL, simulate with joins:

-- INTERSECT: Customers who are also suppliers
SELECT c.email FROM customers c
INNER JOIN suppliers s ON c.email = s.email;

-- EXCEPT: Customers who are not suppliers
SELECT c.email FROM customers c
LEFT JOIN suppliers s ON c.email = s.email
WHERE s.email IS NULL;

Query Optimization Tips

1. Use EXPLAIN to understand query execution:

EXPLAIN SELECT * FROM orders WHERE customer_id = 1;

2. Index columns used in WHERE, JOIN, ORDER BY

3. **Avoid SELECT *** in production

4. Use LIMIT for large result sets

5. Optimize JOIN order - start with smallest table

6. Avoid functions on indexed columns:

-- Bad: Can't use index
WHERE YEAR(order_date) = 2024

-- Good: Can use index
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

Summary

Advanced queries unlock the full power of relational databases. Joins connect related data, subqueries enable complex logic, CTEs improve readability, and window functions provide powerful analytics. Master these techniques to write efficient, maintainable queries.

---

Next: Chapter 5: Indexes and Performance