INNER JOIN

JOIN puts rows from two tables side by side, matching them up with the keys:

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

Reading it out loud: "Take books, and next to each book put the author whose id equals the book's author_id."

• ON says how rows match. It's almost always "foreign key = primary key".
• Write table.column when both tables have a column with the same name. Both tables here have id, so a bare id would confuse the database.
• JOIN is short for INNER JOIN. "Inner" means only rows with a match on both sides are kept.

Short names save typing. Give each table a nickname after its name:

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

Everything you've learned still works after the join: WHERE, ORDER BY, GROUP BY and the rest.

Forgetting ON is a classic mistake. Without it, the database pairs every book with every author: 20 × 10 = 200 rows of nonsense.