Common Table Expressions (CTEs)
debt(d5/e3/b3/t5)
Closest to 'specialist tool catches' (d5). Misuse of CTEs (infinite recursion, unnecessary materialization) requires running EXPLAIN in MySQL Workbench or observing slow queries via Laravel Debugbar. No static analyzer catches CTE performance issues automatically; you need query profiling tools to detect suboptimal usage.
Closest to 'simple parameterised fix' (e3). The quick_fix indicates converting nested subqueries to CTEs or vice versa is a localized SQL refactor. Adding LIMIT/depth counters to recursive CTEs is a single-query fix. However, fixing performance issues may require rewriting multiple queries if the pattern is repeated, pushing slightly beyond e1.
Closest to 'localised tax' (b3). CTEs are query-scoped constructs that don't impose system-wide architectural constraints. A bad CTE affects only that query/component. However, adopting CTEs for tree traversal (recursive CTEs) becomes a pattern that influences how you model hierarchical data access across the codebase, adding some persistent weight.
Closest to 'notable trap' (t5). The misconception explicitly states developers wrongly assume CTEs are always faster than subqueries. PostgreSQL materializes CTEs by default (optimization barrier), while MySQL 8 may inline them — this contradicts the intuition that 'named = optimized'. Most experienced developers eventually learn this gotcha through EXPLAIN analysis, making it a documented but notable trap.
Also Known As
TL;DR
Explanation
A CTE defined with WITH name AS (SELECT ...) acts like a named subquery. Multiple CTEs can be chained and reference each other. Recursive CTEs (WITH RECURSIVE) solve hierarchical queries — tree traversal, graph paths, sequence generation — without application-level looping. Most databases materialise CTEs, but PostgreSQL and MySQL 8.0+ can inline them. CTEs dramatically improve readability for complex analytical queries.
Diagram
flowchart TD
subgraph Recursive CTE - Category Tree
ANCHOR[Anchor: SELECT root categories<br/>WHERE parent_id IS NULL]
RECURSIVE[Recursive: JOIN categories<br/>ON parent_id = prev.id]
ANCHOR -->|UNION ALL| RECURSIVE
RECURSIVE -->|Until no more rows| RESULT[Full tree<br/>in one query]
end
subgraph Regular CTE
CTE1[WITH monthly_stats AS SELECT...]
CTE2[WITH ranked AS SELECT... FROM monthly_stats]
CTE1 --> CTE2 --> FINAL[Final SELECT]
end
style RESULT fill:#238636,color:#fff
style FINAL fill:#238636,color:#fff
Common Misconception
Why It Matters
Common Mistakes
- Not adding a LIMIT or depth counter to recursive CTEs — infinite recursion on cyclic data.
- Assuming CTEs are always optimised away — PostgreSQL materialises them, which can be slower than an equivalent subquery.
- Not using CTEs for multi-step transformations — nested subqueries become unreadable beyond two levels.
- Using a CTE when a simple JOIN would be cleaner and faster.
Code Examples
-- Multiple application round-trips for category tree:
// PHP: fetch top-level categories
// PHP: foreach category, fetch children
// PHP: foreach child, fetch grandchildren
// O(n) database queries for n levels-- Recursive CTE — one query for entire category tree:
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL -- anchor
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
WHERE ct.depth < 10 -- prevent infinite recursion
)
SELECT * FROM category_tree ORDER BY depth, name;