Books and authors
- SQL course
- Intermediate
- Lesson 20/115
- 5 min
Write the INNER JOIN query for books and authors.
Book and author
To list books with their authors you use INNER JOIN. author_id connects the two tables.
SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;This query matches book and author names.
b.title, a.name is selected from books b. authors a is joined on b.author_id = a.id, keeping only matching rows.
Book and author — details
Each book should have an author. Unmatched books drop out.
books JOIN authors ON books.author_id = authors.id puts the author's name next to each book; because it is an INNER JOIN, a book with no registered author is left out.
Practice Book and author
INNER JOIN books with authors; select title and name.
Starter code
-- book and author
SELECT b.title, a.name
FROM books bSample solution
SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;Expected output
title name Suç ve Ceza Dostoyevski Sefiller Hugo
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add INNER JOIN
SELECT b.title, a.name
FROM books b
____ authors a ON b.author_id = a.id;SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;Explanation: INNER JOIN is the right answer.
2 Which JOIN for book-author match?
- CROSS JOIN
- UNION
- INNER JOIN Correct answer
- LEFT JOIN
Explanation: INNER JOIN fits matching pairs.
3 Books and authors: match the terms
| Term | Meaning |
|---|---|
books |
book |
authors |
author |
author_id |
link |
INNER JOIN |
intersection |