Session 5 — Joins I: INNER and LEFT

This is where the three bookshop tables come back together. A JOIN combines rows from two tables by matching a key — ON books.author_id = authors.author_id reconnects each book to its author. INNER JOIN keeps only matched rows; LEFT JOIN keeps every left-table row and fills the missing side with NULL — the tool for spotting orphans and for "all rows from the left, matched when possible". You will trace both joins pair by pair, use table aliases, qualify ambiguous columns, and assemble three-table joins with filtering.

Plan: Core ideas → worked joins → Quiz.

Time: about 70–80 minutes if you sketch the pairings by hand before reading the result tables.

Session 5 — Joins I: INNER and LEFT

The one-paragraph story

The bookshop splits one world into three tables, and joins put it back together. A JOIN combines rows from two tables by matching a key: books JOIN authors ON books.author_id = authors.author_id produces one combined row per book-and-author pair. INNER JOIN keeps only the matched rows; LEFT JOIN keeps every row of the left table even when nothing matches, filling the missing side with NULL. Choosing the join type is choosing what to do with non-matches — that is the entire skill. This session builds both joins by hand, sketches them as pictures, and wires the bookshop tables together for the first time.

---

Why joins exist

Querying one table answers one-table questions ("which books are mystery?"). The interesting questions live across tables: "which author wrote each book?", "what sold, and what was the line total?". The foreign keys from Session 1 are exactly the hooks joins use: books.author_id holds an authors.author_id value, and the join reconnects the two rows.

---

The INNER JOIN

SELECT b.title, a.name
FROM books b
JOIN authors a ON b.author_id = a.author_id;

Read it as a loop over pairs: for each book row, find the author row whose author_id equals the book's author_id, and combine them into one output row with the book's title and the author's name. The table aliases b and a let us say which author_id we mean (both tables have one).

How matching works, precisely. Consider all pairs (book, author). Keep a pair only when b.author_id = a.author_id. Each surviving pair becomes one result row. If a book had no matching author (a dangling author_id), it produces no row. If two authors somehow shared an id (impossible with a proper primary key), the book would pair with both — one row each. The ON condition is the matching rule.

USING shortcut. When the two columns share a name, USING (author_id) is shorthand for ON books.author_id = authors.author_id — it also merges the two matching columns into one in the result.

---

The LEFT JOIN

SELECT b.title, a.name
FROM books b
LEFT JOIN authors a ON b.author_id = a.author_id;

Same matching, different treatment of failures: every left-table row appears in the result. Matched rows combine as before; unmatched left rows appear once with the right table's columns filled with NULL.

That is why LEFT JOIN discovers missing links: a book whose author_id points at a deleted author shows up with name = NULL. The two-letter habit — LEFT JOIN when you want "all rows from the left side, matched when possible" — is one of the most common decisions in real queries.

---

Visualising the two joins

flowchart TD
    subgraph books-side
        B["books rows: 10..15"]
    end
    subgraph authors-side
        A["authors rows: 1..4"]
    end
    M["INNER: keep pairs (book, author)\nwhere author_id matches"]
    B --> M --> R1["one combined row per match"]
    M -->|no match| X1["book row disappears"]
    L["LEFT: keep pairs (book, author)\nwhere author_id matches"]
    B --> L --> R2["combined row per match"]
    L -->|no match| R3["book row kept,\nauthor columns = NULL"]

---

The pipeline with joins

A join slots into FROM, before any filtering:

SELECT b.title, a.name, s.qty
FROM books b
JOIN authors a  ON b.author_id = a.author_id
JOIN sales s    ON s.book_id = b.book_id
WHERE a.country = 'Germany'
ORDER BY s.qty DESC;

Trace order: FROM (three tables joined into one working table of combined rows) → WHERE (keep only rows where the author is German) → SELECT (pick columns) → ORDER BY. Joins add rows' worth of width; filters shrink rows' worth of height. The Session 2 skeleton is unchanged — it just got a wider source.

---

One author, many books — fan-out

Because one author has several books, joining authors to books multiplies author rows: Alina Voss appears once per book she wrote. This fan-out is normal and expected — it is exactly how "author with their book titles" is expressed. Be ready for the row count to grow: the join's row count is "all matching pairs", not "left-table rows".

---

Pitfalls to watch for

• INNER JOIN drops non-matches; LEFT JOIN keeps left rows and NULLs the right side. Ask "what happens to rows with no match?" before choosing.
• Forget the ON condition and you get a cross join (every pair) — usually a mistake, huge result sets.
• Column name collisions: both tables have author_id; without a qualifier (b.author_id vs a.author_id) the engine cannot tell which you mean. Qualify ambiguous columns.
• Fan-out is not a bug: one author + two books = two joined rows. Aggregating later (Session 4) counts each pair correctly.

Session 5 — Worked examples

The bookshop data:

authors: (1, Alina Voss, Germany), (2, Mateo Rios, Argentina), (3, Priya Anand, India), (4, Jonas Weber, Germany)

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

Example A — INNER JOIN books to authors

SELECT b.title, a.name
FROM books b
JOIN authors a ON b.author_id = a.author_id
ORDER BY b.book_id;

Pair each book with its author (matching author_id):

| title | name | |-------|------| | The Glass Harbour | Alina Voss | | Static Echoes | Alina Voss | | River Clock | Mateo Rios | | Salt Roads | Priya Anand | | North of Noon | Priya Anand | | Prairie Circuit | Jonas Weber |

Six rows — one per matching pair. Alina Voss appears twice (fan-out): she wrote two books.

---

Example B — LEFT JOIN shows the non-matches

Now suppose book 16 exists with author_id = 99, an author that does not exist in authors (a dangling reference):

SELECT b.title, a.name
FROM books b
LEFT JOIN authors a ON b.author_id = a.author_id
ORDER BY b.book_id;

| title | name | |-------|------| | The Glass Harbour | Alina Voss | | Static Echoes | Alina Voss | | River Clock | Mateo Rios | | Salt Roads | Priya Anand | | North of Noon | Priya Anand | | Prairie Circuit | Jonas Weber | | Book 16 (orphan) | NULL |

The orphan book survives with name = NULL. This is the signature use of LEFT JOIN: find what does not match — WHERE a.name IS NULL lists precisely the orphaned rows:

SELECT b.title
FROM books b
LEFT JOIN authors a ON b.author_id = a.author_id
WHERE a.name IS NULL;

→ Book 16 (orphan). Note: use IS NULL, never = NULL (Session 2's equality is not how you test for missing values).

---

Example C — Three-table join

SELECT b.title, a.name, s.qty
FROM books b
JOIN authors a ON b.author_id = a.author_id
JOIN sales s   ON s.book_id = b.book_id
ORDER BY s.sale_id;

Join books→authors (6 pairs), then books→sales (5 sales). Only sales rows survive the second join (no LEFT), so one row per sale:

| title | name | qty | |-------|------|-----| | The Glass Harbour | Alina Voss | 2 | | River Clock | Mateo Rios | 1 | | The Glass Harbour | Alina Voss | 3 | | North of Noon | Priya Anand | 1 | | Salt Roads | Priya Anand | 2 |

Note that "The Glass Harbour" appears twice (it was sold twice) and "Static Echoes" and "Prairie Circuit" not at all (never sold).

---

Example D — Join then filter

SELECT b.title, s.qty
FROM books b
JOIN sales s ON s.book_id = b.book_id
WHERE s.qty >= 2
ORDER BY s.qty DESC;

From the joined rows above, keep only qty ≥ 2: the 2-copy sale of The Glass Harbour, the 3-copy sale of The Glass Harbour, the 2-copy sale of Salt Roads. Result:

| title | qty | |-------|-----| | The Glass Harbour | 3 | | The Glass Harbour | 2 | | Salt Roads | 2 |

---

Example E — Qualifying ambiguous columns

Both books and authors have author_id. In a join, a bare author_id is ambiguous; b.author_id and a.author_id are not. Rule: qualify every column that exists in more than one joined table.

---

Example F — Joining on a shared name with USING

SELECT b.title, a.name
FROM books b
JOIN authors a USING (author_id);

Equivalent to ON b.author_id = a.author_id, with the matching column merged into one. Readable when the column names agree; use ON when they differ (e.g. sales.book_id vs books.book_id — those names agree, but a future ON s.book_id = b.book_id shows the pairing explicitly).