COMMON TABLE EXPRESSION (Recursive Tree)
COMMON TABLE EXPRESSION (Recursive Tree) is a SQL statement in the CTE (WITH) category. Recursive CTE for tree structures with parent-child relationships. Supports depth tracking and path building. The syntax is WITH RECURSIVE tree AS (...) SELECT * FROM tree;. It returns tree structure. A typical example: WITH RECURSIVE category_tree AS ( SELECT id, name, parent_id, 0 AS depth, CAST(name AS VARCHAR(500)) AS path FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, ct.depth + 1, CONCAT(ct.path, ' > ', c.name) FROM categories c JOIN category_tree ct ON c.parent_id = ct.id ) SELECT id, path, depth FROM category_tree ORDER BY path; -- Electronics > Computers > Laptops (depth 2) -- Electronics > Computers >… A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Recursive CTE, which a CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures).