JOINs

Chapter 8 — JOINs

Every chapter so far has queried a single table. Real databases split data across multiple related tables — orders live in orders, the customer name lives in customers, the book title lives in books. A JOIN combines rows from two or more tables based on a matching column, letting you query that connected data as if it were one result set.

Why split data across tables? If we stored the customer name inside every order row, changing a customer's name would require updating every one of their orders — and any that were missed would have inconsistent data. By keeping names in one place (customers) and referencing them by ID, a single UPDATE fixes everything. JOINs are the mechanism that puts it back together at query time.

1. Table Aliases — Keeping Queries Readable

JOIN queries reference two or more tables. Qualifying every column name with the full table name (orders.customer_id, customers.customer_id) gets verbose fast. A table alias — a short nickname assigned in the FROM clause — keeps things concise:

-- Without aliases — verbose
SELECT orders.order_id, customers.first_name, customers.last_name
FROM   orders
JOIN   customers ON orders.customer_id = customers.customer_id;

-- With aliases — the standard way
SELECT o.order_id, c.first_name, c.last_name
FROM   orders    o
JOIN   customers c  ON o.customer_id = c.customer_id;

The alias follows the table name in the FROM or JOIN clause, with or without the optional keyword AS. Both forms are identical — orders o and orders AS o mean the same thing.

a→authors

b→books

c→customers

o→orders

We'll use these single-letter aliases consistently throughout this chapter.

2. INNER JOIN — Only Matching Rows

An INNER JOIN returns rows where the ON condition matches in both tables. Rows that have no match on the other side are silently excluded. JOIN and INNER JOIN are identical — INNER is optional.

-- Every order with the customer name and book title
SELECT
    o.order_id,
    c.first_name,
    c.last_name,
    b.title,
    o.quantity,
    o.total_price
FROM        orders    o
INNER JOIN  customers c  ON o.customer_id = c.customer_id
INNER JOIN  books     b  ON o.book_id     = b.book_id
ORDER BY    o.order_id;

order_id	first_name	last_name	title	quantity	total_price
1	Alice	Nguyen	The Midnight Library	2	17.98
2	Alice	Nguyen	Sapiens	1	10.99
3	Ben	Okafor	Dune	1	9.99
4	Chloe	Martinez	And Then There Were None	3	20.97
5	David	Singh	Reasons to Stay Alive	1	7.99

Three tables joined in one query. Customer IDs and book IDs are replaced with the real names and titles.

Joining all four tables — a full order receipt

-- Full receipt: order → customer → book → author
SELECT
    o.order_id,
    o.order_date,
    CONCAT(c.first_name, ' ', c.last_name)  AS customer,
    b.title,
    CONCAT(a.first_name, ' ', a.last_name)  AS author,
    o.quantity,
    o.total_price
FROM       orders    o
JOIN       customers c  ON o.customer_id = c.customer_id
JOIN       books     b  ON o.book_id     = b.book_id
JOIN       authors   a  ON b.author_id   = a.author_id
ORDER BY   o.order_date;

order_id	order_date	customer	title	author	qty	total
1	2024-02-14	Alice Nguyen	The Midnight Library	Matt Haig	2	17.98
2	2024-02-14	Alice Nguyen	Sapiens	Yuval Harari	1	10.99
3	2024-03-05	Ben Okafor	Dune	Frank Herbert	1	9.99
4	2024-03-20	Chloe Martinez	And Then There Were None	Agatha Christie	3	20.97
5	2024-04-10	David Singh	Reasons to Stay Alive	Matt Haig	1	7.99

All four tables joined in one query. Notice David (customer 4) was added in Chapter 5 but never ordered — he won't appear here because INNER JOIN only keeps matched rows.

3. JOIN Types at a Glance

INNER JOIN

Only rows that match in both tables. No match = excluded.

LEFT JOIN

All rows from the left table. Right side NULLs where no match.

RIGHT JOIN

All rows from the right table. Left side NULLs where no match.

LEFT ANTI-JOIN

Left rows with no match on the right. Find orphans / unmatched records.

JOIN type	Keeps from left	Keeps from right	Best for
INNER JOIN	Matched only	Matched only	The common case — you only care about rows that have data on both sides
LEFT JOIN	All rows	Matched rows + NULLs for non-matches	Keep all left rows even without a right-side match — "show all customers, even those with no orders"
RIGHT JOIN	Matched rows + NULLs for non-matches	All rows	Rarely needed — rewrite as LEFT JOIN by swapping table order instead
LEFT JOIN + WHERE right.col IS NULL	Unmatched only	Excluded	Find rows with no match — "customers who have never ordered", "books with no orders"
CROSS JOIN	All rows	All rows	Cartesian product — every left row paired with every right row. Rarely useful; almost always accidental.

4. LEFT JOIN — Keeping Unmatched Left Rows

LEFT JOIN returns all rows from the left table, whether or not they have a matching row in the right table. When there is no match, every column from the right table is NULL in that result row.

-- All customers, with their orders if they have any
SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    o.order_id,
    o.total_price
FROM       customers c
LEFT JOIN  orders    o  ON c.customer_id = o.customer_id
ORDER BY   c.customer_id;

customer_id	first_name	last_name	order_id	total_price
1	Alice	Nguyen	1	17.98
1	Alice	Nguyen	2	10.99
2	Ben	Okafor	3	9.99
3	Chloe	Martinez	4	20.97
4	David	Singh	NULL	NULL
5	David	Singh (if added)	NULL	NULL

David (customer 4) has never ordered — LEFT JOIN still includes him, with NULLs for the orders columns.

LEFT JOIN + aggregate — counting orders per customer including zeros

-- Count orders per customer — include customers with zero orders
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer,
    COUNT(o.order_id)                       AS order_count,
    COALESCE(SUM(o.total_price), 0)          AS total_spent
FROM      customers c
LEFT JOIN orders    o  ON c.customer_id = o.customer_id
GROUP BY  c.customer_id, c.first_name, c.last_name
ORDER BY  total_spent DESC;

customer_id	customer	order_count	total_spent
1	Alice Nguyen	2	28.97
3	Chloe Martinez	1	20.97
2	Ben Okafor	1	9.99
4	David Singh	0	0.00

David appears with 0 orders and £0.00 spent. COUNT(o.order_id) returns 0 (not COUNT(*)) because o.order_id is NULL for David — COUNT(col) skips NULLs. COALESCE converts NULL SUM to 0.00.

COUNT(*) vs COUNT(right_table_col) in a LEFT JOIN. After a LEFT JOIN, unmatched rows have NULL in every right-table column. COUNT(*) would count those NULL rows as 1. Use COUNT(o.order_id) — it skips NULLs and correctly returns 0 for customers with no orders.

5. Anti-Join — Finding Rows With No Match

Combine LEFT JOIN with WHERE right_table.col IS NULL to find rows that have no corresponding entry in the joined table. This is one of the most practical JOIN patterns in real applications:

-- Customers who have never placed an order
SELECT
    c.customer_id,
    c.first_name,
    c.last_name
FROM      customers c
LEFT JOIN orders    o  ON c.customer_id = o.customer_id
WHERE     o.customer_id IS NULL;   -- only keep rows where no order matched

customer_id	first_name	last_name
4	David	Singh

-- Books that have never been ordered
SELECT
    b.book_id,
    b.title
FROM      books  b
LEFT JOIN orders o  ON b.book_id = o.book_id
WHERE     o.book_id IS NULL;

book_id	title
6	The Left Hand of Darkness

The Left Hand of Darkness has never been ordered — a good candidate for a promotion or a stock review.

6. RIGHT JOIN

RIGHT JOIN keeps all rows from the right table and NULLs for the left where there is no match — the mirror image of LEFT JOIN. In practice, RIGHT JOIN is rarely used because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order, which most developers find easier to read:

-- These two queries return identical results:

-- Using RIGHT JOIN
SELECT b.title, o.order_id
FROM        orders o
RIGHT JOIN  books  b  ON o.book_id = b.book_id;

-- Equivalent LEFT JOIN (just swap the table order)
SELECT b.title, o.order_id
FROM       books  b
LEFT JOIN  orders o  ON b.book_id = o.book_id;

Prefer LEFT JOIN over RIGHT JOIN. Both do the same job but most developers read SQL top-to-bottom and find it more natural to start with the "primary" table on the left. Mixing LEFT and RIGHT JOINs in the same query quickly becomes confusing. Stick to LEFT JOIN and swap table order when needed.

7. Self-Join — Joining a Table to Itself

A self-join joins a table to itself using two different aliases. It's used when rows in the same table have a relationship to each other — the classic example is an employees table where each employee has a manager_id that points to another row in the same table:

-- Suppose our authors table had a "mentored_by" column
-- pointing to another author_id in the same table
-- Self-join: show each author alongside their mentor
SELECT
    a.first_name  AS author,
    m.first_name  AS mentor
FROM       authors a
LEFT JOIN  authors m  ON a.mentored_by = m.author_id;

The bookshop schema doesn't have a recursive relationship, but self-joins come up often in organisational charts (employees + managers), category hierarchies (parent category → child category), and graph structures stored in relational tables.

8. Combining JOINs With Everything Else

JOINs compose cleanly with every clause from previous chapters. The execution order stays the same — FROM + JOIN happens first, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT:

Sales report by genre

-- Total revenue and units sold per genre, top genres first
SELECT
    b.genre,
    COUNT(DISTINCT o.order_id)  AS order_lines,
    SUM(o.quantity)              AS units_sold,
    SUM(o.total_price)           AS revenue
FROM     orders o
JOIN     books  b  ON o.book_id = b.book_id
GROUP BY b.genre
ORDER BY revenue DESC;

genre	order_lines	units_sold	revenue
Mystery	1	3	20.97
Fiction	1	2	17.98
Non-fiction	2	2	18.98
Sci-fi	1	1	9.99

Best-selling authors

-- Revenue per author, only those who have sold something
SELECT
    CONCAT(a.first_name, ' ', a.last_name) AS author,
    SUM(o.quantity)                          AS units_sold,
    SUM(o.total_price)                       AS revenue
FROM     authors a
JOIN     books   b  ON a.author_id  = b.author_id
JOIN     orders  o  ON b.book_id    = o.book_id
GROUP BY a.author_id, a.first_name, a.last_name
ORDER BY revenue DESC;

author	units_sold	revenue
Agatha Christie	3	20.97
Matt Haig	3	25.97
Yuval Harari	1	10.99
Frank Herbert	1	9.99

Ursula Le Guin doesn't appear — her book has no orders (INNER JOIN excludes her). Use LEFT JOIN chains if you need to include authors with zero sales.

ON vs WHERE — filtering in the right place

-- Row filter (same for INNER and LEFT JOIN — fine in either place)
SELECT c.first_name, o.order_id, o.total_price
FROM      customers c
LEFT JOIN orders    o  ON c.customer_id = o.customer_id
WHERE     o.total_price > 10.00;   -- filters AFTER join; loses unmatched customers

-- To filter on the joined table WITHOUT losing unmatched rows from the left,
-- move the condition into the ON clause:
SELECT c.first_name, o.order_id, o.total_price
FROM      customers c
LEFT JOIN orders    o  ON  c.customer_id = o.customer_id
                        AND o.total_price  > 10.00;
-- Customers with no qualifying orders still appear; their order columns are NULL

In a LEFT JOIN, filtering on a right-table column in WHERE converts it to an INNER JOIN. Once WHERE filters out rows where the right-table column is NULL, the "keep all left rows" guarantee is broken. Move right-table filters into the ON clause if you still need unmatched left rows in the result.

Chapter Summary

Concept	Key points
Table alias	Short nickname (a, b, c, o) assigned in FROM/JOIN. Required when the same column name exists in multiple tables. Use alias.column to qualify.
INNER JOIN	Returns only rows that match in both tables. Unmatched rows on either side are excluded. Most common join type.
LEFT JOIN	Returns all rows from the left table. Right-table columns are NULL for rows with no match. Use to include records regardless of whether a related record exists.
Anti-join pattern	LEFT JOIN + WHERE right.col IS NULL — finds left-table rows with no match on the right. "Customers who never ordered", "books never sold".
RIGHT JOIN	Mirror of LEFT JOIN. Rarely used — rewrite as LEFT JOIN by swapping table order.
Self-join	Join a table to itself using two aliases. Used for hierarchical/recursive data: manager → employee, parent category → child category.
COUNT in LEFT JOIN	Use COUNT(right_table.pk) not COUNT() — COUNT() counts NULL rows as 1; COUNT(col) skips them, giving the correct 0 for unmatched rows.
ON vs WHERE in LEFT JOIN	Filtering on a right-table column in WHERE silently converts LEFT JOIN to INNER JOIN. Move right-table filters into the ON clause to preserve unmatched left rows.

Next: Chapter 9 — String, Date, and Numeric Functions. MySQL has dozens of built-in functions for transforming data: CONCAT, SUBSTRING, REPLACE, UPPER/LOWER for strings; DATE_FORMAT, DATEDIFF, DATE_ADD for dates; ROUND, FLOOR, CEIL, MOD for numbers. Chapter 9 covers the ones you'll reach for every week.