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

Video: Matching rows across tables

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.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.

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…

Quick check

1. What does ON tell the database?

2. Why write books.id instead of id after joining books and authors?

3. A book's author_id is 99, and there's no author 99. Does an INNER JOIN show that book?