--- title: 'Inner Join' source: 'https://academia.sh/en/courses/sql-fundamentals/inner-join' course: 'SQL Fundamentals' language: en updated: '2026-08-23T07:00:52+00:00' license: 'CC BY-SA 4.0' --- # Inner Join Gathering information scattered across a normalized schema into a single result, the join condition, table aliases, the way unmatched rows drop out, and how row count can multiply. 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. ```sh 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','alice@example.test','2023-02-14'), (2,'Ben','Ortiz','ben@example.test','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09'), (4,'Derek','Voss','derek@example.test','2024-03-22'),(5,'Grace','Kim',NULL,'2024-11-05'), (6,'Owen','Park','owen@example.test','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 ``` ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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. ```sh 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`. ```sh 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.