Multiple Joins

Three-Table Joins

Joining three or more tables is common in real-world queries. You chain JOIN clauses to combine data from multiple sources.

Basic Syntax

SELECT columns
FROM table1
INNER JOIN table2 ON table1.col = table2.col
INNER JOIN table3 ON table2.col = table3.col;

Sample Schema

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

INSERT INTO customers VALUES (1, 'Alice', 'New York'), (2, 'Bob', 'London');
INSERT INTO orders VALUES (101, 1, '2024-01-15'), (102, 2, '2024-01-16');
INSERT INTO order_items VALUES (1, 101, 1, 2), (2, 101, 2, 1), (3, 102, 1, 3);
INSERT INTO products VALUES (1, 'Laptop', 'Electronics', 999.99), (2, 'Mouse', 'Electronics', 29.99);

Three-Table Join Examples

-- Complete order information
SELECT 
    c.name AS customer,
    c.city,
    o.order_id,
    o.order_date,
    p.name AS product,
    oi.quantity,
    p.price,
    oi.quantity * p.price AS line_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;

-- Result:
-- Alice, New York, 101, 2024-01-15, Laptop, 2, 999.99, 1999.98
-- Alice, New York, 101, 2024-01-15, Mouse, 1, 29.99, 29.99
-- Bob, London, 102, 2024-01-16, Laptop, 3, 999.99, 2999.97

Joining with Mixed Join Types

-- All customers with their orders and products (including customers without orders)
SELECT 
    c.name AS customer,
    o.order_id,
    p.name AS product,
    oi.quantity
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;

-- All products with order info (including unsold products)
SELECT 
    p.name AS product,
    p.category,
    COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
GROUP BY p.product_id, p.name, p.category;

Three-Table Join Best Practices

1. Join in logical order - Follow the relationship chain
2. Use aliases for readability
3. Specify join types explicitly
4. Filter with WHERE after joins

-- Good: Clear join chain
SELECT 
    c.name,
    o.order_id,
    p.name AS product,
    oi.quantity * p.price AS total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01'
ORDER BY c.name, o.order_date;

Three-table joins are essential for querying normalized databases.

Join Order

The order in which you join tables can affect performance and sometimes results. Understanding join order helps optimize queries.

Join Order for Performance

-- Join order: Start with smallest/filtered tables
-- Bad: Join large tables first
SELECT c.name, p.name, oi.quantity
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE c.city = 'New York';

-- Better: Filter customers first, then join
SELECT c.name, p.name, oi.quantity
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE c.city = 'New York';

Join Order for Correctness

-- INNER JOIN: Order doesn't affect results
-- A INNER JOIN B = B INNER JOIN A

-- OUTER JOIN: Order matters!
-- A LEFT JOIN B ≠ B LEFT JOIN A

-- Example:
-- customers LEFT JOIN orders: All customers, some orders
-- orders LEFT JOIN customers: All orders, some customers

-- Correct order for all customers with orders
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Different result: all orders with customers
SELECT c.name, o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Join Order with Multiple Tables

-- Complex join chain
SELECT 
    c.name,
    o.order_id,
    oi.quantity,
    p.name AS product,
    cat.name AS category
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN categories cat ON p.category_id = cat.category_id
WHERE c.city = 'New York'
  AND o.order_date >= '2024-01-01'
  AND p.price > 100;

-- Execution order:
-- 1. Filter customers (city = 'New York')
-- 2. Join orders (filtered by date)
-- 3. Join order_items
-- 4. Join products (filtered by price)
-- 5. Join categories

Join Order Best Practices

1. Start with filtered tables - Apply WHERE conditions early
2. Join primary keys to foreign keys - Follow relationship chain
3. Consider table sizes - Join smaller tables first
4. Use EXPLAIN to verify execution plan

-- Good: Filter then join
SELECT c.name, o.order_id, p.name AS product
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE c.city = 'New York'
  AND o.order_date >= '2024-01-01';

-- Check execution plan
EXPLAIN SELECT c.name, o.order_id, p.name AS product
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE c.city = 'New York'
  AND o.order_date >= '2024-01-01';

Join order is crucial for OUTER JOINs and performance optimization.

Mixing Join Types

You can mix INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN in a single query. This provides flexibility for complex data requirements.

Mixed Join Examples

-- All customers (LEFT) with their orders (INNER) and products (LEFT)
SELECT 
    c.name AS customer,
    o.order_id,
    p.name AS product,
    oi.quantity
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;

-- Analysis:
-- 1. LEFT JOIN orders: All customers, some orders
-- 2. INNER JOIN order_items: Only customers with items
-- 3. LEFT JOIN products: All items, some products

Mixed Join for Reports

-- Complete product analysis
SELECT 
    p.name AS product,
    p.category,
    COALESCE(SUM(oi.quantity), 0) AS total_sold,
    COALESCE(SUM(oi.quantity * p.price), 0) AS revenue,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id
LEFT JOIN customers c ON o.customer_id = c.customer_id
GROUP BY p.product_id, p.name, p.category;

Mixed Join with Subqueries

-- Customers with above-average spending
SELECT 
    c.name,
    c.city,
    total_spent,
    avg_order
FROM customers c
INNER JOIN (
    SELECT 
        customer_id,
        SUM(total) AS total_spent,
        AVG(total) AS avg_order
    FROM orders
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id
WHERE total_spent > (SELECT AVG(total) FROM orders);

-- Products in categories with high sales
SELECT 
    p.name,
    p.price,
    cat.total_sales
FROM products p
INNER JOIN categories cat ON p.category_id = cat.category_id
INNER JOIN (
    SELECT category_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY category_id
    HAVING SUM(amount) > 10000
) high_cats ON cat.category_id = high_cats.category_id;

Mixed Join Best Practices

1. Document join logic - Comment complex join chains
2. Use parentheses for clarity
3. Test with small datasets first
4. Consider performance - Each join adds complexity

-- Good: Clear mixed joins with comments
SELECT 
    c.name AS customer,
    -- All customers (LEFT JOIN)
    o.order_id,
    -- Only orders with items (INNER JOIN)
    p.name AS product,
    -- All items with products (LEFT JOIN)
    oi.quantity * p.price AS total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date >= '2024-01-01';

Mixed joins provide maximum flexibility for complex queries.

Complex Real-World Queries

Here are examples of complex multi-join queries commonly used in production systems.

E-Commerce Analytics

-- Monthly revenue by category and region
SELECT 
    DATE_FORMAT(o.order_date, '%Y-%m') AS month,
    cat.name AS category,
    r.name AS region,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    SUM(oi.quantity * p.price) AS revenue
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN categories cat ON p.category_id = cat.category_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN regions r ON c.region_id = r.region_id
WHERE o.order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(o.order_date, '%Y-%m'), cat.name, r.name
ORDER BY month, revenue DESC;

Employee Reporting Structure

-- Full employee hierarchy with department info
SELECT 
    CONCAT(e.first_name, ' ', e.last_name) AS employee,
    d.name AS department,
    CONCAT(m.first_name, ' ', m.last_name) AS manager,
    md.name AS manager_department,
    CONCAT(gm.first_name, ' ', gm.last_name) AS grand_manager
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN employees m ON e.manager_id = m.emp_id
LEFT JOIN departments md ON m.dept_id = md.dept_id
LEFT JOIN employees gm ON m.manager_id = gm.emp_id
ORDER BY d.name, e.last_name;

Inventory Management

-- Products with stock levels and sales velocity
SELECT 
    p.name AS product,
    p.sku,
    cat.name AS category,
    w.name AS warehouse,
    i.quantity AS current_stock,
    COALESCE(s.monthly_sales, 0) AS avg_monthly_sales,
    CASE 
        WHEN COALESCE(s.monthly_sales, 0) = 0 THEN 'No Sales'
        WHEN i.quantity / COALESCE(s.monthly_sales, 1) > 3 THEN 'Overstocked'
        WHEN i.quantity / COALESCE(s.monthly_sales, 1) < 1 THEN 'Understocked'
        ELSE 'Optimal'
    END AS stock_status
FROM products p
INNER JOIN categories cat ON p.category_id = cat.category_id
INNER JOIN inventory i ON p.product_id = i.product_id
INNER JOIN warehouses w ON i.warehouse_id = w.warehouse_id
LEFT JOIN (
    SELECT 
        product_id,
        SUM(quantity) / COUNT(DISTINCT DATE_FORMAT(order_date, '%Y-%m')) AS monthly_sales
    FROM order_items oi
    INNER JOIN orders o ON oi.order_id = o.order_id
    WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
    GROUP BY product_id
) s ON p.product_id = s.product_id
ORDER BY cat.name, p.name;

Performance Optimization

-- Add indexes for join columns
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);

-- Use EXPLAIN to verify plan
EXPLAIN SELECT c.name, o.order_id, p.name AS product
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE c.city = 'New York'
  AND o.order_date >= '2024-01-01';

-- Consider covering indexes
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, order_id);

Query Structure Best Practices

-- Good: Well-structured complex query
SELECT 
    -- Main columns
    c.name AS customer,
    c.city,
    COUNT(DISTINCT o.order_id) AS order_count,
    COALESCE(SUM(oi.quantity * p.price), 0) AS total_spent,
    COALESCE(AVG(oi.quantity * p.price), 0) AS avg_order_value
FROM customers c
-- Join orders
LEFT JOIN orders o 
    ON c.customer_id = o.customer_id
    AND o.order_date >= '2024-01-01'
-- Join order items
LEFT JOIN order_items oi 
    ON o.order_id = oi.order_id
-- Join products
LEFT JOIN products p 
    ON oi.product_id = p.product_id
-- Join categories
LEFT JOIN categories cat 
    ON p.category_id = cat.category_id
-- Filter conditions
WHERE c.city IN ('New York', 'London', 'Paris')
-- Group for aggregation
GROUP BY c.customer_id, c.name, c.city
-- Filter groups
HAVING total_spent > 100
-- Sort results
ORDER BY total_spent DESC
-- Limit results
LIMIT 20;

Complex joins are the backbone of real-world SQL queries. Practice building them incrementally.