Bring two tables together
Combine rows of two tables that belong together with JOIN and ON. About 9 minutes.
Each fact lives in one place
The library keeps books in one table and authors in another. A book row holds an author_id, a number that points at a row of authors. The author's name is written once, in authors, not copied onto every book.
That keeps the data tidy, but a question like "each title with its author's name" needs both tables. JOIN brings them together.
JOIN … ON pairs up matching rows
After FROM name the first table, then JOIN the second and say how rows match with ON. Each row of the first table is paired with the rows of the second where the test is true.
Give each table a short alias, like b and a, and put it before the column (b.title). It is needed when both tables have a column of the same name, such as id. A row with no partner on the other side isn't in the answer.
SELECT b.title, a.name FROM books b JOIN authors a ON a.id = b.author_id;
| id | title | author_id |
|---|---|---|
| 1 | Godaan | 10 |
| 2 | Gitanjali | 11 |
| 3 | The Guide | 12 |
| id | name |
|---|---|
| 10 | Premchand |
| 11 | Tagore |
| 12 | R. K. Narayan |
Take the first book. Its author_id is 10.
What does ON a.id = b.author_id do?
- AIt says which row of authors belongs to each row of books
- BIt keeps only the books written by author 1
- CIt sorts the books by author
Show the answer
It says which row of authors belongs to each row of books. ON is the matching rule that decides which rows get paired.
Who teaches what
The courses table has one row per course and the id of its teacher, and teachers has one row per teacher. Show each course's title and its teacher's name as teacher. Order by title.
teachers(id, name) courses(id, title, teacher_id)
Up to 1,000 courses · course titles are unique