Queries inside queries

One query answers, another asks

A subquery is a whole query in parentheses, used where a value or a list would go. In WHERE year > (SELECT avg(year) FROM books), the inner query computes one number and the outer one compares every row against it — one question answered first, then the question that needed it.

In a condition that expects a list, the subquery returns a column: WHERE author_id IN (SELECT id FROM authors WHERE country = 'Argentina'). The database runs the inner query once and keeps the rows that are in the answer.

WITH gives the answer a name

A common table expression — WITH average AS (SELECT avg(year) AS year FROM books) — names a subquery and puts it at the top of the statement. The rest of the query then reads in order: first the pieces, then the question that uses them.

The name is what makes a hard query readable. Two or three expressions, each a small honest question, and the final SELECT that only has to combine them — instead of one deeply nested clause that has to be read from the middle out.

Where subqueries may stand

The three common places are a scalar value in a comparison, a list after IN or NOT IN, and a whole table in FROM — a derived table you can join against like any other. A subquery in FROM must be named, which is exactly what a CTE does at the top of the statement.

Each place has the same rule about shape: one value for a comparison, one column for a list, and any number of columns for a table.

An empty inner answer is worth thinking about in advance. A scalar subquery over no rows returns NULL rather than zero, so WHERE year > (SELECT avg(year) FROM books WHERE year > 2100) compares every row with an unknown and returns nothing at all — not an error, just an empty table. Wrapping the subquery in COALESCE is how the query says what an empty answer should mean.

The inner query can see the outer row

A subquery that mentions a column from the query around it is correlated. It cannot be run once and reused: it runs for each outer row, with that row's values, which is how EXISTS (SELECT 1 FROM books b WHERE b.author_id = a.id) asks a question about one author at a time.

The SELECT 1 is a convention, not a special shortcut: EXISTS only asks whether the subquery produced a row, so the columns do not matter. NOT EXISTS is the safe way to ask for rows with no match, because it does not compare values and so cannot be tripped by NULL.

Correlated subqueries are readable and sometimes slow. The database may rewrite one as a join; when it does not, the inner query runs once per outer row. The shape of the question is worth keeping even when the plan needs a second look.

WITH is a name, not a table

A CTE is visible to the statement that declares it, and to later CTEs in the same WITH list. It is not visible to the next statement, and it is not stored: the name is shorthand for a query, evaluated when the statement runs.

SQLite may inline a CTE rather than materialise it, so a CTE is not automatically a performance win. What it buys is structure: each name holds one question, and the final SELECT reads like the sentence you started with.

WITH RECURSIVE is the exception that earns the keyword: the CTE refers to itself, starting from a base row and adding rows until the join finds nothing new. It is how a hierarchy — a manager chain, a category tree — is walked in one statement, and it needs a stopping condition or it runs until the database gives up.

The same question, twice: nested, then named

SELECT title, year
FROM books
WHERE year > (SELECT avg(year) FROM books)
ORDER BY year DESC;

WITH average AS (
  SELECT avg(year) AS year FROM books
)
SELECT b.title, b.year, a.year AS average
FROM books b, average a
WHERE b.year > a.year
ORDER BY b.year DESC;

Try these next

• Rewrite the above-average query as two CTEs: one for the average, one for the books that beat it.
• Use EXISTS to list authors who have at least one book, then NOT EXISTS for the rest.
• Write a recursive CTE that counts from 1 to 10.