Inner Join

The previous topic built queries that read from a single table: column selection, conditions, sorting, null-value behavior, functions. One question remained unanswered by those tools. Looking at the loan table alone does not show which member borrowed which book.

The block below builds the schema and the sample data; every query in this lesson runs against the library.db file it creates.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER, branch_id INTEGER REFERENCES branch(branch_id));
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO branch VALUES (1,'Central','Ankara'),(2,'Bahcelievler','Ankara'),
  (3,'Kadikoy','Istanbul'),(4,'Konak','Izmir');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995,1),(2,'The Disconnected','Oğuz Atay',1972,1),
  (3,'The Book of Sand','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Silent House','Orhan Pamuk',1983,3),(6,'Motherland Hotel','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'),
  (4,'Derek','Voss','[email protected]','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'),
  (6,'Owen','Park','[email protected]','2025-01-18');
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT loan_id, book_id, member_id, pickup_date FROM loan WHERE loan_id <= 4 ORDER BY loan_id;
SQL

loan_id  book_id  member_id  pickup_date
-------  -------  ---------  -----------
1        1        1          2025-01-10 
2        2        1          2025-02-02 
3        1        2          2025-02-11 
4        3        3          2025-03-01 

All that is visible is identifiers. This is not a shortcoming; it is the direct consequence of normalization: a member’s name is written once, in the member table, and the loan record holds only a foreign key pointing to it. The information is deliberately scattered; the query’s job is to gather it back together, temporarily. Gathering it back together is called a join.

Join Condition

A join matches the rows of two tables against a condition. Its syntax is built from the JOIN and ON clauses.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT l.loan_id, m.first_name, m.last_name, l.pickup_date
FROM loan AS l
JOIN member AS m ON l.member_id = m.member_id
WHERE l.loan_id <= 4
ORDER BY l.loan_id;
SQL

loan_id  first_name  last_name  pickup_date
-------  ----------  ---------  -----------
1        Alice       Kane       2025-01-10 
2        Alice       Kane       2025-02-02 
3        Ben         Ortiz      2025-02-11 
4        Clara       Diaz       2025-03-01 

Identifiers turned into names. The condition in the ON clause matches the foreign key on the loan record against the primary key in the member table. This is the most common form of join: the one written with an equality condition, carrying the name inner join. When the word JOIN is written alone, an inner join is understood; the spelling INNER JOIN means the same thing.

The spellings AS l and AS m give the tables a table alias. When both tables carry a column with the same name — here, member_id appears in both — the alias determines which table’s column is meant. Single-letter aliases are common, but readability drops as the number of tables grows; meaningful abbreviations serve better.

Multiple Tables

A join can be chained. Because the loan record refers to both the member and the book, two joins together answer the whole question.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT l.loan_id, m.first_name || ' ' || m.last_name AS member, b.title, l.pickup_date
FROM loan AS l
JOIN member AS m ON l.member_id = m.member_id
JOIN book AS b  ON l.book_id = b.book_id
ORDER BY l.loan_id;
SQL

loan_id  member      title              pickup_date
-------  ----------  -----------------  -----------
1        Alice Kane  Blindness          2025-01-10 
2        Alice Kane  The Disconnected   2025-02-02 
3        Ben Ortiz   Blindness          2025-02-11 
4        Clara Diaz  The Book of Sand   2025-03-01 
5        Clara Diaz  Yaban              2025-03-18 
6        Derek Voss  Blindness          2025-04-05 
7        Derek Voss  Silent House       2025-04-21 
8        Grace Kim   The Disconnected   2025-05-02 
9        Alice Kane  Tehlikeli Oyunlar  2025-05-14 
10       Grace Kim   The Book of Sand   2025-06-03 
11       Ben Ortiz   Motherland Hotel   2025-06-11 
12       Derek Voss  Yaban              2025-06-20 

All twelve records in the loan table came back. Each join result is a new relation and becomes the input to the next join; the order it is written in is only the order on the page — the order the engine actually applies can differ. The rule of a declarative language holds here as well.

Unmatched Rows Drop Out

The defining property of the inner join is that rows failing the condition never appear in the result at all. The query joining books with branches shows this.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT b.title, br.name AS branch
FROM book AS b
JOIN branch AS br ON b.branch_id = br.branch_id
ORDER BY b.book_id;
SQL

title             branch      
----------------  ------------
Blindness         Central     
The Disconnected  Central     
The Book of Sand  Bahcelievler
Yaban             Bahcelievler
Silent House      Kadikoy     
Motherland Hotel  Kadikoy     

Six rows came back, though the book table holds seven books. The missing one is Tehlikeli Oyunlar: because its branch id is null, the condition b.branch_id = br.branch_id is never true for any branch row. The rule from the fifth lesson applies here as well — equality against a null value produces neither true nor false, and a join only lets true through.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS book FROM book;
SELECT COUNT(*) AS join_result FROM book AS b JOIN branch AS br ON b.branch_id = br.branch_id;
SQL

book
----
7   
join_result
-----------
6          

The silence of the loss matters: the query raises no error, it returns one row short. When a report’s numbers do not add up, one of the first places to look is the rows an inner join has eliminated. When unmatched rows need to be kept as well, an outer join is written; that is the next lesson’s subject.

Row Count Can Grow, Too

A join does not only drop rows; it can multiply them. When one row in one table matches several rows in the other table, it appears in the result as many times as it has matches.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT b.title, l.loan_id, l.pickup_date
FROM book AS b
JOIN loan AS l ON b.book_id = l.book_id
WHERE b.title = 'Blindness'
ORDER BY l.loan_id;
SQL

title      loan_id  pickup_date
---------  -------  -----------
Blindness  1        2025-01-10 
Blindness  3        2025-02-11 
Blindness  6        2025-04-05 

The single row for this book in the book table appeared three times once joined with the loan records. This is correct — there really are three separate loan transactions — but it turns into a trap during counting: counting books through this result counts Blindness three times over. This distinction returns and is examined more closely when aggregate functions are covered.

General rule: in a one-to-many relationship, a join repeats the rows on the “one” side as many times as there are matches on the “many” side.

Shorthand Syntax

Two shorter spellings of a join exist, and both call for caution.

The first is the USING clause, which bases the join condition on a column carrying the same name in both tables.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT title, name AS branch FROM book JOIN branch USING (branch_id) ORDER BY book_id;
SQL

title             branch      
----------------  ------------
Blindness         Central     
The Disconnected  Central     
The Book of Sand  Bahcelievler
Yaban             Bahcelievler
Silent House      Kadikoy     
Motherland Hotel  Kadikoy     

USING (branch_id) gives the same result as ON b.branch_id = br.branch_id and shows the shared column once in the result. It is useful when naming is consistent; it cannot be used when column names differ.

The second is the join that writes no condition at all: the natural join. Every column carrying the same name in both tables is taken as the join condition. Its brevity comes at the cost of silent error.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT 'member NATURAL JOIN branch row count: ' || COUNT(*) AS measurement FROM member NATURAL JOIN branch;
SQL

measurement                             
----------------------------------------
member NATURAL JOIN branch row count: 24

The member table and the branch table carry no real relationship, and in this schema they also share no column name at all. With nothing to match on, the natural join falls back to a plain cross join: every member row is paired with every branch row, six times four, twenty-four in total. No error message appears; only a suspiciously large result.

The real danger runs the other way: a column added to a schema later can happen to share a name with a column in an unrelated table. A natural join that worked correctly one day can silently start computing something else the next, matching rows on a name that carries no real relationship at all. Writing the join condition explicitly removes this entire class of error before it can occur.

One more old spelling exists: separating tables with a comma and placing the condition inside WHERE.

sqlite3 library.db <<'SQL'
.headers on
.mode column
SELECT b.title, br.name AS branch FROM book AS b, branch AS br
WHERE b.branch_id = br.branch_id ORDER BY b.book_id;
SQL

title             branch      
----------------  ------------
Blindness         Central     
The Disconnected  Central     
The Book of Sand  Bahcelievler
Yaban             Bahcelievler
Silent House      Kadikoy     
Motherland Hotel  Kadikoy     

The result is the same, but it carries two flaws. The join condition and the filter condition mix together in the same clause, and when the condition is forgotten the query produces every matching pair without ever raising an error. The ON syntax keeps the condition next to the join and prevents both.

Summary

• In a normalized schema, information is deliberately scattered; a join gathers it back together for the duration of a query.
• An inner join returns only the row pairs for which the condition is true; an unmatched row drops out without error.
• A foreign key carrying a null value matches no row and is lost in an inner join.
• In a one-to-many relationship, a join repeats the row on the “one” side as many times as it has matches.
• Table aliases distinguish identically named columns and keep multi-table queries readable.
• USING shortens a join on an identically named column; a natural join leaves the condition to name similarity and is open to silent error.

Next Step

An inner join dropping unmatched rows is, for some questions, exactly what is not wanted: questions such as “which branch has no books at all” or “which member has never borrowed anything” are asking precisely for the rows that have no match. The next lesson builds the outer joins that keep those rows, shows how the preserved rows fill with null values, and measures how the right and full outer joins depend on engine support.