Today the gardener stays in the flower_bed you met in Chapter V — the ten flowers (four Lotus, three Tulip, two Jasmine, one Marigold) are still on the sorting table, still where you left them. In this chapter she'll ask questions about individual flowers whose answers depend on facts about the whole bed.

Some questions need a smaller question answered first. Which flowers are taller than the average flower? To answer it you'd have to know the average — but the average isn't something you can stamp on a single flower's tag. It's a property of the whole bed.

You already know the verb that produces the average: SELECT AVG(height) FROM flower_bed. The trick is feeding that one number into a second query that uses it as a filter.

This is a subquery — a query nested inside another, in parentheses. The metaphor: the gardener fills a smaller basket first (the inner query's result), carries it with her, and consults it as she walks the bed for the larger basket.

Look at this query. Anything inside the inner parentheses is a complete SQL query on its own — a normal SELECT. The database runs it first, gets a result, and then plugs that result into the outer query — unless the inner query mentions a column of the outer row, in which case it's recomputed once per outer row. We come to that on the correlated page.

Three kinds of "shape" the inner basket might come back as, each used differently by the outer query:

• One value (a single number or string) — used with comparison operators like >, =, !=
• A list of values — used with IN (...) or NOT IN (...)
• Any rows at all (yes/no) — used with EXISTS (...)

Each gets its own page next.

The simplest subquery returns one number. Here, the inner SELECT computes the average height across the bed — a single value, about 20.8. The outer SELECT then walks the bed and keeps only the flowers whose height is greater than that.

Any place you'd put a literal — 20 or 'pink' — you can put a subquery in parentheses, as long as it returns one value. All the comparison operators you learned for WHERE work unchanged: =, !=, <, >, <=, >=.

If the inner basket comes back with several values instead of one, = won't work. Use IN instead — it asks "is this value any of those?"

For example: which flowers share a color with at least two others? The inner query gathers the names of those popular colors (groups of three or more); the outer keeps flowers whose color is in that list.

Sometimes you want the complement — flowers that are not in some derived list. Add NOT before IN:

Below: flowers whose height doesn't match any per-color maximum. The inner query finds the max height per color; the outer keeps flowers whose height isn't in that set of maxes.

An honest caveat: this almost answers "not the tallest in its own color group," but not quite. A flower whose height happens to equal another color's max would also be excluded. It works here only because no heights collide across colors in this bed. A truly correct "not the tallest in its group" test needs a correlated subquery — see Page 10.

Sometimes you don't care what the subquery returns — only whether it returns any rows at all. EXISTS turns the subquery into a yes/no answer.

The simplest case is uncorrelated: the inner query doesn't mention the outer row. Below: keep every flower, but only if there's at least one pink in the bed. The inner asks "does any pink flower exist?"; if yes, EXISTS is true for every outer row and all ten are kept. If not, zero come back. An uncorrelated EXISTS acts as a table-wide guard — trivially the same answer for each row.

EXISTS really earns its keep when correlated (Page 10) — the inner then asks a fresh question per outer row, and the yes/no varies row by row.

Subqueries don't only live in WHERE. You can put one in the SELECT list, where it becomes a column of the result. The inner query runs and contributes a value to every row.

Below: each flower's height alongside the bed's overall average — visible side-by-side. The average is computed once and stamped onto each row.

So far the inner basket has been the same for every outer row. A correlated subquery is different: it references a column from the outer query, so the smaller basket is recomputed for each outer row.

Below: each flower's height alongside the average of its own color. The inner query has WHERE color = r.color — the r is an alias for the outer row, and the inner sees it. So pink flowers get the pink average, purples get the purple average, and so on.

A lot of subquery questions can also be expressed as JOINs (Chapter IV). Same answer, different shape. Compare:

Loose rules of thumb:

• Use JOINs when you want columns from both tables in the result.
• Use subqueries when you only want columns from the outer table, filtered by the inner result.
• Use EXISTS when you only need a yes/no decision per outer row.
• Use correlated subqueries when each row needs a computed value derived from its own group.

Most experienced gardeners reach for whichever feels clearer for the question at hand.

One more useful shape worth knowing about: Common Table Expressions (CTEs), written with WITH name AS (SELECT ...) SELECT .... They give a subquery a name you can reuse and read top-to-bottom — useful when one query depends on several intermediate baskets. (Many SQL dialects support them; we won't go deeper here.)

The flower_bed is yours again. Combine subqueries, aggregates, and filters however you like. The result panel below shows the rows.

In the garden of the Basket Inside the Basket, a smaller basket is gathered first, then used inside a larger one. The smaller basket sometimes holds a single value, sometimes a list, sometimes only the answer to yes or no. Whatever it holds, the larger basket asks of it before deciding what to keep.

After this garden, two more remain in Autumn. The first is called the Directory at the Gate: there the gardener finds any flower without walking every bed. The second is called Looking at Neighbors: each flower, standing in its own row, learns to see how it compares to the ones beside it without losing itself. Beyond Autumn lies Winter, where risky changes are wrapped in a TRANSACTION that can be undone. For today, the smaller basket is set down; the larger one comes home heavier than before.