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.

Verwenden SQL subqueries und CTEs

Subqueries nest ein Abfrage inside another. CTEs (WITH clause) provide named, readable subqueries. Lernen wenn in verwenden jede.

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

SQL-Formatierer →
Formatieren 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
Rezept ansehen In Werkzeugkette öffnen

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.

Häufige Fragen

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.