MySQL Advanced Joins

Types of Advanced Joins

SELECT * FROM table t1
JOIN table t2 
ON t1.id = t2.parent_id;

SELECT * 
FROM colors
CROSS JOIN sizes;

SELECT * FROM orders
JOIN customers ON ...
JOIN products ON ...;

SELECT * 
FROM table1
NATURAL JOIN table2;

🔹 Self Join

A self join connects a table to itself, useful for hierarchical data like employee-manager relationships or comparing rows within the same table. You must use table aliases to distinguish between the two instances of the same table in your query.

-- Find employees and their managers
SELECT 
    e.name AS Employee,
    e.position AS Position,
    m.name AS Manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find employees in the same department
SELECT 
    e1.name AS Employee1,
    e2.name AS Employee2,
    e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department
WHERE e1.id < e2.id;

🔹 Cross Join

Cross join produces a cartesian product, combining every row from the first table with every row from the second table. This is useful for generating all possible combinations, like creating a product catalog with all size and color variations.

-- Generate all product variations
SELECT 
    p.name AS Product,
    c.color_name AS Color,
    s.size_name AS Size
FROM products p
CROSS JOIN colors c
CROSS JOIN sizes s;

-- Alternative syntax
SELECT * 
FROM table1, table2
WHERE condition;

🔹 Multiple Table Joins

Join three or more tables to combine related data from different sources. Each join connects two tables at a time, building a chain of relationships. This is essential for complex queries involving orders, customers, products, and other related entities.

-- Join orders, customers, and products
SELECT 
    o.order_id,
    c.customer_name,
    p.product_name,
    o.quantity,
    o.total_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
WHERE o.order_date >= '2024-01-01';

🔹 Natural Join

Natural join automatically matches columns with the same name in both tables. While convenient, it can be risky if table structures change. It's better to explicitly specify join conditions for clarity and maintainability in production code.

-- Natural join (auto-matches column names)
SELECT *
FROM customers
NATURAL JOIN orders;

-- Equivalent to:
SELECT *
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

🔹 Join with Aggregation

Combine joins with aggregate functions to calculate summaries across related tables. This technique is powerful for generating reports like total sales per customer or average order value by product category. Use GROUP BY to organize results properly.

-- Total orders per customer
SELECT 
    c.customer_name,
    COUNT(o.order_id) AS total_orders,
    SUM(o.total_price) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name
ORDER BY total_spent DESC;

🔹 Join with Subquery

Combine joins with subqueries to filter or calculate data before joining. This approach helps optimize complex queries by pre-filtering data or computing values that will be used in the join condition, improving query performance and readability.

-- Join with subquery to find high-value customers
SELECT 
    c.customer_name,
    c.email,
    high_value.total_spent
FROM customers c
JOIN (
    SELECT customer_id, SUM(total_price) AS total_spent
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total_price) > 1000
) AS high_value ON c.id = high_value.customer_id;

🔹 Using USING Clause

The USING clause simplifies join syntax when columns have identical names in both tables. It's cleaner than ON and automatically eliminates duplicate columns in the result set. This works only when the join column names match exactly in both tables.

-- Using USING clause
SELECT *
FROM orders
JOIN customers USING (customer_id)
JOIN products USING (product_id);

-- Equivalent to:
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;