Joins

Written Explanation

The inner join

SELECT b.title, a.name
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

• FROM books b names the first table and gives it the alias b; INNER JOIN authors a adds the second with the alias a.
• ON b.author_id = a.id is the join condition: a book and an author are paired when the book's foreign key equals the author's primary key. The pattern is almost always foreign key = primary key.
• Columns are written with the alias, b.title, a.name. This is required whenever a name exists in both tables (both tables have id and name), otherwise PostgreSQL stops with column reference "id" is ambiguous. It is good practice anyway.

An inner join returns a row for each pair of rows that satisfies the condition, and drops rows that have no partner on the other side. The PostgreSQL manual says it the same way: for each row in the first table, one joined row per matching row of the second; the rows without a match do not appear. INNER is optional: plain JOIN means inner join.

The left join

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

A left join keeps every row of the left table (the one written before LEFT JOIN). A left row with a match appears once per match; a left row with no match appears once, with NULL in all the right-hand columns. The manual describes it as an inner join followed by adding the unmatched left rows, so the result has at least one row for every left row. LEFT JOIN is short for LEFT OUTER JOIN.

Which one do I need?

Ask: "Can a row on the left have no partner on the right, and do I still want to see it?"

"Left" and "right" refer only to the position in the text. Swapping the table order and writing RIGHT JOIN gives the same result; most people simply always write left joins.

Finding rows with no match

The left join leaves NULL in the right columns exactly when no match exists. So filtering on a right-hand column that can never be NULL in a real row (the primary key) finds the unmatched rows:

SELECT m.name
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
WHERE l.id IS NULL;

This is the standard answer to "members with no loans".

Joining three tables

Chain more joins, each 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

loans is the junction between members and books, so it is the natural starting point.

Counting across a left join

After a left join, COUNT(*) counts rows, and a member with no loans still produces one row (with NULLs). Use COUNT(l.id), which skips NULL, to count real loans. This is Lesson 5's COUNT(*) versus COUNT(column) again.

Conditions in WHERE versus ON

A WHERE condition on a right-hand column, such as WHERE l.loaned_on >= '2025-10-01', is tested after the join. For the NULL-extended row of a member without loans the condition is not true, so that member disappears again, and your left join has quietly become an inner join. When you want to restrict which right-hand rows are matched while keeping every left row, put the condition in the ON: LEFT JOIN loans l ON l.member_id = m.id AND l.loaned_on >= '2025-10-01'.