Skip to content
Ameen Abozaid
Englishالعربية
PublishedBEG-050-L07

Subqueries

  • Lesson 7 of 9
  • 12 min read
  • Beginner
On This Lesson 11 topics

Lesson Overview

A join combines tables side by side. Sometimes you do not need columns from the second table at all: you only need it to decide which rows of the first table to keep. "Which books are on loan right now?" needs the books' titles, and only needs the loans to know which book ids to keep. For this a subquery is often the clearer tool: a SELECT written inside another statement, in parentheses.

In the library picture, the librarian first asks the loan desk, "Give me the numbers of the books that are out," and then asks the catalog, "Now show me the titles that match these numbers." Two questions, the first feeding the second.

This lesson covers the two shapes you need: a subquery that returns a list (used with IN and NOT IN) and one that returns a single value (used with >, =, and so on). It also covers the NOT IN trap with NULL, which is the NULL lesson of Lesson 4 coming back.

This lesson builds on Lessons 4 to 6: WHERE, IS NULL, aggregates, and joins.

Learning Objectives

By the end of this lesson you will be able to:

  • Write a subquery in parentheses inside a WHERE clause.
  • Use IN and NOT IN with a subquery that returns one column.
  • Use a scalar subquery (one row, one column) in a comparison.
  • Decide whether a join or a subquery is clearer for a question.
  • Explain why NOT IN can return no rows when the list contains NULL.
  • Rewrite a "rows with no match" question both as a subquery and as a left join.

Written Explanation

What a subquery is

A subquery is a complete SELECT inside parentheses, used where a value or a list of values is expected. The database runs the inner query and uses its result in the outer one.

sql
SELECT title
FROM books
WHERE id IN (SELECT book_id FROM loans WHERE returned_on IS NULL);

The inner query returns the book_ids of loans with no return date: 2, 7, 1, 6 on our data. The outer query keeps the books whose id is in that list. You can read the whole statement aloud: "titles of books whose id is among the ids of books that are out".

Shape 1: a list, with IN and NOT IN

  • x IN (subquery) is true when x equals any value the subquery returns.
  • x NOT IN (subquery) is true when x equals none of them.

The subquery must return exactly one column. If it returns more, PostgreSQL stops with subquery has too many columns.

"Books never loaned" reads naturally with NOT IN:

sql
SELECT title FROM books WHERE id NOT IN (SELECT book_id FROM loans);

Shape 2: a single value (a scalar subquery)

A scalar subquery returns one row and one column, and can be used like a single value:

sql
SELECT title, published_year
FROM books
WHERE published_year > (SELECT AVG(published_year) FROM books);

"Books newer than the average year" cannot be written with a plain WHERE alone, because WHERE cannot contain an aggregate (Lesson 5). The subquery computes the average first.

The PostgreSQL manual says it is an error if a scalar subquery returns more than one row or column. If it returns no rows, there is no error and the value is NULL.

The NOT IN and NULL trap

The PostgreSQL manual spells out the rule: for NOT IN, if the list contains a NULL and there is no equal value, the result is null rather than true, and a row whose condition is null is not returned. One NULL anywhere in the list makes NOT IN return no rows at all. The same would be true for the subquery form.

In our schema loans.book_id is declared NOT NULL, so NOT IN (SELECT book_id FROM loans) is safe here. But if a column can be NULL (like returned_on or published_year), either exclude the NULLs inside the subquery with WHERE column IS NOT NULL, or use a left join with IS NULL, which has no such trap.

Subquery or join?

JoinSubquery
Need columns from both tables in the resultYesNo: only the outer table's columns
Only filtering the first table by something in the secondWorksOften reads more clearly
A row can match several rows in the other tableRepeats the row once per matchIN returns each outer row once

The last row matters. Joining books to loans gives one row per loan, so a book loaned twice appears twice; id IN (SELECT book_id FROM loans) gives each book once. Neither is better in general: choose the one that states the question most directly, and verify the row count.

(For completeness: PostgreSQL also has EXISTS (subquery), which is true when the inner query returns at least one row. It is a close cousin of IN, but we do not need it in this module.)

Reading nested queries

A subquery can contain another subquery, but beyond one level the query is usually clearer as a join. Write the inner query first and run it alone to see what it returns, then wrap it in the outer one. If the inner query refers to nothing from the outer one, as in all our examples, you can always run it by itself to check it.

Examples

All results are real output from PostgreSQL 18.3 on the seeded library.

Example 1: filtering with IN (simple)

Which books are on loan right now?

sql
SELECT title
FROM books
WHERE id IN (SELECT book_id FROM loans WHERE returned_on IS NULL)
ORDER BY title;
text
        title         
----------------------
 A Wizard of Earthsea
 Kindred
 Palace Walk
 The Dispossessed
(4 rows)

Nesting one more level answers a bigger question: which authors have a book out? Each level answers a smaller question:

sql
SELECT name FROM authors
WHERE id IN (SELECT author_id FROM books
             WHERE id IN (SELECT book_id FROM loans WHERE returned_on IS NULL))
ORDER BY name;
text
      name      
----------------
 Naguib Mahfouz
 Octavia Butler
 Ursula Le Guin
(3 rows)

It works, but three levels is where a join starts to read better.

Example 2: a scalar subquery (practical)

Books newer than the average publication year:

sql
SELECT title, published_year
FROM books
WHERE published_year > (SELECT AVG(published_year) FROM books)
ORDER BY published_year;
text
        title         | published_year 
----------------------+----------------
 Kindred              |           1979
 Parable of the Sower |           1993
 Fledgling            |           2005
 Lavinia              |           2008
(4 rows)

The inner query returned 1977.4 (computed over the ten books that have a year). The book with a NULL year is not returned, because comparisons with NULL are never true (Lesson 4). A scalar subquery that returns too many rows is an error:

sql
SELECT title FROM books
WHERE published_year = (SELECT published_year FROM books WHERE author_id = 1);
text
ERROR:  more than one row returned by a subquery used as an expression

Author 1 has four books, so the inner query returned four values where one was expected. (Using IN instead of = would turn this into a list comparison.)

Example 3: "never loaned", the NULL trap, and the join alternative (realistic)

Books that have never been loaned:

sql
SELECT title FROM books
WHERE id NOT IN (SELECT book_id FROM loans)
ORDER BY title;
text
      title       
------------------
 Arrow of God
 Collected Verses
 Fledgling
 Lavinia
(4 rows)

book_id can never be NULL here, so this is correct. To see the trap, imagine the list contained a NULL. We can simulate it with a literal list:

sql
SELECT title FROM books WHERE id NOT IN (1, 2, NULL);
SELECT title FROM books WHERE id IN (1, 2, NULL) ORDER BY id;

The first returned (0 rows); the second returned two rows (books 1 and 2). IN still finds the matches it can, but NOT IN can never be sure that a book is not the unknown value, so it returns nothing. A defensive version adds WHERE book_id IS NOT NULL inside the subquery.

The same question as a left join, which has no such trap:

sql
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;

It returned the same four titles. Finally, here is where IN and a join differ in the row count. Books that have ever been loaned: IN (SELECT book_id FROM loans) returned 7 rows, but books JOIN loans returned 8, because The Dispossessed was loaned twice and appears twice.

Backend Insight

Subqueries show up all the time in real systems: "customers who have placed an order", "products that were never sold", "users whose last login is older than the average". For these filtering questions you will write them directly in SQL, or you will meet them behind a framework that generates SQL for you.

Two practical habits. First, build subqueries inside-out: run the inner query alone, confirm the values, then wrap it. Second, remember that the database decides how to run the query: a subquery is not necessarily slower than a join, or the other way round. The PostgreSQL planner often handles both well, and measuring on realistic data is how you would decide, not guessing.

The NOT IN trap is a favourite bug in production code because it only appears when a NULL reaches the data months later. A query that was correct on the test data can start returning nothing. Prefer a left join with IS NULL, or add the IS NOT NULL guard, when the column can be NULL.

Common Mistakes

Mistake 1: A subquery that returns the wrong column

  • Incorrect: WHERE id IN (SELECT id FROM loans) compares book ids with loan ids.
  • Correct: WHERE id IN (SELECT book_id FROM loans).

The incorrect version is valid SQL and returned 8 books in our test, none of which are the ones you wanted. Check that the inner column means the same thing as the outer one.

Mistake 2: Returning two columns to IN

IN (SELECT book_id, member_id FROM loans) fails with subquery has too many columns. Select one column.

Mistake 3: A scalar subquery that returns several rows

Use IN for lists, = or > only when the inner query must return one value.

Mistake 4: NOT IN with a nullable column

One NULL makes the result empty. Add IS NOT NULL, or use a left join.

Mistake 5: Using a subquery when you need columns from the other table

SELECT cannot take a column from a table that only appears in a subquery. If you need member name along with book title, use a join.

Exercises

Try each exercise yourself before opening the solutions.

Level A: Foundation

A1. Write a query for the names of members who have at least one loan, using IN and a subquery.

Show solution A1

A1. SELECT name FROM members WHERE id IN (SELECT member_id FROM loans); (four members on the seeded data)

A2. What does the scalar subquery (SELECT MAX(published_year) FROM books) return?

Show solution A2

A2. A single value, the largest published_year: 2008 on the seeded data (the NULL year is ignored by MAX).

A3. Which is the correct column in the inner query to find books on loan: loans.id or loans.book_id? Why?

Show solution A3

A3. loans.book_id. The outer query compares with books.id, so the inner list must hold book ids. loans.id is a loan number, a different thing.

Level B: Application

B1. List titles of books that have never been loaned, with a subquery. (Four titles on the seeded data.)

Show solution B1

B1. SELECT title FROM books WHERE id NOT IN (SELECT book_id FROM loans);

B2. List titles of books published in the same year as the newest book, using a scalar subquery.

Show solution B2

B2. SELECT title FROM books WHERE published_year = (SELECT MAX(published_year) FROM books);

B3. List the names of members who have a loan that is not returned and whose due_on is before '2025-10-15'.

Show solution B3

B3. SELECT name FROM members WHERE id IN (SELECT member_id FROM loans WHERE returned_on IS NULL AND due_on < '2025-10-15'); On the seeded data this returns Amal and Bilal.

Level C: Challenge

C1. Write the "members with no loans" query twice: once with NOT IN and once with a left join. Say which you would trust if loans.member_id were allowed to be NULL, and why.

Show solution C1

C1.

sql
SELECT name FROM members WHERE id NOT IN (SELECT member_id FROM loans);

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

Both return Emil on the seeded data. If member_id could be NULL, a single NULL row would make the NOT IN version return nothing, so you would trust the left join (or add WHERE member_id IS NOT NULL inside the subquery).

C2. A teammate says: "Subqueries are slow; always use joins." Respond with one situation where the subquery is clearer, one where the join is required, and what you would do to decide about speed.

Show solution C2

C2. Subquery clearer: a pure filtering question such as "books never loaned" or "books newer than the average", where only the outer table's columns are needed. Join required: when you need columns from both tables, such as the member's name next to the book title. As for speed, neither is automatically faster; measure on realistic data using the database's tooling, which a later module on performance introduces. In the meantime, choose the clearest form and check that the results are correct.

Mini Challenge

Using the seeded library, write three subquery-based queries:

  1. Members who joined after the member with id = 3 (a scalar subquery on joined_on).
  2. Books by authors from the country 'USA' (the inner query selects author ids).
  3. Books that have been loaned more than once (the inner query groups the loans).

Run each inner query alone first and write down what it returns. Then compare the final row counts with what you expect from the data.

Check your work: query 1 uses a scalar subquery on joined_on; query 2 compares author_id with a list of author ids; query 3 uses GROUP BY book_id HAVING COUNT(*) > 1 inside IN (...).

Summary & Next Lesson

Key takeaways

  • A subquery is a SELECT in parentheses used inside another statement.
  • IN (subquery) and NOT IN (subquery) test against a list; the subquery must return exactly one column.
  • A scalar subquery returns one row and one column and can be used with =, >, and the like; more than one row is an error.
  • NOT IN returns nothing if the list contains NULL; guard with IS NOT NULL or use a left join.
  • Use a subquery to filter by another table; use a join when you need columns from both.

Next lesson

You now have every tool of the module: DDL, DML, SELECT, grouping, joins, and subqueries. The next lesson, "Demo: answering library questions with SQL", puts them together by turning plain-language questions into queries.

Self-check

  1. What must a subquery used with IN return?
  2. What happens when a scalar subquery returns two rows?
  3. Why can NOT IN (SELECT ...) return no rows even when some match is missing?
  4. When would you choose a join over a subquery?
  5. Why does IN (SELECT book_id FROM loans) return a book once even if it was loaned twice?
Show answers
  1. Exactly one column.
  2. PostgreSQL raises an error: more than one row returned by a subquery used as an expression.
  3. If the list contains a NULL, the comparison is unknown, not true, for every row.
  4. When you need columns from both tables in the result.
  5. IN only asks whether the id is somewhere in the list, so duplicates in the list do not repeat the outer row.

References

  • PostgreSQL documentation, Subquery Expressions. Supports IN and NOT IN needing a one-column subquery, the null rule that makes NOT IN return null instead of true, and the description of EXISTS.
  • PostgreSQL documentation, Value Expressions: Scalar Subqueries. Supports that a scalar subquery must return one row and one column, and that zero rows gives NULL.
  • All outputs and error messages came from real runs on PostgreSQL 18.3. The statements that subqueries and joins are not automatically faster or slower than each other are cautious general knowledge that I did not verify on a fetched page.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson

Type to search levels, phases, modules and lessons.