Subquery and Common Table Expression

A subquery is a query used inside another query. A common table expression, or CTE, names a query result with WITH for use within one statement. Both express intermediate results; neither automatically creates a permanent table.

Suppose a report needs monthly spend and each customer’s first observed purchase month. Name the paid-order input, then the customer-month totals, then the first-month result. To inspect an intermediate CTE, retain its preceding WITH definitions and select from it. Its name does not persist after that statement ends.

A scalar subquery supplies one column and at most one row as a value. Under DuckDB’s default behavior, zero rows gives NULL and multiple rows raise an error. IN tests membership in a subquery result, while EXISTS tests whether it returns any row. A correlated subquery refers to an outer value; this is a logical dependency, not proof that the engine executes it separately for every outer row.

The optimizer can rewrite, inline, or materialize parts of a query. A deeper nesting level is not a fixed execution schedule, and replacing a subquery with a join does not guarantee better performance. Choose readable boundaries, then inspect the actual plan. Also preserve the analytical scope: filtering to March before finding a first purchase finds the first purchase observed within March, not necessarily the first purchase ever.

Further reading

• DuckDB Documentation, WITH Clause

See the worked examples in SQL from Zero to Working.