الاستعلامات الفرعية (Subqueries)

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.

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:

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:

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?

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.