Skip to content
aviral gupta
Get in touch

// B3.3 · ~30 min · Beginner

Outer joins

After this lesson you can keep the rows a join drops, read their empty columns, place a filter so they stay, and list members or books with no partner.

Lesson 3 of 6 in B3 Joins and aggregates

You will be able to

  • Keep unmatched rows with LEFT, RIGHT or FULL JOIN and read the NULL-filled columns
  • Put a condition on the optional table in ON or in WHERE, knowing which rows each keeps
  • Find rows without a partner with an anti-join: LEFT JOIN … WHERE key IS NULL

  1. Warm-up · Activity 1 of 7

    Warm-up from the last lesson. Ada and Grace have two loans each, Linus one, Margaret none. Which member is missing from the result of this inner join?

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

  2. Predict · Activity 2 of 7

    Predict before you read on. The inner join of members and loans returned 5 rows, and Margaret has no loan. How many rows does the LEFT JOIN return?

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

  3. Practice · Activity 3 of 7

    Fill in the key word so that every book is listed, also the ones that were never loaned.

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

  4. Practice · Activity 4 of 7

    Match each join to the rows it returns.

  5. Practice · Activity 5 of 7

    SELECT m.name, l.id FROM … Which FROM clause returns the same rows as loans l RIGHT JOIN members m ON l.member_id = m.id?

  6. Brain teaser · Activity 6 of 7

    Brain teaser. You want the members who have never borrowed a book. Loans 2, 4 and 5 are still open (returned_on is NULL). Which names does this return?

    -- books: 1 Dune, 2 Emma, 3 Kindred, 4 Beloved, 5 The Hobbit, 6 Neuromancer
-- members: 1 Ada, 2 Grace, 3 Linus, 4 Margaret
-- loans (id, book_id, member_id, returned_on):
--   (1, 1, 1, 2026-09-10), (2, 3, 1, NULL), (3, 2, 2, 2026-09-20),
--   (4, 1, 3, NULL), (5, 6, 2, NULL)

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

  7. Apply · Activity 7 of 7

    Mini-task. In the editor of the worked example, write a loan history for the whole catalogue: every book, with the name of each member who borrowed it and the loan date, in book id order and then loan id order. Books never loaned must appear once, with empty name and date. Then change one LEFT JOIN to JOIN and predict which rows disappear before you run it.

    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

Members without loans, books without shelves

setup.sql loads the library from module B1 (books, members, loans) and the shelves table of the last lesson. main.sql lists every member with their loans, finds the members who never borrowed anything, and joins books and shelves so that the rows without a partner on either side stay. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Every member, with their loans if they have any.
SELECT m.name, l.id AS loan_id, l.loaned_on
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
ORDER BY m.name, l.id;

-- 2. Members who have never borrowed a book (anti-join).
SELECT m.name
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
WHERE l.id IS NULL;

-- 3. Books and shelves: unmatched rows from both sides.
SELECT genre, b.title, s.floor
FROM books b
FULL JOIN shelves s USING (genre)
ORDER BY genre, b.title;

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

   name   | loan_id | loaned_on
----------+---------+------------
 Ada      |       1 | 2026-09-01
 Ada      |       2 | 2026-09-12
 Grace    |       3 | 2026-09-05
 Grace    |       5 | 2026-09-18
 Linus    |       4 | 2026-09-15
 Margaret |         |
(6 rows)

   name
----------
 Margaret
(1 row)

  genre  |    title    | floor
---------+-------------+-------
 classic | Emma        |     1
 fantasy | The Hobbit  |     2
 fiction | Beloved     |
 poetry  |             |     3
 sci-fi  | Dune        |     2
 sci-fi  | Kindred     |     2
 sci-fi  | Neuromancer |     2
(7 rows)
  • Margaret has no loan, so the LEFT JOIN adds her once with loan_id and loaned_on NULL; psql prints NULL as an empty cell.
  • WHERE l.id IS NULL keeps only the rows the LEFT JOIN added. A real loan always has an id, so only members without any loan remain.
  • FULL JOIN keeps Beloved, whose genre fiction has no shelf, and the poetry shelf, which has no book. genre comes from whichever side has a value.
  • An inner join of books and shelves would return only the five matched 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

Every member and their books

List every member with the title of each book they borrowed, sorted by name and then title. Members with no loan must appear once, with an empty title. The starter uses LEFT JOIN for loans, but Margaret is still missing. Find out why and fix 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.

Hints
  1. Hint 1

    After the LEFT JOIN, Margaret’s row has NULL in l.book_id.

  2. Hint 2

    The next join is an inner join: b.id = NULL is never true, so it drops her row again.

  3. Hint 3

    Make the second join an outer join as well: LEFT JOIN books b ON b.id = l.book_id.

Show a solution

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

-- Every member, with the titles they borrowed.
SELECT m.name, b.title
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
LEFT JOIN books b ON b.id = l.book_id
ORDER BY m.name, 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 member, with the titles they borrowed.
SELECT m.name, b.title
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
JOIN books b ON b.id = l.book_id
ORDER BY m.name, b.title;

test.sql

-- test: All four members are listed, Margaret once with an empty title
-- output:
--    name   |    title
-- ----------+-------------
--  Ada      | Dune
--  Ada      | Kindred
--  Grace    | Emma
--  Grace    | Neuromancer
--  Linus    | Dune
--  Margaret |
-- (6 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

Books nobody has borrowed

Write two queries, each sorted by title. First: the titles of the books that were never loaned. Second: the titles of the books on the shelf right now, that is, books with no open loan (returned_on IS NULL). Use LEFT JOIN with WHERE l.id IS NULL in both; in the second, the open-loan test belongs in ON.

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

    An anti-join: LEFT JOIN loans, then keep the rows where l.id IS NULL, the books that found no loan.

  2. Hint 2

    For the second query, only open loans should count as a match, so l.returned_on IS NULL goes into ON with AND.

  3. Hint 3

    Emma was loaned and returned: with the test in ON, she finds no open loan and comes back.

Show a solution

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

SELECT b.title
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
WHERE l.id IS NULL
ORDER BY b.title;

SELECT b.title
FROM books b
LEFT JOIN loans l
  ON l.book_id = b.id
 AND l.returned_on IS NULL
WHERE l.id IS NULL
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

-- The books that have been loaned.
SELECT b.title
FROM books b
JOIN loans l ON l.book_id = b.id
ORDER BY b.title;

test.sql

-- test: First Beloved and The Hobbit, then Beloved, Emma and The Hobbit
-- output:
--    title
-- ------------
--  Beloved
--  The Hobbit
-- (2 rows)
--
--    title
-- ------------
--  Beloved
--  Emma
--  The Hobbit
-- (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.

Common mistakes

OUTER JOIN without LEFT, RIGHT or FULL

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

What psql prints

ERROR:  syntax error at or near "OUTER"

Why, and the fix

OUTER is optional noise after LEFT, RIGHT or FULL, and cannot stand alone: PostgreSQL has to know which side to keep. Write LEFT JOIN (or LEFT OUTER JOIN) to keep every member.

The join condition in WHERE instead of ON

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

What psql prints

ERROR:  syntax error at or near "WHERE"

Why, and the fix

An outer join, like an inner one, needs its condition in ON (or USING) right after the table. Even with a CROSS JOIN, the condition in WHERE would run after the join and drop Margaret. Write LEFT JOIN loans l ON l.member_id = m.id.

An ON that uses a table joined later

SELECT m.name, b.title
FROM members m
LEFT JOIN loans l ON l.book_id = b.id
LEFT JOIN books b ON b.id = l.book_id;

What psql prints

ERROR:  missing FROM-clause entry for table "b"

Why, and the fix

Joins are built left to right, and each ON can only use the tables joined so far. loans must be joined to members through l.member_id = m.id; books comes next and uses l.book_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

LEFT JOIN keeps every row of the left table

members LEFT JOIN loans ON l.member_id = m.id first does the inner join: 5 rows, one per loan. Then it adds each member without a match once, with NULL in every column of loans: Margaret, so 6 rows. psql prints those NULLs as empty cells. Which table is left matters: loans LEFT JOIN members keeps every loan, and since every loan has a member, that is just the 5 matched rows. In a chain, keep the outer join going: after members LEFT JOIN loans, a plain JOIN books ON b.id = l.book_id finds no book for Margaret’s NULL and drops her again; write LEFT JOIN books.

RIGHT and FULL JOIN

RIGHT JOIN keeps every row of the right table instead: shelves s RIGHT JOIN books b ON b.genre = s.genre keeps all six books, and Beloved, whose genre has no shelf, gets an empty floor. It is the same as books b LEFT JOIN shelves s, apart from the column order, which is why most people write LEFT JOIN only. FULL JOIN keeps the unmatched rows of both sides: books FULL JOIN shelves USING (genre) returns the five matches, Beloved without a floor and the poetry shelf without a book, 7 rows. With USING, the shared genre column shows the value of whichever side has one.

ON or WHERE, and the anti-join

A condition in ON is applied before the outer join adds its NULL rows; a condition in WHERE after it. members LEFT JOIN loans ON l.member_id = m.id AND l.returned_on IS NOT NULL keeps all four members, with returned loans where they exist. The same test in WHERE removes Linus and Margaret, whose rows have NULL there. WHERE can also use this on purpose: LEFT JOIN loans … WHERE l.id IS NULL keeps only the members with no loan at all, an anti-join. Test a column that is never NULL in a real match, such as l.id. returned_on is NULL for open loans too, so it would keep those as well.

Sources

Last reviewed September 30, 2026