Subqueries & CTEs

A subquery is a query inside a query – single-row returns one value for `=`, multi-row returns a set for `IN`/`ANY`/`ALL`, and correlated re-runs per outer row.

Old PostgreSQL always materialized CTEs as a fence blocking filter pushdown; modern versions inline single-use CTEs, and MATERIALIZED / NOT MATERIALIZED let you force either behavior.

A non-correlated subquery runs once and is reused; a correlated subquery references the outer row and logically re-runs per row, which is why it can be slow at scale.

Scalar returns one value, row returns one row, table returns a result set used in FROM, and correlated is any subquery that depends on the outer row.

Mark reads as readOnly so a routing datasource sends them to a replica and everything else to the primary, but send must-be-fresh reads back to the primary because of lag.

A CTE is a named subquery scoped to one statement and usually inlined; a temp table is a real, session-lived table you reuse across many statements.

A CTE is a named `WITH` block that makes queries read top-to-bottom like steps, and a recursive CTE walks hierarchies with an anchor row plus a self-referencing recursive step.