How do CTEs and self-joins help, and when is a recursive CTE the right tool?

A common table expression (WITH ...) names a subquery to improve readability and let you reference it multiple times, and a self-join joins a table to itself to relate rows within it, like matching employees to their managers in the same table.

A recursive CTE is the right tool for hierarchical or graph traversal such as an org chart, bill-of-materials, or category tree, walking parent-child relationships to arbitrary depth. Be aware that in some databases CTEs were historically an optimization fence (materialized), so on hot paths verify the plan rather than assuming the optimizer inlines them.