Joins

Why We Join

Good databases keep data in separate tables. Joins re-combine those tables so you can answer questions that span them, like "which customers bought which products".

The Classic INNER JOIN

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

Only rows with a match on both sides come back.

LEFT JOIN Keeps the Left Side Whole

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Every customer appears, even with no orders; the missing order fields show as NULL.

RIGHT and FULL OUTER

• RIGHT JOIN: keeps every row from the right table.
• FULL OUTER JOIN: keeps every row from both sides (not in MySQL directly).
• CROSS JOIN: every combination of rows - the Cartesian product.

Joining Three Tables

SELECT c.name, o.total, p.name AS product
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Key Points

• JOIN ... ON pairs rows using a joining condition.
• INNER keeps matches; LEFT keeps the left side whole.
• Chain joins for relationships across several tables.
• RIGHT, FULL and CROSS cover the remaining join shapes.