SQL · Lesson 4 of 7

Joins

Combine tables with INNER, LEFT and self joins.

• Intermediate
• 17 min read
• 3 objectives

Before this lessonLesson 3: Sorting and Aggregates

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.

Why data is split across tables

Imagine storing the customer's name, country and age on every one of their orders. If Ada moves country you would have to fix hundreds of rows, and miss one. Good design keeps each fact in exactly one place: customers in one table, orders in another, linked by an id. A join temporarily stitches those tables back together when you need an answer that spans both.

Picture two lists on a table. A join lays a line from each order to its customer wherever the ids match, then reads across.

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.

Joining and aggregating

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;

INNER JOIN: only rows that match on both sides

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 placed no orders, so he does not appear. An inner join keeps only the pairs that have a match. The ON clause is the matching rule, and c and o are short aliases so we can write c.name and o.total without ambiguity.

LEFT JOIN: keep everyone from the left table

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

name  | order_id | total
------+----------+------
Ada   | 11       | 35.5
Ada   | 10       | 120.0
Linus | NULL     | NULL
Grace | 12       | 80.0

Now Linus appears, with NULL where the order columns would be. Use a left join when you want "all customers, and their orders if they have any".

The classic use: find what is missing

SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL

name
-----
Linus

"Customers who never ordered" is a left join plus a check for NULL on the right side. Learn this pattern; you will use it constantly.

Joining and summarising together

SELECT c.name, COUNT(o.id) AS orders, 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

name  | orders | spent
------+--------+------
Ada   | 2      | 155.5
Grace | 1      | 80.0
Linus | 0      | 0

COUNT(o.id) counts only real orders (it ignores NULL), and COALESCE replaces a missing sum with 0. This single query is a real customer report.

Debugging a join that returns too many rows

• Forgot the ON clause: every row pairs with every row (a cartesian product).
• Joining on the wrong column: check that you match a foreign key to the primary key it points at.
• Duplicate matches: if the right table has several matching rows, the left row repeats. That is correct, but it inflates sums, so aggregate carefully.

Key takeaways

• Join tables on a foreign key = primary key match using ON.
• INNER JOIN keeps matches only; LEFT JOIN keeps every left row, using NULL when nothing matches.
• Left join plus IS NULL finds rows with no partner.
• Aliases (c, o) keep multi-table queries readable.

-- Write your solution here