Subqueries

A subquery is a query used inside another, in one of three positions: as a single value, as a list, or as a table.

As a value: WHERE year > (SELECT AVG(year) FROM books).

As a list: WHERE author_id IN (SELECT id FROM authors WHERE country = 'US').

As a table: FROM (SELECT ...) t, where it must be given a name.

The inner query runs once and produces one number. The outer query then compares every row against it.

✦ NOT IN and NULL is a real trap

If the subquery returns even one NULL, NOT IN returns no rows at all, because 'not equal to unknown' is unknown rather than true. It does not error. Use NOT EXISTS instead, which handles NULLs correctly, or filter the NULLs out of the subquery.