Relationships and Joins

The last lesson pulled rows out of one table and put them in order: a WHERE clause to keep the books published after 1960, an ORDER BY to sort them by year. Every answer came from a single table. Real questions rarely stay that tidy. Ask for a list of books next to the country each author comes from, and one table cannot answer, because the country was never stored beside the book. It lives with the author, in a different place. Joining is how SQL answers a question whose pieces are spread across tables, and it is the point where the language starts to earn its keep.

Why one big table goes wrong

Picture a single books table that tries to hold everything: the title, the year, and the author's full details on every row.

Look at rows 1 and 2. Le Guin's name and country are written out twice, and if she had ten books, they would be written out ten times. That repetition is the problem, and it bites in two ways.

The first bite is updates. Say her country was entered wrong and needs fixing. In this design the fix has to touch every single row that mentions her. Ten books means ten edits, and the moment one edit gets missed, the table now claims Le Guin is from two different countries at once. A database that disagrees with itself is worse than no database, because queries trust it.

The second bite is that there is nowhere to record an author who has not published yet. Author details only exist as a side effect of a book row. No book, no author. The data model has quietly decided that authors cannot exist on their own, which is not how the world works.

The fix is to stop repeating and start pointing. Pull the author details into their own table, list each author exactly once, and have each book point at the right author instead of copying the details in. That splits one wide table into two narrow ones that relate to each other. Database people call this design normalization, and the plain version of the idea is simple: each fact gets one home, and everything else refers to that home rather than restating it.

Two tables and a key that connects them

Here is the same information split into an authors table and a slimmer books table. Each author appears once. Each book keeps only a reference to its author.

CREATE TABLE authors (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  country TEXT
);

CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  title     TEXT,
  author_id INTEGER,
  year      INTEGER
);

The authors table now holds three people, each on one row:

And books drops the copied author details, keeping a single author_id column that names which author wrote it:

Two words name what is happening here. A primary key is the column that gives every row in a table its own unique label, the way id does in both tables. No two authors share an id, and no two books do. It is how a single row gets picked out from all the rest.

A foreign key is a column in one table that holds the primary key of a row in another table. Here books.author_id is the foreign key: the value 1 in a book row is not the number one in any meaningful sense, it is a pointer that says "the author whose id is 1." Book 1 and book 2 both carry author_id = 1, so both point at the single Le Guin row. Fix her country once, in that one row, and every book that points at her sees the correction instantly, because the country was never copied into the books to begin with.

That is the whole payoff of splitting the data. One fact lives in one place. The pointer is cheap to store and free to update, and an author can now sit in the authors table with no books at all, waiting for a first title.

The join: matching rows on the key

Splitting the data was the setup. Now the question "list each book beside its author's name" needs both tables read together, matched up on the key. That match is what a JOIN does.

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

Read it in pieces. FROM books starts with the books table. JOIN authors says to bring in the authors table as well. The ON books.author_id = authors.id is the heart of it: for each book, find the author whose id equals that book's author_id, and stitch the two rows together into one wider row. The SELECT then picks which columns from that combined row to show, and because both tables have their own columns, each name is written table-first, as books.title and authors.name, so there is no confusion about where a column comes from.

A useful way to picture the work: the database walks the books table one row at a time, and for each book it scans the authors table for the row whose id matches that book's author_id. When it finds one, it lays the book's columns and the author's columns side by side and emits that as a single result row. The stored tables are never altered by any of this. The joined shape exists only for the length of the query, assembled fresh each time it runs.

The result pairs every book with the one author it points at:

Le Guin appears on two rows because two books point at her, and that is correct: the join produces one output row per matching pair, not one per author. This exact form is called an INNER JOIN, and INNER is the important word. It keeps only rows where the match succeeds. A book with an author_id that matches no author would simply drop out of the result, and an author with no books would never appear, because nothing on the books side points back at them.

Running it against a real SQLite database prints the same four rows. With column mode and headers turned on, the output looks like this:

$sqlite3 library.db

Shorter names with aliases, and one filter on top

Once queries touch two or three tables, writing books.title and authors.country everywhere gets long. SQL lets each table be given a short alias right after its name in the FROM and JOIN, and the rest of the query uses the alias instead.

SELECT b.title, a.name, a.country
FROM books b
JOIN authors a ON b.author_id = a.id
WHERE a.country = 'USA';

books b names the books table b for the length of this query, and authors a names the authors table a. From that point on, b.title means the same thing books.title did, just with less typing. The aliases are not new tables or copies of data. They are nicknames that vanish when the query finishes.

This query also folds in the filtering from the previous lesson. The join stitches books to authors, and then the WHERE clause keeps only the pairs where the author's country is the United States. Joining and filtering compose cleanly: match first, then narrow. The result is two rows, both Le Guin's, since she is the only author from the USA in this data:

When the unmatched rows matter

The INNER JOIN drops rows that fail to match, which is usually what a question wants. Sometimes it is exactly wrong. To list every author including the ones who have not published a book yet, an inner join would silently hide them, because nothing on the books side points back. A LEFT JOIN keeps every row from the left table, the one named first, even when no match exists on the right, and fills the missing right-side columns with NULL, the database's marker for "no value here."

Written as FROM authors a LEFT JOIN books b ON b.author_id = a.id, that query returns all three authors, with an author who wrote nothing showing a NULL where a title would go. The choice between the two joins comes down to a single question: should a row survive when it has no partner? INNER says no, LEFT says yes. Reach for the inner join by default, and switch to LEFT only when the unmatched rows are part of the answer.

A join reads across tables without changing a thing in them. The next lesson, changing data, turns to the other half of SQL: INSERT to add rows, UPDATE to edit them, and DELETE to remove them. It also covers the WHERE clause's most dangerous absence, because an UPDATE or DELETE with no WHERE does not touch one row, it touches every row in the table at once.