JOINs

Real data lives in multiple tables — authors here, books there — because storing the author's name on every book row means updating fifty rows when one person changes their name (and missing three of them — the classic data-corruption story; the discipline of splitting is called normalization). JOIN is how you put the pieces back together at query time, and it's the single most important operation in SQL.

The setup

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE books (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER              -- ← points at authors.id
);

books.author_id holding an authors.id value is the relationship — a foreign key (chapter 4 makes the database enforce it). The tables are linked by data, and JOIN follows the link.

INNER JOIN: the workhorse

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

Read the ON clause as the matching rule: pair each book row with the author row whose id matches. The result is a combined table with columns from both sides — filter it with WHERE, sort it, aggregate it like any other. Three notes of fluency:

• Qualify columns (books.title) once two tables are in play — an unqualified name that exists in both is an error (or worse, ambiguity).
• Aliases shorten the ceremony, universally used: FROM books b JOIN authors a ON b.author_id = a.id.
• Rows that find no partner vanish from INNER JOIN results. A book with author_id pointing nowhere, an author with no books — silently absent. That's the "inner" in inner join, and it leads directly to…

LEFT JOIN: keep the unmatched

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

Every row of the left table appears at least once; where no right-side match exists, the right columns come back NULL. "All authors, with their books if any" — the shape of every "including the empty ones" question. Corollary idiom worth recognizing: LEFT JOIN … WHERE b.id IS NULL finds left rows with no match at all (authors who never wrote a thing).

Counting per parent combines this lesson with the last one:

SELECT a.name, COUNT(b.id) AS book_count
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
GROUP BY a.name;

COUNT(b.id) — not COUNT(*) — makes bookless authors show 0 (their only b.id is NULL, and COUNT(column) skips NULLs; COUNT(*) would count the row itself and report 1). That subtlety is a favorite interview question, now spoiled for you.

Your exercise: Books by Author

Create both tables, insert the rows, join, and output the requested columns. The mechanics: match the join keys correctly (ON b.author_id = a.id — joining on the wrong pair produces a plausible-looking cartesian mess), qualify or alias every column, and ORDER BY the output as specified. Once this passes, you've cleared SQL's conceptual summit: everything after joins is refinement, and every report, dashboard, and API endpoint you'll ever build is SELECT–JOIN–WHERE–GROUP–ORDER wearing different clothes.