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.

Utiliser SQL subqueries et CTEs

Subqueries nest un requête inside another. CTEs (WITH clause) provide named, readable subqueries. Apprendre quand en utiliser chaque.

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

Formateur SQL →
Formater 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
Voir la recette Ouvrir dans la chaîne d’outils

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.

Questions fréquentes

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.