Subqueries

A query can use the result of another query

Sometimes a condition or value cannot be expressed conveniently with one flat query. In that case, one SELECT can be nested inside another and its result can be used as part of the outer query.

In this section, we will study subqueries from simple scalar expressions to dependent queries, and then move to CTEs — named intermediate result sets that help make more complex SQL easier to read and build.

What this section contains

• Subquery basics. We will understand where a nested query can appear and how the outer query uses its result.
• Scalar subqueries. We will examine a subquery that must return a single value.
• Subqueries in WHERE. We will learn how to use the result of a nested query to filter rows.
• EXISTS and NOT EXISTS. We will check not for a particular value, but for whether a matching row exists at all.
• Correlated subqueries. We will see queries where the inner query depends on the current row of the outer query.
• CTE. We will move an intermediate query into a named WITH block.
• Recursive CTE. We will learn the basic idea of recursive queries for hierarchies and sequences.

The main goal is not simply to learn how to “put SELECT inside SELECT,” but to understand the shape of the result produced by each part of the query and how that result is used at the next level.