Skip to content
aviral gupta
Get in touch

// B3.2 · ~30 min · Beginner

Inner joins

After this lesson you can combine the rows of two or three tables with JOIN, name every column without ambiguity, and tell a correct join from one that pairs every row with every other.

Lesson 2 of 6 in B3 Joins and aggregates

You will be able to

  • Join two tables with JOIN ... ON and predict which rows come back
  • Name columns in a join with table aliases and qualified names, or USING when the column names match
  • Chain three tables and recognise a missing join condition by its row count

  1. Warm-up · Activity 1 of 7

    Warm-up: loan 3 has book_id 2 and member_id 2. Using the tables below, which book did which member borrow?

    -- books: 1 Dune (sci-fi), 2 Emma (classic), 3 Kindred (sci-fi),
--        4 Beloved (fiction), 5 The Hobbit (fantasy), 6 Neuromancer (sci-fi)
-- members: 1 Ada (Berlin), 2 Grace (Munich), 3 Linus (Berlin), 4 Margaret (no city)
-- loans (id, book_id, member_id): (1, 1, 1), (2, 3, 1), (3, 2, 2), (4, 1, 3), (5, 6, 2)

  2. Predict · Activity 2 of 7

    Predict before you read on. members has 4 rows and loans has 5. Ada has two loans, Grace two, Linus one and Margaret none. How many rows does this return?

    SELECT m.name, l.id
FROM members m
JOIN loans l ON l.member_id = m.id;

  3. Practice · Activity 3 of 7

    Fill in the gap so that the query runs: the ON condition and the select list call the books table b.

    SELECT b.title, l.loaned_on
FROM loans l
JOIN books ____ ON b.id = l.book_id
ORDER BY l.id;
    SELECT b.title, l.loaned_on FROM loans l JOIN books ON b.id = l.book_id ORDER BY l.id;

  4. Practice · Activity 4 of 7

    Match each piece of a join to what it does.

  5. Practice · Activity 5 of 7

    Put the lines in order to list the books that are out on loan now, with the loan date, oldest loan first.

    1. 1.FROM loans AS l
    2. 2.JOIN books AS b
    3. 3.ORDER BY l.loaned_on
    4. 4.WHERE l.returned_on IS NULL
    5. 5.ON b.id = l.book_id
    6. 6.SELECT b.title, l.loaned_on

  6. Brain teaser · Activity 6 of 7

    Brain teaser. A table can be joined with itself, under two aliases. Ada and Linus live in Berlin, Grace in Munich, and Margaret’s city is NULL. How many rows does this return?

    SELECT m1.name, m2.name
FROM members m1
JOIN members m2 ON m1.city = m2.city;

  7. Apply · Activity 7 of 7

    Mini-task. In the editor of the worked example, list every loan of a sci-fi book with the member’s name, the title and the loan date, oldest loan first. Then write the same query in the older form, with the tables separated by commas in FROM and the join conditions in WHERE, and check that both return the same four rows.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Loans with titles, names and shelves

setup.sql loads the library from module B1 (books, members, loans) and a small shelves table: the floor each genre stands on. main.sql joins loans to books, then to members as well, joins books to shelves with USING, and finally counts the pairs of a join without a condition. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Each loan with the title of its book.
SELECT l.id, b.title, l.loaned_on
FROM loans AS l
JOIN books AS b ON b.id = l.book_id
ORDER BY l.id;

-- 2. Open loans: who has which book (three tables).
SELECT l.id, m.name, b.title
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books b ON b.id = l.book_id
WHERE l.returned_on IS NULL
ORDER BY l.id;

-- 3. The floor of each book's shelf, joined on genre.
SELECT genre, b.title, s.floor
FROM books b
JOIN shelves s USING (genre)
ORDER BY s.floor, b.title;

-- 4. No join condition: every loan with every book.
SELECT count(*) AS pairs
FROM loans
CROSS JOIN books;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);
INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);
INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);
INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

CREATE TABLE shelves (
  genre text,
  floor integer
);
INSERT INTO shelves VALUES
  ('classic', 1),
  ('fantasy', 2),
  ('sci-fi', 2),
  ('poetry', 3);

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 id |    title    | loaned_on
----+-------------+------------
  1 | Dune        | 2026-09-01
  2 | Kindred     | 2026-09-12
  3 | Emma        | 2026-09-05
  4 | Dune        | 2026-09-15
  5 | Neuromancer | 2026-09-18
(5 rows)

 id | name  |    title
----+-------+-------------
  2 | Ada   | Kindred
  4 | Linus | Dune
  5 | Grace | Neuromancer
(3 rows)

  genre  |    title    | floor
---------+-------------+-------
 classic | Emma        |     1
 sci-fi  | Dune        |     2
 sci-fi  | Kindred     |     2
 sci-fi  | Neuromancer |     2
 fantasy | The Hobbit  |     2
(5 rows)

 pairs
-------
    30
(1 row)
  • Five loans give five rows, and Dune appears twice because it was loaned twice. Beloved and The Hobbit match no loan, so the inner join leaves them out.
  • The second JOIN adds members to rows that already hold a loan and its book; WHERE then keeps the three open loans.
  • USING (genre) joins on equal genres and shows genre once, unqualified. Beloved is missing: no shelf holds fiction. The poetry shelf is missing too: no book is poetry.
  • Without a condition, every one of the 5 loans is paired with every one of the 6 books: 30 rows.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

Loans of the Berlin members

List the loans of members who live in Berlin: the member’s name, the book’s title and the loan date, oldest loan first. The starter shows only the numbers stored in loans. Join members and books, and filter on the member’s city.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Start from loans and add one JOIN per table you need: members for the name and city, books for the title.

  2. Hint 2

    Each JOIN needs its own ON: m.id = l.member_id and b.id = l.book_id.

  3. Hint 3

    The city belongs to the member, so the filter is WHERE m.city = 'Berlin'.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

SELECT m.name, b.title, l.loaned_on
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books b ON b.id = l.book_id
WHERE m.city = 'Berlin'
ORDER BY l.loaned_on;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members, loans and shelves.
SELECT l.member_id, l.book_id, l.loaned_on
FROM loans l
ORDER BY l.loaned_on;

test.sql

-- test: The query lists the three loans of Ada and Linus with titles, oldest first
-- output:
--  name  |  title  | loaned_on
-- -------+---------+------------
--  Ada   | Dune    | 2026-09-01
--  Ada   | Kindred | 2026-09-12
--  Linus | Dune    | 2026-09-15
-- (3 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);
INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);
INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);
INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

CREATE TABLE shelves (
  genre text,
  floor integer
);
INSERT INTO shelves VALUES
  ('classic', 1),
  ('fantasy', 2),
  ('sci-fi', 2),
  ('poetry', 3);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Fifteen rows for four loans

The first query should list who borrowed which sci-fi book, but it returns 15 rows for 4 loans: books is joined without a condition. Give it the right join condition. Then add a second query: the title and floor of every book on floor 2, joining books and shelves with USING, sorted by title.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    CROSS JOIN pairs every loan with every sci-fi book: 5 × 3 = 15 rows.

  2. Hint 2

    Replace CROSS JOIN books b with JOIN books b ON b.id = l.book_id.

  3. Hint 3

    books and shelves both have a column genre, so the second query can use JOIN shelves s USING (genre).

Show a solution

One way to solve it. Yours can look different and still pass the checks.

-- Every loan of a sci-fi book: who borrowed which title.
SELECT m.name, b.title
FROM loans l
JOIN members m ON m.id = l.member_id
JOIN books b ON b.id = l.book_id
WHERE b.genre = 'sci-fi'
ORDER BY m.name, b.title;

SELECT b.title, s.floor
FROM books b
JOIN shelves s USING (genre)
WHERE s.floor = 2
ORDER BY b.title;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Every loan of a sci-fi book: who borrowed which title.
SELECT m.name, b.title
FROM loans l
JOIN members m ON m.id = l.member_id
CROSS JOIN books b
WHERE b.genre = 'sci-fi'
ORDER BY m.name, b.title;

test.sql

-- test: The first query lists the four sci-fi loans, the second the four books on floor 2
-- output:
--  name  |    title
-- -------+-------------
--  Ada   | Dune
--  Ada   | Kindred
--  Grace | Neuromancer
--  Linus | Dune
-- (4 rows)
--
--     title    | floor
-- -------------+-------
--  Dune        |     2
--  Kindred     |     2
--  Neuromancer |     2
--  The Hobbit  |     2
-- (4 rows)

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);
INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);
INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);
INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

CREATE TABLE shelves (
  genre text,
  floor integer
);
INSERT INTO shelves VALUES
  ('classic', 1),
  ('fantasy', 2),
  ('sci-fi', 2),
  ('poetry', 3);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

A column name both tables have

SELECT id, title
FROM books b
JOIN loans l ON l.book_id = b.id;

What psql prints

ERROR:  column reference "id" is ambiguous

Why, and the fix

books and loans both have a column id, and PostgreSQL will not guess which one you mean. Qualify it with the alias: b.id for the book, l.id for the loan. Qualifying every column in a join (b.title too) keeps the query working when a table gains a column with the same name later.

The table’s name after it has an alias

SELECT books.title
FROM books AS b
JOIN loans AS l ON l.book_id = b.id;

What psql prints

ERROR:  invalid reference to FROM-clause entry for table "books"

Why, and the fix

An alias replaces the table’s name for the whole query, so books.title no longer works once FROM says books AS b. PostgreSQL adds the hint Perhaps you meant to reference the table alias "b". Write b.title, or leave out the alias.

USING with columns of different names

SELECT m.name, l.loaned_on
FROM loans l
JOIN members m USING (member_id);

What psql prints

ERROR:  column "member_id" specified in USING clause does not exist in right table

Why, and the fix

USING (member_id) needs a column member_id in both tables, but in members the column is called id. USING only works when the names match. Write the condition out: JOIN members m ON m.id = l.member_id.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

A join pairs rows that meet a condition

loans stores only numbers: book_id 1, member_id 3. To see the title, FROM loans JOIN books ON books.id = loans.book_id pairs each loan with every book for which the ON condition is true, and each pair becomes one result row. A loan matches exactly one book here, so there are 5 rows. Dune was loaned twice, so it appears twice. Beloved and The Hobbit were never loaned: no loan matches them, and an inner join leaves them out. JOIN on its own means INNER JOIN. Everything after FROM (WHERE, ORDER BY, LIMIT) then works on the joined rows.

Aliases, qualified names and USING

FROM loans AS l JOIN books AS b gives each table a short name for this query (AS is optional). Once a table has an alias, only the alias works: books.title then fails. Write l.id and b.id when both tables have an id; a bare id fails with column reference "id" is ambiguous. Qualifying every column is good style, so the query survives a new column later. When both tables name the joining column the same, USING (genre) is short for ON b.genre = s.genre, and SELECT * shows genre once. loans.member_id and members.id have different names, so USING cannot join them.

Three tables, and the join without a condition

Each JOIN adds one table with its own ON: FROM loans l JOIN members m ON m.id = l.member_id JOIN books b ON b.id = l.book_id gives one row per loan, with the member’s name and the title. The joins run left to right, and each ON may use the tables before it. Leave the condition out and you get every combination: loans CROSS JOIN books pairs 5 loans with 6 books, 5 × 6 = 30 rows. FROM loans, books without a WHERE does the same. So a result much larger than the table you started from means a missing or wrong join condition. JOIN without ON or USING is a syntax error.

Sources

Last reviewed September 30, 2026