Joins: Combining Tables

Data is split across tables to avoid duplication; joins put it back together for a query.

INNER JOIN: only matching rows

SELECT o.id, c.full_name, o.status, o.created_at
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Orders without a customer (impossible here thanks to the foreign key) and customers without orders are excluded.

LEFT JOIN: keep everything on the left

-- every customer, with their order count (including zero)
SELECT c.full_name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.full_name;

COUNT(o.id) counts only matched orders; COUNT(*) would count 1 for customers with none.

Finding rows with no match is a classic LEFT JOIN pattern:

-- customers who never ordered
SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Joining three tables

SELECT o.id AS order_id, p.name, oi.quantity,
       oi.quantity * oi.unit_cents / 100.0 AS line_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.status = 'paid'
ORDER BY o.id;

Many-to-many through a join table

SELECT p.title, t.name AS tag
FROM projects p
JOIN project_tags pt ON pt.project_id = p.id
JOIN tags t          ON t.id = pt.tag_id
WHERE t.name = 'flask';

Self joins

A table joined to itself, e.g. employees and their managers:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

The duplicate-row trap

Joining a customer to both orders and support tickets multiplies rows: 3 orders × 4 tickets = 12 rows per customer, and sums double-count. Aggregate each side separately in a subquery or CTE (next module), then join the results.

Exercise

Write queries for: each paid order with its customer name and total; products that have never been ordered; the customer who spent the most.

Check yourself

• When do you use LEFT JOIN instead of JOIN?
• Why does COUNT(o.id) differ from COUNT(*) after a LEFT JOIN?
• How do you find rows with no matching row in another table?