Joins: combining tables

Match rows across tables with INNER and LEFT joins and keep rows that have no match.

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)

Output

[('Ada', 'Notes'), ('Grace', 'Compilers'), ('Grace', 'Languages')]
[('Ada', 1), ('Grace', 2), ('Linus', 0)]

Explanation

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.

Explore the dry run

Guided walkthroughReady

1import sqlite3

3db = sqlite3.connect(":memory:")

4db.executescript(

5 """

6 CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT);

7 CREATE TABLE book (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER);

8 INSERT INTO author VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');

9 INSERT INTO book VALUES (1, 'Notes', 1), (2, 'Compilers', 2), (3, 'Languages', 2);

10 """

11)

12inner = db.execute(

13 "SELECT author.name, book.title FROM author "

14 "JOIN book ON book.author_id = author.id ORDER BY author.name, book.title"

15).fetchall()

16left = db.execute(

17 "SELECT author.name, COUNT(book.id) FROM author "

18 "LEFT JOIN book ON book.author_id = author.id "

19 "GROUP BY author.id ORDER BY author.id"

20).fetchall()

21print(inner)

22print(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

Prepared trace of the published program. Some rows group repeated iterations.
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)]

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.

Joins: combining tables

Explanation

Follow the example

Cost and common mistakes

Try it yourself

Further reading