SQL from Zero › Module 5: Combining tables
INNER JOIN
Put two tables side by side, like each book next to its author's name.
Lesson 26 of 35 · about 15 minutes
The lesson
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.columnwhen both tables have a column with the same name. Both tables here haveid, so a bareidwould confuse the database. JOINis short forINNER 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.
Key ideas
- JOIN ... ON puts matching rows from two tables side by side.
- ON is usually foreign key = primary key, such as books.author_id = authors.id.
- Use table.column (or short nicknames) when both tables have a column with the same name.
Practise
Loading practice…