Subqueries and CTEs — SQL & Relational Databases

Subqueries and Common Table Expressions (CTEs) are both mechanisms for breaking a complex query into composable, named parts. Without them, multi-step analytical logic must either be expressed as a single deeply nested SQL expression — which is unreadable and difficult to debug — or split across multiple intermediate tables or views. Subqueries embed one SELECT statement inside another, while CTEs define named temporary result sets using a WITH clause before the main query, making the logic readable and step-by-step.

The choice between subqueries and CTEs is not merely stylistic — it affects readability, debuggability, and in some databases, performance. CTEs are particularly powerful in data engineering because they allow complex multi-step transformations to be expressed in a single SQL statement that reads like a sequential narrative: define the source, filter it, aggregate it, rank it, then select the top results. This readability directly reduces the review and maintenance cost of pipeline transformation queries.

Recursive CTEs extend this further, enabling hierarchical queries — queries that traverse tree or graph structures — which are not possible with standard SELECT-FROM-WHERE syntax. For data engineers working with organisational hierarchies, category trees, or network graphs stored in relational tables, recursive CTEs are the correct and often the only SQL-native tool for the job. Understanding when to use each form — inline subquery, scalar subquery, CTE, or recursive CTE — is a key skill in producing maintainable transformation code.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.