Querying with joins

Build the library left you with four related tables. So far each show data looks at one table at a time — but the interesting questions span tables: who wrote this book? who has borrowed it? Answering those means a join: matching rows from one table against related rows in another.

Switch to advanced mode, then join books to their authors.

Joins are part of SQL, so this guide is in advanced mode. Switch to it with the mode command:

mode advanced

If you only need one SQL command without leaving simple mode, prefix it with a colon instead — see Simple and advanced modes.

Join two tables

books stores an author_id, not the author’s name. To show the name, join books to authors, matching each book’s author_id to the author it points at:

select authors.name, books.title from books
  join authors on books.author_id = authors.author_id
  order by authors.name

┌───────────────────┬───────────────────────────┐
│ name              │ title                     │
├───────────────────┼───────────────────────────┤
│ Italo Calvino     │ Invisible Cities          │
│ Octavia E. Butler │ Kindred                   │
│ Ursula K. Le Guin │ A Wizard of Earthsea      │
│ Ursula K. Le Guin │ The Left Hand of Darkness │
└───────────────────┴───────────────────────────┘

The on books.author_id = authors.author_id clause is the heart of the join: it says which rows belong together. Because Le Guin wrote two books, her name appears on two rows — exactly the one-to-many relationship you declared, expanded into result rows.

Join through the bridge table

The real payoff is the many-to-many link. Who has borrowed which book? lives in three tables: the borrower in members, the book in books, and the connection in loans. Join all three, hopping through the loans bridge:

select members.name, books.title, loans.loaned_on, loans.returned_on from loans
  join books on loans.book_id = books.book_id
  join members on loans.member_id = members.member_id
  order by loans.loaned_on

┌──────────────┬──────────────────────┬────────────┬─────────────┐
│ name         │ title                │ loaned_on  │ returned_on │
├──────────────┼──────────────────────┼────────────┼─────────────┤
│ Grace Hopper │ A Wizard of Earthsea │ 2024-05-01 │ (null)      │
│ Alan Turing  │ Invisible Cities     │ 2024-05-03 │ 2024-05-20  │
└──────────────┴──────────────────────┴────────────┴─────────────┘

Each loan row pulls in the matching book and the matching member, turning three tables of IDs into a readable sentence: who borrowed what, and when.

Filter a join

A where clause narrows a join just like it narrows a single table. A loan that has not been returned has an empty returned_on, so which books are currently out? is:

select members.name, books.title from loans
  join books on loans.book_id = books.book_id
  join members on loans.member_id = members.member_id
  where loans.returned_on is null

┌──────────────┬──────────────────────┐
│ name         │ title                │
├──────────────┼──────────────────────┤
│ Grace Hopper │ A Wizard of Earthsea │
└──────────────┴──────────────────────┘

Summarise with group by

Joins combine with group by to count and total. How many books has each author written? groups the joined rows by author and counts each group:

select authors.name, count(*) as book_count from books
  join authors on books.author_id = authors.author_id
  group by authors.name
  order by book_count desc

┌───────────────────┬────────────┐
│ name              │ book_count │
├───────────────────┼────────────┤
│ Ursula K. Le Guin │ 2          │
│ Octavia E. Butler │ 1          │
│ Italo Calvino     │ 1          │
└───────────────────┴────────────┘

count(*) counts the rows in each group; as book_count names the result column. Le Guin’s two books collapse into a single row with a count of 2.

Where to go next

• See how a query runs — prefix any of these with explain to view the plan, and add an index to speed up a join.
• The full select surface — projections, more join forms, set operations, and CTEs — is in Querying & inspecting.
• Switch back to simple mode any time by running mode simple.