Joins

Combine tables with INNER, LEFT and self joins.

Good database design splits data across tables so nothing is duplicated: a customer's name lives once in customers, not on every order. A join stitches related rows back together at query time, matching a foreign key to a primary key.

INNER JOIN

Returns only rows that have a match in both tables.

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

name  | order_id | total
Ada   | 10       | 120.0
Ada   | 11       | 35.5
Grace | 12       | 80.0

Linus has no orders, so he does not appear. The short names c and o are table aliases; they keep queries readable and disambiguate columns that share a name, like id.

LEFT JOIN

Keeps every row from the left table, filling the right side with NULL when there is no match. Use it to answer "who has none?" questions.

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

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

name
Linus

Join types at a glance

• INNER: only matches.
• LEFT: all left rows, matches from the right.
• RIGHT: mirror of LEFT (rarely needed; swap the table order instead).
• FULL OUTER: all rows from both sides.
• CROSS: every combination; use with care, it multiplies row counts.

SELECT c.name, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC;

Self join

A table can join to itself, for example to pair each employee with their manager (both live in employees).

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

SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'refunded';