SQL Joins

Combine rows from two tables that share a key.

SQL

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER);
INSERT INTO authors VALUES (1, 'Ada'), (2, 'Grace');
INSERT INTO books VALUES (1, 'Notes', 1), (2, 'Compilers', 2), (3, 'Drafts', 1);

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

Output

Ada|Drafts
Ada|Notes
Grace|Compilers

Explanation

Relational data is split across tables to avoid repeating information. A join brings the pieces back together: JOIN ... ON matches each row of one table to the rows of another that satisfy a condition, usually a foreign key equal to a primary key.

An inner JOIN keeps only rows that have a match on both sides. Here each book matches the author whose id equals author_id. Use LEFT JOIN when you also want rows from the left table that have no match. Qualifying column names with the table name, as in authors.name, avoids ambiguity.

Explore the dry run

Guided walkthroughReady

1CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

2CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER);

3INSERT INTO authors VALUES (1, 'Ada'), (2, 'Grace');

4INSERT INTO books VALUES (1, 'Notes', 1), (2, 'Compilers', 2), (3, 'Drafts', 1);

6SELECT authors.name, books.title

7FROM books

8JOIN authors ON authors.id = books.author_id

9ORDER BY authors.name, books.title;

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
Two authors with ids 1 and 2.	`—`	—
Three books; each stores the id of its author.	`—`	—
Pair each book with the author whose id matches author_id.	`—`	—
Sort by author, then by title.	`—`	Ada\|Drafts Ada\|Notes Grace\|Compilers

Cost and common mistakes

With an index on the join column the engine can look up matches quickly; without one it may compare many row pairs, up to O(n*m).

Leaving out the ON condition (or writing it wrongly) can pair every row with every row.
An inner join drops rows with no match; an author with no books would not appear.

Try it yourself

List every author even if they have no books.

Reveal answer and reasoning

Start from authors and use LEFT JOIN books ON books.author_id = authors.id. Authors without books get NULL in the title column.

SQL Joins

Explanation

Follow the example

Cost and common mistakes

Try it yourself

Further reading