What are common pitfalls with common table expressions (CTEs)?

Common table expressions (CTEs) can simplify complex queries and improve readability, but they come with several pitfalls that users should be aware of, such as performance issues, recursion limits, and incorrect scoping. This article explores these common pitfalls to help you leverage CTEs effectively in your SQL queries.

-- Example of a recursive CTE in MySQL
WITH RECURSIVE cte AS (
    SELECT id, name, parent_id FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;