CTE vs subquery — which is better?

CTEs are more readable (named, sequential, flat) and can be recursive. Subqueries are more compact for simple cases. Performance is usually identical — the optimizer expands CTEs inline. Use CTEs for complex queries and recursion; subqueries for simple one-off cases.

What is a recursive CTE?

A CTE that references itself, used for hierarchical data (org charts, file systems, graph traversal). Structure: WITH RECURSIVE name AS (base case UNION ALL recursive step). The recursion continues until no new rows are generated. Supported in PostgreSQL, MySQL 8+, SQLite, SQL Server.

Usar SQL subqueries y CTEs

Subqueries nest un consulta inside another. CTEs (WITH clause) provide named, readable subqueries. Aprender cuando a usar cada.

1

Subqueries appear in SELECT, FROM, WHERE, and HAVING clauses. Types: scalar (returns one value), row (one row), table (multiple rows). CTEs (Common Table Expressions, WITH name AS (...)) are named subqueries that improve readability and can be recursive. Prefer CTEs over nested subqueries for readability; the optimizer treats them equivalently in most databases.
2

Formateador SQL →
Formatear queries with subqueries or CTEs. For subqueries, format shows the nesting level. For CTEs, format shows each named query clearly. Prefer CTE syntax — it is more readable and easier to debug than deeply nested subqueries.
3

sql-format → word-count
Ver receta Abrir en cadena de herramientas

Use the SQL format workflow to format complex queries with multiple CTEs or subqueries. This produces clean, documented SQL that is easy to understand and maintain.

Preguntas frecuentes

CTE vs subquery — which is better?: CTEs are more readable (named, sequential, flat) and can be recursive. Subqueries are more compact for simple cases. Performance is usually identical — the optimizer expands CTEs inline. Use CTEs for complex queries and recursion; subqueries for simple one-off cases.
What is a recursive CTE?: A CTE that references itself, used for hierarchical data (org charts, file systems, graph traversal). Structure: WITH RECURSIVE name AS (base case UNION ALL recursive step). The recursion continues until no new rows are generated. Supported in PostgreSQL, MySQL 8+, SQLite, SQL Server.