Lesson 4: Joining Related Tables

Why are joins needed?

Data is split into related tables to avoid storing the same information repeatedly (normalisation).

• orders holds customer_id
• customers holds customer_name and region
• A join combines them so you can analyse orders by customer region.

Common join types

Example

SELECT o.order_id,
c.customer_name,
o.total_amount
FROM orders AS o
LEFT JOIN customers AS c
ON o.customer_id = c.customer_id;

Finding customers with no orders:

SELECT c.customer_id, c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

How it works

• Aliases (o, c) shorten table names and make it clear which table each column comes from.
• The ON condition defines how rows match.
• Row multiplication: if the right table has several rows per key, each left row is repeated, which inflates totals.

Join safety checklist:

1. Check key uniqueness: SELECT customer_id, COUNT(*) FROM customers GROUP BY customer_id HAVING COUNT(*) > 1;
2. Count rows before and after the join.
3. Reconcile a total (e.g., revenue) before and after the join.

Common mistakes