Subqueries and CTEs

A subquery is a query nested inside another. A scalar subquery returns one value and can sit anywhere a value is allowed — for example comparing each row to an overall average.

A subquery returning a column can feed IN, and one returning rows can act as a derived table in the FROM clause.

Once queries get long, a common table expression (WITH ... AS) gives each step a name. It reads top to bottom instead of inside out, and you can chain several CTEs.