Joining two tables

Data is split into tables so that nothing is repeated: books sit in one table and authors in another, and a book's row carries only the author's number (author_id). To see a book's title next to its author's name you join the two tables on that number.

1SELECT b.title, a.name
2FROM books AS b
3JOIN authors AS a ON a.id = b.author_id;

JOIN ... ON says which row pairs with which: those where the book's author_id equals the author's id. b and a are short names given to the tables; when both tables have a column of the same name, these say which one you mean.

A plain JOIN brings only the rows that have a partner: an author with no books does not appear in the result. To see them too you write LEFT JOIN: every row of the left table stays, and those without a partner get NULL on the right side. That is how "every author and their number of books, zero included" is solved.

Tasks