A query inside a query. The inner one runs first and hands its answer to the outer one — except in the correlated case, where it runs again for every single row. Watch that happen.
A scalar subquery returns one value, such as a maximum date. A subquery in FROM acts as a derived table. A subquery in WHERE can test membership or existence. These shapes let one query express a calculation or filter that would otherwise need a separate statement.
The inner query produces one row per customer; the outer query filters those results. A CTE can make the same steps easier to name. See common table expressions.
An uncorrelated subquery can be evaluated independently of the current outer row. A correlated subquery refers to a column from that row, as in a test for whether a customer's orders exist. Correlation describes the logic, not necessarily the physical execution: an optimizer may transform or decorrelate it.
For a yes/no existence question, EXISTS often states the intent directly. A JOIN instead returns matching pairs and may multiply outer rows when several matches exist. Choose based on result shape, then inspect the plan if performance matters. See EXISTS, IN, and JOIN.
A scalar subquery should return at most one row; behavior when it returns more than one is engine-specific. PostgreSQL reports an error, while SQLite uses the first result row, so do not rely on accidental multi-row results. NOT IN can produce UNKNOWN if its candidate set contains NULL. Test empty input, duplicate matches, and NULLs when those cases are possible.
A scalar subquery can provide a value in a SELECT list, such as each product's latest price date. Ensure its logic returns no more than one row: use an aggregate, a unique key, or a deterministic ORDER BY with a supported row limit. If there are no rows, many engines return NULL; if there are several, behavior may be an error. A correlated scalar subquery may be evaluated many times or transformed by the optimizer, so inspect the plan on large data.
Use a scalar subquery when one value belongs beside each row, EXISTS when only match existence matters, and a derived table when an intermediate result has its own columns or grain. A subquery in FROM needs an alias in many engines. If the same calculation is difficult to read inline, a CTE can name it. These are readability and semantic distinctions; the optimizer may produce the same physical plan. Measure only after confirming the query returns the intended rows.
About to run: switch Kind of Subquery to Correlated - runs once per row. Before it does — what happens to the readout?
Committing to an answer first is the point — the reveal runs the experiment on the visualisation above and reads the real value back, so nothing here is scripted.
Say the answer out loud before you reveal it — recalling it is what makes it stick, and rereading it is not.
What does this module say about “Three common shapes”?
What does this module say about “Correlated and uncorrelated”?
What does this module say about “Check cardinality and NULLs”?
The inner query runs first and hands its answer to the outer one. Scalar, IN-list, derived table, and a correlated subquery re-running once per row.