Subqueries and Common Table Expressions (CTEs) are the foundation of writing complex SQL in a structured and maintainable way. Instead of solving a difficult query in one deeply nested statement, they allow developers to decompose the problem into logical steps. A subquery can appear in SELECT to return a single value, in WHERE to filter rows, in FROM as a derived table, or in a WITH clause as a named CTE. The position of the subquery determines what it must return—whether a scalar value, a list, or an entire table. CTEs extend this idea by giving intermediate results meaningful names, making long analytical queries easier to read, debug, and review.
Scalar and correlated subqueries solve different kinds of problems. An uncorrelated subquery is independent of the outer query and is typically evaluated once, making it efficient for tasks such as comparing every order against the global average. A correlated subquery references columns from the outer query, meaning it is logically evaluated for each row, such as comparing an order against its own customer’s average purchase. While correlated queries are expressive, they can become expensive on large datasets and are often better rewritten as joins or window functions. When testing for existence rather than retrieving values, EXISTS is generally superior to IN because it is NULL-safe and stops searching as soon as the first matching row is found.
Derived tables and CTEs provide elegant ways to structure intermediate calculations. A derived table is an inline temporary table that is particularly useful when filtering on aggregates or window-function results, while a CTE transforms nested logic into a top-down pipeline where each step feeds the next. Modern SQL engines usually optimize CTEs similarly to subqueries, but PostgreSQL also offers MATERIALIZED and NOT MATERIALIZED to control whether a CTE is computed once or inlined for predicate pushdown. Writable CTEs take this even further by allowing INSERT, UPDATE, or DELETE operations with RETURNING, enabling atomic multi-step operations such as archiving old records in a single statement.
Recursive CTEs introduce controlled iteration into standard SQL, making hierarchical queries possible without procedural code. They combine an anchor query with a recursive term connected by UNION ALL, allowing databases to traverse organizational charts, category trees, bills of material, and graph paths while carrying additional state such as depth and ancestry. Every recursive query should include a depth limit or cycle guard to prevent infinite recursion. Although recursive CTEs are powerful, many day-to-day analytical problems are better expressed through chained non-recursive CTEs that progressively filter, aggregate, enrich, rank, and present data in clearly separated stages.
Choosing the right query structure is ultimately a balance between readability and performance. Use scalar subqueries for single computed values, EXISTS or NOT EXISTS for membership tests, derived tables or CTEs when filtering aggregates, and window functions when a grouped value must appear alongside detailed rows. The presentation highlights one of the most valuable optimization patterns in SQL: replacing correlated aggregate subqueries with a pre-aggregated join, which allows the database to compute expensive summaries once instead of repeating them for every output row. Well-structured SQL is therefore not only easier to maintain—it also gives the query optimizer a better opportunity to generate efficient execution plans.
DECISION STATS 