Joins: combining tables
Match rows across tables with INNER and LEFT joins and keep rows that have no match.
On this page
Foreign keys and SELECT.
- Match rows across tables with INNER and LEFT joins and keep rows that have no match.
- Predict the sample output and explain the state changes.
- Complete the practice task and check the boundary cases.
The concept
A relational design splits data across tables to avoid repeating it, and joins put it back together. A join pairs rows from two tables that satisfy a condition, usually that a foreign key equals a primary key.
An INNER JOIN returns only the pairs that match. A LEFT JOIN returns every row from the left table and fills the right side with NULL when nothing matches. In the example, Linus has written no books, so the inner join omits him and the left join keeps him with a count of zero.
Counting with a left join needs care. COUNT(book.id) counts only non-NULL values, so an author without books correctly gets 0, while COUNT(*) would count the single NULL-extended row and give 1. Other join types include RIGHT, FULL OUTER (both supported in modern SQLite), and CROSS JOIN, which returns every combination.
Code example
import sqlite3
db = sqlite3.connect(":memory:")
db.executescript(
"""
CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER);
INSERT INTO author VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO book VALUES (1, 'Notes', 1), (2, 'Compilers', 2), (3, 'Languages', 2);
"""
)
inner = db.execute(
"SELECT author.name, book.title FROM author "
"JOIN book ON book.author_id = author.id ORDER BY author.name, book.title"
).fetchall()
left = db.execute(
"SELECT author.name, COUNT(book.id) FROM author "
"LEFT JOIN book ON book.author_id = author.id "
"GROUP BY author.id ORDER BY author.id"
).fetchall()
print(inner)
print(left)[('Ada', 'Notes'), ('Grace', 'Compilers'), ('Grace', 'Languages')]
[('Ada', 1), ('Grace', 2), ('Linus', 0)]Follow the code
Open the walkthrough to inspect the example one step at a time.
Show guided walkthrough
import sqlite3 db = sqlite3.connect(":memory:")db.executescript( """ CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER); INSERT INTO author VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus'); INSERT INTO book VALUES (1, 'Notes', 1), (2, 'Compilers', 2), (3, 'Languages', 2); """)inner = db.execute( "SELECT author.name, book.title FROM author " "JOIN book ON book.author_id = author.id ORDER BY author.name, book.title").fetchall()left = db.execute( "SELECT author.name, COUNT(book.id) FROM author " "LEFT JOIN book ON book.author_id = author.id " "GROUP BY author.id ORDER BY author.id").fetchall()print(inner)print(left)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
| Step / line | What happens | State after step | Output so far |
|---|---|---|---|
| The inner join keeps only author-book pairs where book.author_id equals author.id. Linus has no match, so he disappears. | 3 matching pairs | — | |
| The left join starts from every author and attaches matching books; Linus keeps a row with NULL book columns. | Linus: book.id = NULL | — | |
| Output of the inner join, sorted by author then title. | Linus: book.id = NULL | [('Ada', 'Notes'), ('Grace', 'Compilers'), ('Grace', 'Languages')] | |
| COUNT(book.id) ignores NULL, so Linus shows 0 books. | Linus: book.id = NULL | [('Ada', 'Notes'), ('Grace', 'Compilers'), ('Grace', 'Languages')]
[('Ada', 1), ('Grace', 2), ('Linus', 0)] |
Behind the scenes
How Python processes this code
Python runs this source through its interpreter. It creates objects for values and resolves names as each statement runs.
Cost and common mistakes
A nested-loop join without an index costs O(n·m). With an index on the join column it is about O(n log m); a hash join achieves roughly O(n + m).
- COUNT(*) after a LEFT JOIN counts the NULL-extended row, so unmatched rows show 1 instead of 0. Count a column from the right table.
- A join condition placed in WHERE instead of ON changes the meaning of a LEFT JOIN, because the WHERE filter removes the NULL-extended rows.
- A missing or wrong join condition produces a Cartesian product with n × m rows.
Try it yourself
Which query lists authors who have written no books?
Reveal answer and reasoning
SELECT author.name FROM author LEFT JOIN book ON book.author_id = author.id WHERE book.id IS NULL. It returns Linus.