Programonic

Before you start

SQL SELECT and primary keys.

Combine rows from two tables that share a key.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

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.

Code example

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

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

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

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

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