Subqueries: a Query Inside a Query

Some of Lora's questions are naturally two questions stacked on top of each other. "Which films run longer than the average film?" cannot be answered in one thought — first you need the average, then you can compare each film against it. SQL has a shape for exactly this: a subquery, a complete query in parentheses whose answer feeds the query around it.

The idea rests on a promise planted quietly back in Topic 10: every query returns a table. If a query's result is a table, then anywhere a query expects a value or a set of values, another query can stand in and supply them. Queries can eat queries — and once you see it, half of everyday analytical SQL falls open.

The Scalar Subquery

Picture answering the films question by hand. You'd work out the average runtime first, jot it on a scrap of paper — 102 minutes — and then go down the list of films comparing each one against the scrap. The scrap is the subquery: a small side-calculation whose one result the main task consumes. That's the whole mental model, so let's write it in real terms.

SELECT title, runtime_min
FROM films
WHERE runtime_min > (SELECT AVG(runtime_min) FROM films);

The inner query, SELECT AVG(runtime_min) FROM films, collapses the whole films table to a single number: 102. The outer query then behaves exactly as if you had typed WHERE runtime_min > 102 — the subquery's answer is used like a literal value. On the Marquee's three films, only one clears the bar:

A subquery that boils down to one row, one column — one value — is called a scalar subquery, and it can stand anywhere a single value could: after a comparison in WHERE, even in the SELECT list. One honest edge to know now rather than later: if a query in that position returns more than one row, the database refuses, because a comparison needs one value to compare against.

IN and Lists

A subquery doesn't have to shrink to one value — it can hand back a whole column of them, and the outer query can test membership against that list with IN. Lora's question: which films have screenings on screen 2? The inner query gathers the film ids that appear in screen 2's schedule; the outer keeps the films whose id is in that set.

SELECT title
FROM films
WHERE film_id IN (SELECT film_id FROM screenings WHERE screen = 2);

Read IN as the plain question "is this value in that set?" — for each film, true keeps the row, false drops it, the same row-by-row thinking as every WHERE since Chapter 3. The inner query built the set; the outer query interrogated it.

Subquery or JOIN?

Sharp readers will have noticed that the screen-2 question smells like a JOIN — films connected to screenings through film_id is an arrow straight off the schema diagram. Correct, and here is the same question written both ways:

-- as a subquery: screenings only filter the films
SELECT title
FROM films
WHERE film_id IN (SELECT film_id FROM screenings WHERE screen = 2);

-- as a JOIN: DISTINCT collapses repeat matches
SELECT DISTINCT f.title
FROM films f
JOIN screenings s ON s.film_id = f.film_id
WHERE s.screen = 2;

Both return the same titles, and the choice between them is taste more than law — but the taste has a rule of thumb worth carrying. Reach for a JOIN when you need columns from both sides in the answer (the title and the showtime). Reach for a subquery when one table merely filters the other and contributes nothing to the output, as screen 2's schedule does here. Fluent readers use both shapes daily and translate between them without ceremony.

Don't Nest for Sport

Since queries can eat queries, nothing stops a subquery from containing its own subquery, and so on downward. Resist. Two levels read fine; five read like a legal document, and knowing where to stop is part of the craft. When a question genuinely needs many stacked steps, grown-up SQL has a kinder tool — WITH … AS, which lets you name each step before using it. Its name is all this book will drop; the deep-dive course picks it up properly.

One performance worry, defused before it forms: "the inner query runs once per row, so this must be slow." Usually not — the engine typically computes an independent inner query once, or quietly rewrites the whole thing into a join-like plan. Write the version that says what you mean most clearly, and measure before optimizing; that is Chapter 8's spirit, one chapter early.