Joins: combining tables
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.
Python
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)