MySQL — Joins

Types of Joins

-- INNER JOIN
SELECT * FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN
SELECT * FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN
SELECT * FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- CROSS JOIN
SELECT * FROM users
CROSS JOIN products;

JOIN Examples

-- Users with their orders
SELECT
    users.name,
    orders.total,
    orders.created_at
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- All users, even without orders
SELECT
    users.name,
    COALESCE(SUM(orders.total), 0) as total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

-- Multiple table join
SELECT
    users.name,
    products.name as product,
    order_items.quantity
FROM users
INNER JOIN orders ON users.id = orders.user_id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

JOIN Best Practices

1. Always specify join condition
2. Use table aliases for readability
3. Index join columns
4. Avoid joining too many tables
5. Use appropriate join type

Mini Practice

Write MySQL code that:

1. Uses INNER JOIN
2. Uses LEFT JOIN
3. Joins multiple tables
4. Uses table aliases

Up Next

Next: Learn about Inner Join.